0

I have this table:

CREATE TABLE `foo` (
  `CalculatedResultsId` int NOT NULL,
  `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `SectionData` json NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

ALTER TABLE `foo`
  ADD UNIQUE KEY `CalculatedResultsId` (`CalculatedResultsId`),
  ADD UNIQUE KEY `Md5Hash` (`Md5Hash`);

which contains ~400k rows and is ~800MB big. This is the output from PMA:

enter image description here

Then I delete 2'454 rows [< 0.6%], which produces an overhead of around 5MB which is also documented in PMA:

enter image description here

Running OPTIMIZE Table suddenly doubles the size of this table. I checked the real size of the table file in the directory and this is correct. It really doubled!

enter image description here

My question:

Why, what is going on? What happened, what is the reason, that the table size doubled?

The only way I found how to reduce the size of the table as it was before is to create a new table and re-fill it. Another very strange fact:

Running this command in order to get the copy of the big table CREATE TABLE foo_new AS SELECT * FROM foo; runs for 27! minutes for these 400k rows.

enter image description here

Creating an empty table and then copying with INSERT INTO foo_new SELECT * FROM foo needs 24! seconds.

Why such a difference? I don't know, but somehow it looks to me like a very serious design problem in MySQL, or a bug.

MySQL 8.0.20 running on SLES 15.1

2
  • your row size just doubled and a bit, maybe it is a bug. i can't see why it should be so. I hope you made a backup before doing such things – nbk Jun 6 '20 at 18:55
  • @nbk Don't worry, The servers are VM on a Citrix host with enough backups and snapshots. That far nothing happened, but it's strange. See my answer. It's a bug. – Peter VARGA Jun 8 '20 at 12:05
1

It's a 4 month ago filed bug and it is fixed in MySQL 8.0.22.

1
  • Sounds like a nudge to get away from MyISAM. I wonder if the Data Dictionary is somehow involved in the regression. – Rick James Jun 9 '20 at 17:05
0

As for the slowness of the copying -- For each row it must check that there is not already a dup in each UNIQUE index.

Two workarounds (maybe):

  • Increase key_buffer_size.
  • Disable the indexes, do the load, then re-enable the indexes. (Hopefully, this will cause it to do a file sort, which might be faster.)
  • CREATE the table without the indexes, then ADD them.
7
  • With all request Rick, but I cannot agree. Why is the INSERT SELECT * version more or less 70 times faster? Here the index does as well exist – Peter VARGA Jun 9 '20 at 18:37
  • @PeterVARGA - Please time them again and see what SHOW PROCESSLIST; says during the loading. I would like to know if one says "Rebuilding indexes" and the other says "Rebuilding using key_buffer" (or some such wording). If they differ, then that will lead to the answer you seek. – Rick James Jun 9 '20 at 18:49
  • I wanted to post the requested output and now, I cannot believe it myself, the copy using the CREATE TABLE AS SELECT * version needs 14 seconds. I posted in the question the screenshot where it's visible. Of course, I haven't changed anything AND I didn't restart in the meantime the MySQL server. Very, very strange. – Peter VARGA Jun 9 '20 at 19:30
  • Not so strange. That's caching. Restart mysqld and it will be slow again. When timing SELECTs, I recommend running them twice and trusting the second time. (Your situation can't easily be rerun since it involves create table or insert.) The caching is probably on the SELECT side of the queries. – Rick James Jun 9 '20 at 19:50
  • I forgot to mention I restarted the server as I thought exactly the same, e.q. it can't be caching. Now, it takes always these 14 seconds. To be 100% sure I restarted also the VM on which the MySQL server is running. Always these 14 seconds. – Peter VARGA Jun 9 '20 at 19:53

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.