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

I discovered one of the reasons for QPS regressions with in-memory Sysbench and MySQL 5.7+ that I explained in a previous post. The default value for eq_range_index_dive_limit was increased from 10 in 5.6 to 200 in 5.7. This option determines the amount of work (index searches) the optimizer might do to estimate predicate selectivity and going from 10 to 200 means up to ~20X more index searches for queries with large in-lists. I have sysbench scripts with queries that have 10, 100, 1000 and 10,000 values in an in-list, so the change in the option value effects the cases with 100, 1000 and 10,000 values.

I am wary of changing the default values for options, but change is inevitable and frequently a good thing. The best way to spot this is to compare output from SHOW GLOBAL VARIABLES. Of course, reading release notes is always a good idea if you have more time.

The summary from my previous post is mostly true here:

• QPS in 8.0 is frequently between 60% and 90% of what 5.6 gets because 8.0 uses more CPU
• Compared with my previous post, QPS is better here for the covered & not-covered, point & range queries in Read-only before write-heavy and Read-only after write-heavy (thanks to setting eq_range_index_dive_limit=10).
• There is a CPU regression new in 8.0.22 that hurts queries with large in-lists (bug 102037)

my.cnf changes

The my.cnf that I use for 5.7 and 8.0 used to have eq_range_index_dive_limit=10. I forgot it was there for a good reason and removed it a few weeks ago while testing my.cnf options. So I compared SHOW GLOBAL VARIABLES output between 5.6, 5.7 and 8.0 and changed a few options to make configurations more similar. 

The changes for 8.0 in the cx6d config are:

• add eq_range_index_dive_limit=10 as the default is 200
• add innodb_buffer_pool_load_at_startup=OFF, innodb_buffer_pool_dump_at_shutdown=OFF. I don't want dump/restore to interfere with my workflow.
• innodb_purge_threads=1. My server is small and more threads has hurt perf in the past.
• innodb_log_file_size=1G. I use innodb_dedicated_server but still want to guarantee that all versions use a similar number amount of redo log space.

The changes for 5.7 in the cx6d config are:

• eq_range_index_dive_limit=10 as the default is 200
• innodb_flush_method=O_DIRECT_NO_FSYNC because 8.0 uses this
• innodb_autoinc_lock_mode=2 because the default is 1 in 5.7 and 2 in 8.0
• innodb_flush_neighbors=0 because the default is 1 in 5.7 and 0 in 8.0
• innodb_buffer_pool_load_at_startup=OFF, innodb_buffer_pool_dump_at_shutdown=OFF
• innodb_purge_threads=1 to avoid CPU & mutex contention on a small server
• innodb_log_file_size=1G to match 8.0 as the default is much smaller

The changes for 5.6 in the cx6d config are:

• eq_range_index_dive_limit=10 to remind myself that this option matters
• innodb_flush_method=O_DIRECT_NO_FSYNC because 8.0 uses this
• innodb_autoinc_lock_mode=2 because the default is 1 in 5.7 and 2 in 8.0
• innodb_flush_neighbors=0 because the default is 1 in 5.7 and 0 in 8.0
• innodb_checksum_algorithm=crc32 to match 8.0 as the default (innodb) is slower
• innodb_file_format=Barracuda, innodb_file_format_max=Barracuda to match 8.0
• innodb_log_file_size=1G to match 8.0 as the default is much smaller

Other changes

I made other changes after the first round of tests:

• Run for 1, 2 and 3 clients for 180s each. Previously I used 1, 2, 3 and 4 clients at 90s each but my servers have 4 cores and CPU is frequently saturated at 4 clients.
• Change helper scripts to use the --random-points option for the covered point & range query tests
• Give time to perform all pending writes (buffer pool writeback, LSM compaction) after the initial load and before the read-only queries
• Skipped tests for 8.0.17, 8.0.19 and 8.0.20 to save time. I still run for 5.6.49, 5.7.31, 8.0.18, 8.0.21 and 8.0.22

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.72 (~100k/s -> ~70k/s)
• CPU/insert ratio is 1.58
• The insert/second ratio for 5.7 is 1.07, so loads are faster in 5.7 vs 5.6, and the regression begins in 8.0 and the problem is new CPU overhead
• There were significant 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, and iostat output shows that is limited to the end of the "prepare" step.

Read-only before write-heavy:

