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

Monday, June 12, 2017

Linkbench, 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 Linkbench on Intel NUC servers with an IO-bound workload. The working set is larger than RAM and there many reads from and writes to storage. This is part of my work on bug 86215 and will be my last blog post on the topic for a few weeks. For more on Linkbench read the Facebook post and paper and my latest post.

tl;dr

For the i5 NUC at least 2/3 of the regression is from MySQL 5.6 to 5.7 and the remainder from 5.7 to 8. Most of this is explained by more CPU overhead in 5.7 and 8.
For the i3 NUC there is more variance, but that has a slower and older CPU that is less interesting to me. At this point I think the i3 NUC is useful to test builds and run MTR but not to test performance.

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 latain1/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, 2X more storage 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.

For this test I used maxid1=40M in the Linkbench configuration for the i3 NUC and maxid1=80M for the i5 NUC. I use Linkbench from here and helper scripts from here. My usage of Linkbench is described here. A sample command line to run the tests is:

bash all.sh rx ~/bin/mysql /data/m/data 40000001 sdb 1 3600 mysql lb.sql.inno 24 127.0.0.1 1

Results

The first pair of charts shows the absolute and relative insert rates during the load test. For the i3 NUC there is a regression from MySQL 5.6 to 5.7 but not to 8. Results for many of my tests have been less predictable on the i3 NUC perhaps because it has a much slower and older CPU. For the i5 NUC most of the regression is from MySQL 5.6 to 5.7.

The next pair of charts show the absolute value for CPU per insert followed by the inverse of the relative rates. The absolute value comes from the Mcpu/t column in my performance data. The i3 NUC results continue to be odd for MySQL 8. For the i5 NUC most of the CPU regression is from MySQL 5.6 to 5.7. For the i5 NUC the inverse of CPU per insert values are similar to the relative insert rates above and the CPU regression explains the drop in insert rates from MySQL 5.6 to 5.7 and 8.

The next pair of charts show the absolute transactions/second rates and the the rates relative to the value for MySQL 5.6. These are from the 24th hour of the transaction test. For the i3 NUC about 1/3 of the regression is from MySQL 5.6 to 5.7. For the i5 NUC about 2/3 of the regression is from MySQL 5.6 to 5.7.

The final pair of charts shows the absolute value for CPU per transaction followed by the inverse of the value relative to MySQL 5.6. These are from the 24th hour of the transaction test. The absolute value is from the Mcpu/t column in the performance data. For the i3 NUC about 1/3 of the CPU regression is from MySQL 5.6 to 5.7 and for the i5 NUC most of the regression is from MySQL 5.6 to 5.7. The second chart shows that the inverse of the relative CPU predicts the transaction rate and the CPU regression explains the transaction rate regression.