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

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

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

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

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

One problem is the redo log write code that was rewritten in 8.0. I have had problems with this in earlier 8.0 releases (crashes & excessive CPU). On my small server (4 cores) the excessive CPU overhead remains the the CPU load drops almost in half when I repeat tests with innodb_log_writer_threads=OFF.

ReplyDeleteFiled https://bugs.mysql.com/bug.php?id=102238 for redo log writer CPU overhead new in 8.0

DeleteThanks, Mark! My team is also seeing an increase in query latency -- from actual tee'd traffic on the same dataset -- from 5.7 to 8.0 that we're hoping to correct for by leveraging new configs and increasing concurrency (QPS). Your work is really useful for us! :heart:

ReplyDelete