Low-concurrency performance for updates with InnoDB: MySQL 5.7 vs previous releases

First I tested updates with the Heap engine as a base case and now I report on InnoDB to find regressions that are specific to it. The workload is described in the previous blog post. Response time is measured at 1, 4 and 32 threads for two types of updates. The unindexed update increments an unindexed column. The indexed update updates a column that is in a secondary index. Tests are run with the binlog enabled and disabled. Tests are done for MySQL versions 5.0.85, 5.1.63, 5.5.40, 5.6.21 and 5.7.5. In some cases tests are also done with the adaptive hash index disabled. The test database has 64,000 rows but all updates are to one row (lots of contention). The client is mysqlslap running on the same host as mysqld. The test server has 40 hyperthread cores.

For the single-thread update unindexed workload MySQL 5.7.5 is about 1.6X slower than 5.0.85. This is similar to what I reported in previous posts. For the single-thread updated indexed workload MySQL 5.7.5 is about 4X slower than 5.0.85 and much slower than 5.6.21. This is a regression for which I created bug 74325. This and a few other bugs might be motivation to get similar tests into the upstream release process.

The binaries below are tagged with -noahi, -nobl and -bl. The -noahi binary has the binlog and adaptive hash index disabled. The -nobl binary has the binlog disabled. The -bl binary has the binlog enabled.

update indexed, binlog off

Result summary:

• at 1 thread: 5.7.5 is 3.8X slower, 5.6.21 is 1.43X slower
• at 4 threads: 5.7.5 is 3.88X slower, 5.6.21 is slightly faster
• at 32 threads: 5.7.5 is 1.96X slower, 5.6.21 is faster

update indexed, binlog on

Result summary:

• at 1 thread: 5.7.5 is 4X slower, 5.6.21 is 1.37X slower
• at 4 threads: 5.7.5 is 2.1X slower, 5.6.21 is 1.09X slower
• at 32 threads: 5.7.5 is 2.26X slower, 5.6.21 is faster

update unindexed, binlog off

Result summary:

• at 1 thread: 5.7.5 is 1.65X slower, 5.6.21 is 1.44X slower
• at 4 threads: 5.7.5 is 1.33X slower, 5.6.21 is faster. Note that 5.7.5 is the only post-5.0.85 release still slower than 5.0.85 at 4 threads.
• at 32 threads: 5.7.5 is faster, 5.6.21 is faster

update unindexed, binlog on

Result summary:

• at 1 thread: 5.7.5 is 1.62X slower, 5.6.21 is 1.43X slower
• at 4 threads: 5.7.5 is 1.39X slower, 5.6.21 is 1.18X slower
• at 32 threads: 5.7.5 is faster, 5.6.21 is faster