InnoDB, MyISAM and disk space

InnoDB uses more disk space than MyISAM because transactions are not free. But how much more will it use? That depends on your schema and data. Depending on the value of pack_keys for a MyISAM table, InnoDB uses 1.24X, 1.35X or 1.46X the disk space as used by MyISAM for the schema and data that I care about. I don't think this is a huge difference and is certainly far from a 3X to 4X overhead.

I measured the overhead by reloading the database using InnoDB and MyISAM. For MyISAM I used pack_keys=default, 1 and 0. I would not use pack_keys=1 in production because of the performance overhead during query processing. The schema is complex -- think enterprise app rather than web app. The load was done using 8 concurrent sessions on an 8-core server. It took much longer (3.5X) to load the MyISAM tables even though repair by sort was used for index creation. The MyISAM load was CPU bound with almost 2/3 of the CPU time in pthread_mutex calls. Had I manually assigned indexes to multiple key caches this would have been less of a problem, but that was too much work. Why doesn't MySQL do this automatically? Percona has great articles on this topic.

The InnoDB row structure is described here. The overhead for an InnoDB row in MySQL 5.0 and beyond is approximately 20 + (1 * #columns) bytes:

• 6 bytes for a transaction ID
• 7 bytes for a roll pointer
• 5 bytes for a row header
• (#possibly_null_columns + 7) / 8 bytes for the null column bitmap
• 1 or 2 bytes per variable length column

There are additional overheads:

• Each secondary index entry stores all primary key columns. You should avoid creating many secondary indexes on a table with a large primary key.
• Each secondary index entry may have a per-row overhead similar to that for the base row. I have not read the code to determine whether this is true (shame on me). But from a quick test, the overhead was no more than 9 bytes per entry on MySQL 4.0.
• Secondary indexes are almost always fragmented. Prior to the MySQL 5.1 plugin they are always built incrementally after insert, update and delete statements. Assuming changes are made in a random key order, they should be about 2/3 full.
• Primary (clustered) indexes may be fragmented. This can be avoided by occasionally reorganizing the table.