Small Datum: Insert benchmark, in-memory & Intel NUC for MySQL 5.6, 5.7 and 8

Friday, June 2, 2017

Insert benchmark, in-memory & Intel NUC for MySQL 5.6, 5.7 and 8

Update - the regression isn't as bad as I have been reporting. Read this post to understand why.

Next up in my series on low-concurrency performance is results for the insert benchmark on Intel NUC servers with an in-memory workload. The database is cached so there are no reads from but many writes to storage. This is part of my work on bug 86215. One day I will stop running tests and start working on code improvements but not yet. Read this for more on the insert benchmark.

tl;dr

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.

Configuration

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.

Results

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.

The next pair of graphs is the absolute and relative query rate for the q1000 test where another client does 1000 inserts/second. 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.6. This is similar to the results for the load test above.

The next pair of graphs is the absolute and relative query rate for the q100 test where another client does 100 inserts/second. 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.6. This is similar to the results for the load and q1000 tests above.

CPU regression

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.