Sysbench: IO-bound and InnoDB

This has results for IO-bound sysbench with InnoDB in MySQL 5.6, 5.7 and 8.0. The test is similar to what I used for in-memory sysbench except the table has 400M rows instead of 10M and the test table is much larger than memory. The goal is to understand how performance and efficiency change from MySQL 5.6 to 8.0. I also have posts for Postgres and MyRocks.

Summary:

• MySQL 8.0 on read-heavy tests is mixed. Sometimes is is better than 5.6 and when worse the loss is mostly at most 10%.
• MySQL 8.0 on write-heavy tests does worse. Many of these are CPU bound and throughput in 8.0 is frequently 20% to 30% worse than 5.6. But for some 8.0 does much better than 5.6.
• MySQL 8.0 for full scans is ~17% slower than 5.6
• New CPU overhead explains most of the regressions
• There are large regressions on a few tests that are logically index-only and extra read IO is the problem. See slide 7 in Explaining MVCC GC.

Overview

I use my sysbench fork. I have yet to change the code but added Lua scripts for new tests. Tests are run in a sequence (prepare, pause to let write back & compaction catch up, read-only, write-heavy, pause again, read-only, delete, insert) via all_small.sh which calls another helper script, run.sh, to run tests and collect HW metrics.

The tests use 1 table with 400M rows and each test is run for 300 seconds for 1, 2 and 3 threads. The test servers have 4 CPU cores with HT disabled, 16G of RAM and NVMe SSD. The test table is much larger than RAM and I call this an IO-bound setup.

Tests used upstream MySQL 5.6.49, 5.7.31 and 8.0.22. The servers use Ubuntu 20.04 and XFS. 

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 or TPS for a test. Relative throughput is the QPS or TPS relative to the base case. The HW efficiency report has absolute and relative results for CPU and IO per operation. In this post the base case is the result for InnoDB in MySQL 5.6.49.

I use ratios (relative throughput & relative HW efficiency) to explain performance. For this post the denominator (the base case) is InnoDB from MySQL 5.6.49 and the numerator is InnoDB from MySQL 5.7.31 or 8.0.22. A throughput ratio < 1 means that 5.7 or 8.0 are slower. For HW efficiency, CPU and IO per operation, a ratio > 1 means that 5.7 or 8.0 use more CPU or IO per operation.

Files are in github including summaries for absolute throughput, relative throughput and HW efficiency. I annotate the results below with a focus on the 5.6 vs 8.0 comparison.

Load:

• Inserts/second ratio for 8.0 is 0.71 (here)
• Inserts/second ratio for 5.7 is 1.08
• CPU/insert ratio is 0.95 for 5.7 and 1.63 for 8.0 (here)

Read-only before write-heavy:

• For the first 4 tests that do point queries
• QPS ratios for 8.0 are 1.45, 1.09, 1.03, 0.88 (here to here)
• QPS ratios for 5.7 are 1.43, 1.09, 1.02, 0.88
• CPU/query ratios for 8.0 are 0.63, 0.90, 1.00, 1.33 (here to here)
• The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
• QPS ratios for 8.0 are 0.88, 1.06, 1.12 (here to here)
• QPS ratios for 5.7 are 0.93, 1.06, 1.25
• CPU/query ratios for 8.0 are 1.28, 0.93, 0.90 (here to here)
• The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
• QPS ratios for 8.0 are 1.03, 1.03 (here to here)
• QPS ratios for 5.7 are 0.98, 1.02
• CPU/query ratios for 8.0 are 0.99, 0.98 (here to here)
• The next 2 tests are similar to the previous but use the secondary index
• QPS ratios for 8.0 are 0.72, 0.93 (here to here)
• QPS ratios for 5.7 are 0.93, 0.92
• CPU/query ratios for 8.0 are 1.41, 1.23 (here to here)
• IO read KB/query ratios for 8.0 are 1.39, 1.00
• The next 2 tests do range queries that are covering and not covering for the PK index
• QPS ratios for 8.0 are 1.02, 1.03 (here to here)
• QPS ratios for 5.7 are 1.05, 1.04
• CPU/query ratios for 8.0 are 0.99, 0.97 (here to here)
• The next 2 tests are similar to the previous but use the secondary index
• QPS ratios for 8.0 are 0.77, 0.95 (here to here)
• QPS ratios for 5.7 are 0.84, 0.92
• CPU/query ratios for 8.0 are 1.28, 1.11 (here to here)
• IO read KB/query ratios for 8.0 are 1.29, 1.00

