1

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,

1
  • The tablesize change during engine change is unpredictable. You do not give relative size change, and absolute value may tell about nothing - so nothing to say additionally.
    – Akina
    Jun 28, 2020 at 18:40

2 Answers 2

1

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.

MyISAM

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.

InnoDB

The size of an InnoDB Page is 16K by default : See innodb_page_size

COMPARISON

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".

3
  • Thank you. Just to be clear, even though, it's a known issue, it's nothing I have to worry about, right?
    – mhweb
    Jun 28, 2020 at 20:51
  • 1
    @mhweb That's right. Nothing to worry about in terms of storage. Jun 28, 2020 at 21:19
  • That Percona link is just one example. 2x-3x, not 7x, is typical. (I have measured it many times in many situations.)
    – Rick James
    Jun 29, 2020 at 15:25
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.