Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=InnoDB; command, about 20MB. So, this is the first time I've done this, and I'm wondering if this is an expected behaviour?
Thanks,
This is a known issue (not a bug) all the days of Storage Engine technology
Someone asked a similar question in a Percona Forum in November 2014: The table size between InnoDB and MyISAM engines differs in 7 times.
This may have something to do with the granularity of storage between MyISAM and InnoDB.
According to MyISAM Dynamic Data File Layout
Variable length records are contained in "frames". A record can be put in one or more frames, also called the record "parts" or "blocks". The sense of the frames is to allow reusage of the space of deleted records. Starting with an empty data file, records are put in a single frame each, unless a record is bigger than the maximum frame size (16MB - 4). When a record is deleted, its frame is marked deleted. When a record is inserted after this, it reuses the old frame. If the new record is smaller, the frame is split. The unused part is marked deleted. If a new record is bigger than the old frame, the frame is filled with the record as much as fits. The rest is inserted in other old frames, or, if non is available, in a new frame at end of file.
The size of an InnoDB Page is 16K by default : See innodb_page_size
Since InnoDB stores in a fixed 16K "frame" whereas MyISAM stores in frames that can vary in size up 16777212 (16M - 4), one should always expect InnoDB tables to be larger due to page splits that are required to fit data into fixed 16K "frames".
Yes. 2x-3x is typical. This is one of very few drawbacks in moving to InnoDB.
In the best of situations, data in MyISAM needs only 1 extra byte per row.
InnoDB has column lengths, row pointers, BTree overhead, history list, transaction number, block chaining, fill-factor, "off-record" for big texts/blobs, etc, etc. All of that helps with speed and/or transactional semantics.
