Sysbench: in-memory InnoDB, MySQL 5.6, 5.7 & 8.0, take 3

This is my third attempt to report in-memory sysbench results for InnoDB in MySQL 5.6, 5.7 and 8.0. In my second attempt I changed my.cnf so that both versions used the same value for eq_range_index_dive_limit. In this attempt I get InnoDB to write back dirty pages (see here) after the write-heavy tests are run (before the read-only tests are repeated). The main reason for the change was to reduce variance in MyRocks results as explained here, but I hope it helps InnoDB as well.

Will MySQL continue to get 15% less QPS in each new major version as it has for 5.6 to 5.7 to 8.0? This is true for in-memory and low-concurrency workloads, and likely true for any workload for which CPU overhead is an issue. Workloads that are IO-bound and/or limited by contention will suffer less from this and also benefit from improvements upstream has made for mutex contention.

The summary from my previous post is mostly true here:

• InnoDB in 5.7 gets ~85% or better of the QPS vs 5.6
• InnoDB in 8.0 gets ~70% or more of the QPS vs 5.6 
• For write-heavy there is a large regression from 5.7 to 8.0 that I have yet to explain
• For queries with large in-lists there is a CPU regression bug in 8.0.22 -- bug 102037
• Regressions from 5.6 to 8.0 for low-concurrency, in-memory workloads are worse for InnoDB than for MyRocks. I didn't expect this, but there have been some big changes to InnoDB in 8.0. I assumed that most regressions are from code above InnoDB, but that isn't true.

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete. 

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.49). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.49) for CPU and IO per operation.

I use ratios to explain performance. In this case MySQL 5.6.49 is the denominator and the numerator is the value for 5.7 or 8.0. A QPS ratio < 1 means the new version is slower. For HW efficiency I use CPU/operation and IO/operation (read & write). For CPU and IO per operation a ratio > 1 means the new version uses more CPU or IO per query.

The results are in github for absolute throughput, relative throughput and HW efficiency. I annotate the relative throughput and HW efficiency results below. Unless called out, I am explaining the results for 8.0.22 relative to 5.6.49. 

Load:

• Inserts/second ratio is 0.65 (~104k/s -> ~68k/s, see here)
• CPU/insert ratio is 1.67 (see here)
• The regression is new in 8.0 and CPU overhead is the problem
• There were many reads from storage for 5.6, 5.7 and 8.0.18, but not 8.0.21 or 8.0.22. That IO was from creating the secondary index which is done after the table has been loaded.
• Inserts/second ratio for 5.7.31 is 0.99

Read-only before write-heavy:

• QPS ratios are 0.74, 0.71, 0.59, 0.24 for the first 4 tests (here to here)
• These do point queries
• CPU/query ratios are: 1.36, 1.43, 1.72, 4.06 (here to here)
• The large regressions (0.59, 0.24) are from bug 102037 which is new in 8.0.22 and QPS ratios are for 8.0.21 are 0.72, 0.69, 0.73, 0.89
• QPS ratios for 5.7.31 are 0.85, 0.87, 0.87, 1.07 
• QPS ratios are 0.73, 1.01, 1.25 for the next 3 tests (here to here) 
• These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
• CPU/query ratios are 1.34, 1.03, 0.83 (here to here). 
• QPS ratios for 5.7.31 are 0.86, 1.12, 1.46
• QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)
• These do point queries via in-lists that are covering and then not covering for the PK index
• CPU/query ratios are 1.72, 1.74 (here to here).
• 8.0.22 suffers from bug 102037 and QPS ratios for 8.0.21 are 0.75, 0.74.
• QPS ratios for 5.7.31 are 0.86, 0.87
• QPS ratios are 0.66, 0.65 for the next 2 tests (here to here)
• These are similar to the previous test, but use the secondary index.
• CPU/query ratios are 1.51, 1.53 (here to here)
• 8.0.22 suffers from bug 102037 and QPS ratios for 8.0.21 are 0.76, 0.74.
• QPS ratios for 5.7.31 are 0.88, 0.88
• QPS ratios are 0.74, 0.69 for the next 2 tests (here to here)
• These do range queries that are covering and then not covering for the primary index
• CPU/query ratios are 1.35, 1.44 (here to here)
• QPS ratios for 5.7.31 are 0.84, 0.85
• QPS ratios are 0.77, 0.59 for the next 2 tests (here to here)
• These are similar to the previous test but use the secondary index. 
• CPU/query ratios are 1.32, 1.60 (here to here)
• QPS ratios for 5.7.31 are 0.85, 0.71

