0

I am testing the performance of MySQL 5.7.42 vs. MySQL 8.0.33 on Ubuntu 22.04 (amd64). Each of the test is using their own AWS EC2 instance (t2.2xlarge which has 8 core/32 GB). I ran a simple sysbench (1.0.20 version) test like this:

sysbench oltp_read_write --table-size=100000 ... prepare
sysbench oltp_read_write --table-size=100000 --threads=4 --max-requests=0 --time=30 ... run
MySQL 5.7.42 MySQL 8.0.33 (stock) MySQL 8.0.33 (config change)
transactions/sec 483.60 / sec 306.87 / sec 389.31 / sec
queries/sec 9671.90 / sec 6137.38 / sec 7786.80 / sec

Just to clarify on the 2 columns on MySQL 8.0.33, the (stock) means there is no configuration change, while the (config change) means I added the "skip-log-bin" in my.cnf.

Based on the result, MySQL 8.0 seems to be slower than 5.7. I am not sure why this is the case. Has anyone seen something similar to this?

Also, I wonder if it is related to these bugs. MySQL Bug 108430 or MySQL Bug 93734 ?

By the way, I can give more details (eg. commands that I used, full output of Sysbench, etc.) that I used if that helps.

7
  • Is it slower for your transactions and queries? How many xacts/sec and qps does your app run? How much RAM on the box? Did you also test MariaDB?
    – Rick James
    May 27, 2023 at 0:22
  • Yes, when we ran our local tests wrt to the app, it is slower. We ran 4,800 queries. The average query time for 5.7 is 0.56 ms while 8.0 is 1.43 ms. The total duration time for 5.7 is 2,666 ms while 8.0 is 6,814 ms. Also, I tested this Sysbench test and our local app test on MariaDB 10.6.4 and it ran as fast as 5.7. I am hesitant to switch to MariaDB for local testing because our Production will eventually run Aurora-MySQL 3.03.1 which is equivalent to MySQL 8.0.26. May 28, 2023 at 5:10
  • Did you happen to have the slowlog turned on (with low value of long_query_time) during those tests? I am fishing for whether only one query became a lot slower -- In this case, let's focus on that query. We may find a regression in 8.0 and/or a setting that can trick 8.0 into running that query as fast as 5.7.
    – Rick James
    May 28, 2023 at 16:38
  • Aurora is not completely equivalent to 8.0. The Query cache is available on Aurora; did you have it turned on? The underlying disk architecture is quite different. Etc.
    – Rick James
    May 28, 2023 at 16:41
  • The slow query log was turned on and the 8.0 was generally slow by a few milliseconds. There wasn’t a query that really regressed. The testing was all done in vanilla MySQL. May 30, 2023 at 0:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.