Write-heavy

• For the next 5 tests that are update-only
• QPS ratios for 8.0 are 1.07, 1.01, 1.51, 0.75, 0.63 (here to here)
• QPS ratios for 5.7 are 1.11, 1.01, 1.22, 0.96, 1.23
• CPU/statement ratios for 8.0 are 1.35, 1.86, 1.45, 2.23, 1.91 (here to here)
• The next test is write-only that has the writes from oltp_read_write.lua
• QPS ratio for 8.0 is 0.75 (here)
• QPS ratio for 5.7 is 1.32
• CPU/transaction ratio is 0.91 for 5.7 and 1.71 for 8.0 (here)
• The next 2 tests are the traditional sysbench tests with ranges of size 10 & 100
• QPS ratio for 8.0 is 0.62, 0.79 (here to here)
• QPS ratio for 5.7 is 1.00, 1.06
• CPU/transaction ratios for 8.0 are 2.04, 1.66 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy.

• The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
• QPS ratios for 8.0 are 0.88, 1.01, 1.13 (here to here). 
• QPS ratios for 5.7 are 0.94, 1.03, 1.25
• CPU/transaction ratios for 8.0 are 1.28, 0.99, 0.89 (here to here)
• The next 5 tests do point queries
• QPS ratios for 8.0 are 0.89, 1.09, 1.03, 1.00, 0.49 (here to here)
• QPS ratios for 5.7 are 1.02, 1.09, 0.98, 1.00, 0.89
• CPU/query ratios for 8.0 are 1.23, 0.89, 0.97, 1.32, 1.91 (here to here)
• The regression for hot-points (QPS ratio = 0.49) is from bug 102037
• The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
• QPS ratios for 8.0 are 1.00, 1.03 (here to here)
• QPS ratios for 5.7 are 0.95, 0.98
• CPU/query ratios for 8.0 are 1.04, 0.98 (here to here)
• The next 2 tests are similar to the previous test but use the secondary index
• QPS ratios for 8.0 are 0.72, 0.90 (here to here)
• QPS ratios for 5.7 are 0.93, 0.89
• CPU/query ratios for 8.0 are 1.40, 1.30 (here to here)
• IO read KB/query ratios for 8.0 are 1.39, 0.99
• The next 2 tests do range queries that are covering and not covering for the PK index
• QPS ratios for 8.0 are 1.00, 1.03 (here to here)
• QPS ratios for 5.7 are 1.02, 1.06
• CPU/query ratios for 8.0 are 1.01, 0.96 (here to here)
• The next 2 tests are similar to the previous but use the secondary index
• QPS ratios for 8.0 are 0.77, 0.92 (here to here)
  
• QPS ratios for 5.7 are 0.84, 0.94
• CPU/query ratios for 8.0 are 1.29, 1.23 (here to here)
• IO read KB/query ratios for 8.0 are 1.24, 1.00
• The next test does a single-threaded full scan of the test table with a filter so that the result set is empty.
• Rows scanned/second ratio is 0.83 for 8.0 (here)
• Rows scanned/second ratio is 0.87 for 5.7
• For 8.0 the CPU/query ratio is 0.96 and the IO read KB/query ratio is 0.83 (here)
• It isn't in the tables I shared but the SSD read 624 MB/s for 5.6 vs 516 MB/s for 8.0.

Insert/delete

• QPS ratios for 8.0 are 1.33 for delete and 0.66 for insert
• QPS ratios for 5.7 are 1.08 for delete and 0.85 for insert
• CPU/statement ratios for 8.0 are 1.56 for delete and 2.94 for insert