Write-heavy

• QPS ratios are 0.70, 1.61, 0.76, 0.75, 0.75 for the next 5 tests (here to here)
• These are update-only
• HW efficiency metrics can be confusing (here to here)
• CPU/statement ratios for 8.0.22 are 2.75, 1.44, 2.21, 2.24. 2.23 and for 5.7.31 are 1.14, 0.77, 1.01, 1.03, 1.04. There is a big change from 5.7 to 8.0.
• wKB/statement ratios have more variance
• QPS ratios for 5.7.31 are 0.93, 1.80, 0.96, 0.95, 0.94
• QPS ratio is 0.84 for the next test, write-only. See here.
• This has the writes from oltp_read_write.lua. 
• CPU/transaction ratio for 8.0.22 is 2.22 and for 5.7.31 is 1.00. See here.
• QPS ratios for 5.7.31 is 1.09
• QPS ratios are 0.74, 0.87 for the next two tests, read-write (here to here)
• These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 & 100 
• CPU/transaction ratios are 1.41, 1.24 (here to here)
• QPS ratios for 5.7.31 are 0.91, 1.01

Read-only after write-heavy includes tests that were run before write-heavy. The DBMS engines were given a chance to catch up on write back and LSM compaction between the write-heavy tests above and the read-only tests below.

• QPS ratio is 0.73, 1.00, 1.25 for the next 3 tests, read-only (here to here)
• These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000 
• CPU/transaction ratios are 1.37, 1.04, 0.83 (here to here)
• QPS ratios for 5.7.31 are 0.88, 1.11, 1.46
• QPS ratios are 0.74, 0.71, 0.58, 0.24, 0.49 for the next 5 tests (here to here)
• These do a variety of point queries. 
• CPU/query ratios are 1.38, 1.41, 1.76, 4.05, 1.90 (here to here)
• QPS ratios for 8.0.21 are 0.73, 0.70, 0.73, 0.90, 0.63. The large regressions in QPS ratios (0.58, 0.24, 0.49) are from bug 102037 which is new in 8.0.22.
• QPS ratios for 5.7.31 are 0.85, 0.87, 0.87, 1.07, 0.85
• QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)
• These do point queries via in-lists that are covering and then not covering for the PK index
• CPU/query ratios are 1.72, 1.74 (here to here)
• QPS ratios for 8.0.21 are 0.74, 0.74 (bug 102037 hurts 8.0.22)
• QPS ratios for 5.7.31 are 0.86, 0.87
• QPS ratios are 0.68, 0.66 for the next 2 tests (here to here)
• These are similar to the previous test, but use the secondary index.
• CPU/query ratios are 1.48, 1.55 (here to here)
• QPS ratios for 8.0.21 are 0.78, 0.75 (bug 102037 hurts 8.0.22)
• QPS ratios for 5.7.31 are 0.89, 0.87
• QPS ratios are 0.75, 0.70 for the next 2 tests (here to here)
• These do range queries that are covering and then not covering for the PK index
• CPU/query ratios are 1.29, 1.42 (here to here)
• QPS ratios for 5.7.31 are 0.85, 0.85
• QPS ratios are 0.77, 0.59 for the next 2 tests (here to here)
• These are similar to the previous test, but use the secondary index. 
• CPU/query ratios are 1.31, 1.59 (here to here)
• QPS ratios for 5.7.31 are 0.85, 0.72

Insert/delete

• QPS ratio is 0.68 for the delete test and 0.68 for the insert test
• CPU/statement ratio is 2.34 for delete and 2.92 for insert. The ratios for 5.7.31 are 1.09 and 1.22. Hopefully I can explain this in a few days.
• QPS ratios for 5.7.31 are 0.86 for delete and 0.89 for insert