Skip to main content

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

Comments

4 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

Please sign in to leave a comment.