• QPS ratios are 0.74, 0.72, 0.59, 0.24 for the first 4 tests (up to here)
• These do point queries
• CPU/query ratios are: 1.36, 1.40, 1.72, 4.02. Up to here.
• The large regressions (0.59, 0.24) are from bug 102037 which is new in 8.0.22. For 8.0.21 the QPS ratios are 0.73, 0.70, 0.74, 0.90.
• Writeback was done before these tests started, unlike results in my previous post.
• QPS ratios are 0.74, 1.02, 1.26 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.35, 1.02, 0.83 (here to here). Long scans are better in 8.0 vs 5.6, but worse in 8.0 vs 5.7.
• 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.70, 1.73 (here to here).
• The in-lists had 100 values vs 10 for the test in the previous post. The 8.0.22 results here suffer more from bug 102037 but benefit from the change to eq_range_index_dive_limit. The QPS ratios for 8.0.21 are 0.75, 0.74.
• 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.53, 1.53 (here to here)
• The in-lists had 100 values vs 10 for the test in the previous post. The QPS ratios for 8.0.21 are 0.77, 0.75. See the previous bullet point.
• 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 primary index. The range size was 100 vs 10 in the previous tests.
• CPU/query ratios are 1.35, 1.42 (here to here)
• QPS ratios are 0.78, 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.29, 1.59 (here to here)

Write-heavy

• QPS ratios are 0.71, 2.10, 0.75, 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 are 2.72, 1.22, 2.24, 2.25, 2.25 and wKB/statement ratios are 1.95, 0.91, 0.99, 1.00, 1.44.
• 8.0 is faster than 5.6 for update-index (QPS ratio is 2.10), but slower in the other tests.
• QPS ratio is 0.77 for the next test, write-only. See here.
• This has the writes from oltp_read_write.lua. 
• CPU/transaction ratio is 2.31. See here.
• QPS ratios are 0.71, 0.86 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 and 100. 
• CPU/transaction ratios are 1.43, 1.26 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy. There is much more write IO activity with 8.0 during these tests compared to 5.6 and 5.7. I have yet to explain this. See the wKB/o columns here. I assume that something has changed with respect to furious flushing.

• QPS ratio is 0.75, 1.01, 1.25 for the next 3 tests, read-only (here to here)
• These have the queries (range scans) from oltp_read_write.lua with ranges of size 10, 100 and 10,000. Results here are similar to Read-only before write-heavy and 8.0 is better than 5.6 at long range scans, but worse at shorter ones. 
• CPU/transaction ratios are 1.34, 1.05, 0.83 (here to here)
• QPS ratios are 0.75, 0.71, 0.59, 0.24, 0.49 for the next 5 tests (here to here)
• These do a variety of point queries. The first 4 were run in Read-only before write heavy, and results here are similar.
• CPU/query ratios are 1.36, 1.42, 1.77, 4.07, 1.91 (here to here)
• The large regressions in QPS ratios (0.59, 0.24, 0.49) are from bug 102037 which is new in 8.0.22. For 8.0.21 the QPS ratios are 0.74, 0.70, 0.73, 0.90, 0.63.
• 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 primary index. Results here are similar to Read-only before write-heavy.
• CPU/query ratios are 1.73, 1.74 (here to here)
• For the impact from bug 102037 the QPS ratios for 8.0.21 are 0.75, 0.74 
• QPS ratios are 0.69, 0.66 for the next 2 tests (here to here)
• These are similar to the previous test, but use the secondary index. Results here are similar to Read-only before write-heavy. 
• CPU/query ratios are 1.47, 1.54 (here to here). 
• For the impact from bug 102037 the QPS ratios for 8.0.21 are 0.80, 0.76
• QPS ratios are 0.76, 0.71 for the next 2 tests (here to here)
• These do range queries that are covering and then not covering for the primary index. Results here are similar to Read-only before write-heavy.
• CPU/query ratios are 1.34, 1.40 (here to here)
• For the impact from bug 102037 the QPS ratios for 8.0.21 are 
• QPS ratios are 0.78, 0.59 for the next 2 tests (here to here)
• These are similar to the previous test, but use the secondary index. Results here are similar to Read-only before write-heavy.
• CPU/query ratios are 1.32, 1.60 (here to here)

Insert/delete

• QPS ratio is 0.70 for the delete test and 0.65 for the insert test
• CPU/statement ratio is 2.31 for delete and 2.96 for insert