Skip to main content
cPanel Technical Support has been heavily impacted by hurricane Beryl and our ability to respond to tickets has been hindered as a result. We appreciate your understanding and patience as we address these delays.

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

Comments

5 comments

  • vacancy

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

    0
  • Benjamin D.

    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

    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.

    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.

    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.