Extreme InnoDB performance degradation in MySQL 8.0.36 (compared to 5.7)

Benjamin D.

• January 28, 2024 13:51
• Edited

Alright, so after spending a week migrating to a new server, fixing all the configuration issues, tuning Apache for the traffic we're getting, I'm now at the step of optimizing websites and apps that now run SUPER SLOW compared to the old server and this is not normal since the new hardware is much faster than the old one, so it should not run slow.

I've spent yesterday isolating the issue and I'm 100% sure it's MySQL 8.0 InnoDB related.  Any query, even a simple SELECT with indexes on all columns that are mentioned in the WHERE statement, no join, no sub-query, will take between 10 to 20 SECONDS (yes, twenty) where as the same query run under MySQL 5.7 will take 0.1s (zero point one second)

So clearly, something is way off with MySQL 8.0 or the configuration that came with WHM 116.0.9.

I went to phpMyAdmin and simply switched that table from InnoDB to MyISAM and the same query now takes 0.1s like it did as InnoDB under MySQL 5.7 before.  So MyISAM looks to be running smoothly under MySQL 8.0 but InnoDB is absolutely not.

I would like to know what could be done apart from switching every single table of every domain on this server from InnoDB to MyISAM and never using InnoDB ever again under MySQL 8.0.36?

 

• 

  vacancy

  • January 28, 2024 22:31

  We experienced similar problems on some of our servers, but after switching to MariaDB, all problems were improved incredibly.

  0
• 

  Benjamin D.

  • January 28, 2024 22:33
  • Edited

  OK, thanks for the advice.  I will keep this in mind as a possible option.  I have switched the most used tables to MyISAM today and everything is so much faster.  It's probably not the best option in the long run though.

  0
• 

  vacancy

  • January 28, 2024 22:48

  Theoretically innodb should be faster than myisam, also you may experience frequent table lock in myisam. The change you made was actually a reversal.

  0
• 

  Benjamin D.

  • January 28, 2024 23:21
  • Edited

  I know, InnoDB was faster than MyISAM under MySQL 5.7 but it's definitely 100x to 200x slower under MySQL 8.0.36, at least out of the box.  Today, I've read tons of threads dated 2021 to 2023 about the same issue.  On the Oracle forums, some of the bug reports ended up with dev patches that landed on MySQL 8.0.20 for example.  That's why I'm posting here.  If anybody uses MySQL 8.0.36 and has had to make a couple changes to their my.cnf please let me know what you changed from a vanilla WHM 116.0.9 install.

  Executing a SELECT on a table with no join, no sub query and 200K rows with MyISAM is 0.1s compared to 12 seconds with the same exact table switched to InnoDB engine.  That is 120 times slower.  InnoDB is just unusable on a production server in its current state under MySQL 8.0.36.

  0
• 

  Benjamin D.

  • March 15, 2024 12:01
  • Edited

  I just can't get over how utterly broken MySQL 8 performance is compared to MySQL 5.7.  It's like it's 2002 again.  I have a super small/simple table with 1K rows in it.  When I run

  SELECT COUNT(*) AS c from (SELECT * FROM thetable WHERE whatever = 1)

  ...to count all the found rows in order to be able to render an appropriate pagination system, it takes approximately 3.1s (which obviously is insanely slow).

  Now, if I instead very naively fetch relevant rows among those 1K rows via this query

  SELECT * FROM thetable WHERE whatever = 1

  ...and ask PHP to do a count($arr_rows) on the result, then the whole operation only takes 0.02s (20ms) instead of 3.1s (3100ms) which is something like 150x faster! I have never seen such levels of retardry in MySQL 5.7.  How is fetching 40MB of DATA then having PHP parse it to an array and count the items in that array be 150x faster than asking MySQL to count its own table's rows?

  0

Please sign in to leave a comment.