- MySQL 5.7 and 8 get about 85% of the insert throughput and 72% of the query throughput compared to MySQL 5.6.
- For the i3 NUC about half of the regression is from MySQL 5.6 to 5.7 and the other half from 5.7 to 8. For the i5 NUC most of the regression is from MySQL 5.6 to 5.7. This is true for all of the tests (load, q1000 and q100).
- MySQL 5.7 and 8 use more CPU per insert and per query and that explains the regression. Note that I run the benchmark client on the same host as mysqld and the client uses a lot of CPU so the real CPU regression is larger than I report here.
I tested MySQL using upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I repeated the test: first with the default charset/collation and then with latin1/latin1_swedish_ci.
My servers are described here. The charts below refer to the servers as i3 NUC and i5 NUC. Compared to the i3 NUC, the i5 NUC is newer, has a CPU that is 2X faster, 2X more RAM and storage that is more than 2X faster.
The my.cnf used for each database engine is described here. The my.cnf files I share were for the i3 NUC. For the i5 NUC the InnoDB buffer pool and IO capacity options were increased using these values. I use the same server for mysqld and the sysbench clients. The binlog is enabled but sync-on-commit is disabled.
My usage of the insert benchmark is described here. For this test I used 1 client for the load test and then 1 writer + 1 query client for the tests that do inserts & queries concurrently. The load test loads 10M rows and the insert+query tests run for about 2 hours per test.
The first pair of graphs shows the absolute and relative insert rates during the load test. On the i3 NUC about half of the regression is from MySQL 5.6 to 5.7 and the other half from 5.7 to 8 On the i5 NUC about 2/3 of the regression is from MySQL 5.6 to 5.7 and 1/3 from 5.7 to 8.
I run vmstat and iostat when the tests run to determine how much hardware is used per insert and per query. Here I show that almost all of the throughput differences are due to a CPU regression from MySQL 5.6 to 5.7 and 8. For each of the tests (load, q1000, q100) the graphs show the inverse of the relative CPU overhead per insert and per query. In the charts you can see that value is similar to the relative insert and query rates above.
All of the performance data is here. This includes metrics from the benchmark client and from vmstat and iostat.