I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while.

I have a few tables that contain some UTF8 texts. When I optimize the table (from within mySQL, using OPTIMIZE TABLE), I noticed the MYD file's size more than doubled, such as going from 4M to 8M, roughly (and not exactly double, more like 2.1 times).

I thought this could be due to my upgrade from MySQL 5.7 to 8.0. So I duplicated the entire table within MySQL 8. The newly duplicated table has the size as expected. Then I ran optimization again on this table, and its size doubles again!

I also noticed the following.

First: this does not occur to all MyISAM table. Just some of them.

Second: in the older systems (5.7 and before), If I repeatedly run OPTIMIZE TABLE on the same table, in the second and onward times, mySQL would tell me that the table was up to date, and the optimization takes almost no time. But in MySQL 8, and only for those tables that doubles the size after OPTIMIZE TABLE, every time I run optimization, it does optimization to reach a status OK. It never reaches "table is up to date" status, and the file size does not change after doubling.

Anyone runs into similar situations? Any explanations?

  • Why are you still using MyISAM tables, especially with 8.0? MyISAM is dead. Change to InnoDB and forget about this whole optimize thing. – tombom Jan 7 '19 at 9:12
  • What did you notice in common with the tables where it happened, compared to those where it didn't? – Randolph West Jan 7 '19 at 20:03
  • Randolph: I have been able to find what are in common among those files. – Bostonian Jan 8 '19 at 10:52
  • tomtom: I am new to MySQL 8.0; and those MyISAM tables have been with me for over 10 years. So I am moving slowly: taking care of issues from upgrading to 8.0 from 5.7 while exploring the InnoDB. Those tables do not involve transactions. That was the main reason I chose MyISAM long time ago. – Bostonian Jan 8 '19 at 10:56
  • You should just convert those tables to InnoDB. There is no reason to use MyISAM anymore. – Ryan Bell Mar 3 '20 at 15:00

Your Answer

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

Browse other questions tagged or ask your own question.