100x to 200x InnoDB performance degradation MySQL 5.7 -> 8.0.36 ?
Posted by:
Ben D.
Date: January 28, 2024 07:27AM
Hi, I've posted in the newbies forum, because I'm completely new to MySQL 8.0 specifically (I have experience with MySQL 5.7) and have developed over 400 web apps and websites using MySQL over the course of 15 years.
I've recently migrated from an old server that had MySQL 5.7 and my new server came with MySQL 8.0.36, so this week I've checked all the web apps for errors and performance changes and although everything "works", it's unbelievably, excruciatingly slow. I'm talking 100 to 200 TIMES SLOWER (!!) than under MySQL 5.7.
I've isolated the issue to queries that select rows from InnoDB tables. MyISAM is lightning fast (and faster than on the old server too). So there seems to be some kind of bug in the MySQL 8.0 InnoDB engine that did not exist under MySQL 5.7.
For example, I've got an users table that contain 200K rows and for which there are lots of both READ and WRITE operations every second. This table's engine is InnoDB because on the old server under MySQL 5.7 it had performance improvements over MyISAM.
Now, under MYSQL 8.0, simply counting the rows with a couple WHERE statements takes 12 to 20 seconds! It's just a simple SELECT query with no join, no sub-query and yes, there are indexes on every column that are used in the WHERE statements. That table size is only 100MB.
If I go to phpMyAdmin and change its engine from InnoDB to MyISAM and run the same query, it's 0.1 second (yes, zero point one) so it's a factor of 120 to 200 times slower! I tried to leave the computer for an hour, came back and ran the same query and it's still consistently 0.1 second under MyISAM. To me, there's something way, WAY off with InnoDB under MySQL 8.0 or perhaps there's some kind of configuration value that's missing in my.cnf? I kept most of it to default values that came with WHM 116.0.9
Any help would be appreciated, otherwise I will have to abandon InnoDB and set every table of every web app on that server to MyISAM as it's the only solution I can see right now.