List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 28 2007 3:52am
Subject:Re: Table Size
View as plain text  
In the last episode (Oct 27), Baron Schwartz said:
> InnoDB has the following extra things, plus some things I might forget:
> 
> a) the primary key B-Tree
> b) row versioning information for every row
> c) 16k page size; each page might not be completely full
> 
> Those are all counted towards the table size.  Actually, the primary
> key B-Tree might not be; I'd need to look that up.  But I think it
> is. Hmmmm.  I just tested -- yes, the PK counts towards table size.

In fact, in InnoDB, all indexes count towards table size, since there
is a single .ibd file for the whole thing.  So you've got the space
taken up by your `repid` index to consider as well.

Useful reading:

http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-structure.html
 
Note the 5-byte header per record in all indexes, plus another 13 bytes
per record in the primary key, and the fact that the columns in your
primary key are not omitted from the record data.  So repid is actually
stored three times in the .ibd file; once in the primary key, once in
the record, and once in the `repid` index.  There's quite a lot of
overhead in making a transaction-safe multiversioned table.  What I've
seen is that for small row lengths (under 50 bytes) an InnoDB table is
about twice the size as the same data in MyISAM format (including
indexes).

For your particular table, you're probably seeing the effect of 16k
page sizes.  With only 10K rows, your leaf pages are mostly empty.  Try
putting 100K rows in and see how big the .ibd file is.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Table SizeJosh27 Oct
  • Re: Table SizeBaron Schwartz27 Oct
  • Re: Table Sizejs27 Oct
Re: Table SizeJosh27 Oct
  • Re: Table SizeBaron Schwartz27 Oct
    • Re: Table SizeDan Nelson28 Oct
      • Re: Table SizeBaron Schwartz28 Oct
Re: Table SizeJosh27 Oct
Re: Table SizeJosh2 Nov
  • Re: Table SizeBaron Schwartz2 Nov