Small Datum: Insert benchmark, IO-bound & Intel NUC for MySQL 5.6, 5.7 & 8

Thursday, June 8, 2017

Insert benchmark, IO-bound & Intel NUC for MySQL 5.6, 5.7 & 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 the insert benchmark on Intel NUC servers with an IO-bound workload. The database and working set are much larger than RAM and there are many reads from and 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

New MySQL releases do better on the i3 NUC but worse on the i5 NUC. This is similar to results for the in-memory test, but the differences are larger for IO-bound. One theory for why I don't see a regression for the i3 NUC is that the SSD has much higher read response times which mask CPU overheads. Per StorageReview the read latency on the i3 NUC might be 1.5X slower for a read-only workload. I will guess the difference is larger for a mixed workload.
I hope to repeat the tests for the i3 NUC because the speedup on the q100 test is odd. I have repeated some of the results for the in-memory insert benchmark. It is possible that the device wasn't in a steady state for all of the IO-bound tests. Perhaps I need to run tests for more time.
In case you missed the previous two points, my results for the i3 NUC are confusing and I hope to repeat tests to explain that. But the i5 NUC results match previous tests and show the impact from the CPU regression in new MySQL releases, even for IO-bound workloads.

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 250M rows for the i3 NUC, 500M rows for the i5 NUC 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. For the i3 NUC there is no regression from MySQL 5.6 to 5.7 and 8. For the i5 NUC there is a regression and about half is from 5.6 to 5.7.

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 there is no regression. For the i5 NUC there is a large regression and about half is from MySQL 5.6 to 5.7.

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 there a speedup from MySQL 5.6 to recent releases. For the i5 NUC there is a small regression after MySQL 5.6.

CPU regression

I run vmstat and iostat when the tests run to determine how much hardware is used per insert and per query. For each of the tests (load, q1000, q100) the graphs show the inverse of the relative CPU overhead per insert and per query. For the i5 NUC the results here are similar to the throughput results above and more CPU overhead in recent MySQL releases is likely to explain the throughput regression. For the i3 NUC the CPU overhead appears to be reduced on recent releases. I don't understand that yet. Note that my CPU measurement includes everything on the host, not just mysqld.