table sizing

[scottf76]

Hi,
I have an innodb table with 3 columns, int(11), bigint(20) and decimal(24,5). Based on what I see in the docs each row should account for 24 bytes.

Currently this table has 25923814 rows in it (generated from select count() from table), therefore I would think that the amount of data should add up to @ 600MB (24numrows).

However, the data length that I get from information_schema.tables is 2718744576 (roughly 2.5GB). This table was analyzed very recently as well.

Could someone point me to a doc or explain what is going on here? The table is very static, its lifetime consists of a bunch of inserts, truncation then more inserts and truncation.

Any ideas on how I can create a sizing calculation that is more accurate?

The table is using utf8, but I would not think that has anything to do with the sizing. Haven’t tested that theory yet, but I would think that it wouldn’t matter what char set I use since this table only consists of numbers.

I am running 64 bit mysql 5.5.8 on Solaris 10. Should I be using Percona server instead?

thanks.

[sterin71]

Yes, but you also have to have some meta data for each record for internal house holding chores.

And for InnoDB that meta data is quite large for very small rows like yours. In your case the meta data would be something like 6+6+6+7 = 43 bytes.
Which gives you a row size about: 43 + your data 24 bytes/row = ~70 bytes/row
And then you have more space occupied by checksum and meta data for each 16k page InnoDB stores data in and the size of the b-tree made up of the primary key at which leaves InnoDB stores the data etc.
So yes the physical size of an InnoDB table is quite large.

If you are running MyISAM on the other hand the overhead per row is very low with just a few bytes at which point your calculation would be more accurate.
But then you don’t have the transaction support, or the robustness that InnoDB provides which is what you are paying for with increased size.

A very simple rule of thumb for storage need is that InnoDB takes about 2.5-3 times the size of a MyISAM table.

A good place to read about the InnoDB table format is here:
http://forge.mysql.com/wiki/MySQL_Internals_InnoDB
But it’s not easy reading.

[scottf76]

Thanks, this is exactly the type of information I wanted.

I’ve been experimenting and reading up a bit more in the mysql docs about this.

I found that since the primary key is a composite key on the bigint(20) and the int(11) that it is increasing the size of the table quite a bit due to the additional book-keeping. I’ve also found that it increases the sizing of the index as well.

Since I don’t need the uniqueness and the I/O clustering that primary key provides I just removed it. As a result I’ve actually found a space savings of about 50%. And as a welcomed side-effect the selects are running quite a bit faster.

So before the table was constructed like this:

column1 int(11),
column2 bigint(20),
column3 decimal(24,5),
primary key(column2, column1),
key (column1)

after experimenting, I’ve found this table layout to be much more efficient:

column1 int(11),
column2 bigint(20),
column3 decimal(24,5),
key (column1),
key (column2)