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**

Load:

- 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

- 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

- 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.

## No comments:

## Post a Comment