Perf regressions in MySQL from 5.6.21 to 8.0.34 using the Insert Benchmark and a small server

This post has results from the Insert Benchmark for some MySQL 5.6 releases, all 5.7 releases and all 8.0 releases. I previously shared results like this using sysbench.

Disclaimer - these results are from a low concurrency workload (1 to 3 concurrent connections). Results on a bigger server with more concurrency won't look as bad.

tl;dr

• From MySQL 5.6.21 to 8.0.34
• Throughput for 4 of 6 benchmark steps drops by ~50%
• Throughput for one of the benchmark steps drops by ~20%
• Throughput for the remaining benchmark step increases by ~20%
• The problem is new CPU overhead
• With MySQL 5.6
• It mostly avoids perf regressions from 5.6.21 to 5.6.51 as throughput drops by ~5%
• With MySQL 5.7
• There is a large perf regression from the last 5.6 release to the first 5.7 release
• It mostly avoids regressions from 5.7.10 to 5.7.43 as throughput drops by ~5%
• With MySQL 8.0
• There is a large perf regression from the last 5.7 release to the first 8.0 release
• There are perf regressions from 8.0.13 to 8.0.34 as throughput drops by up to 20%

In summary, the largest regressions occur across major versions. In theory that is good news because when regressions are not spread across many releases they are easier to fix. But I am not sure that theory holds when the adjust releases span major releases. The largest regressions occur between these adjacent releases:

• From 5.6.51 to 5.7.10
• From 5.7.43 to 8.0.13

Builds

It isn't easy to build older code on newer systems, compilers, etc. Notes on that are here for 5.6, for 5.6 and 5.7, for 5.7 and for 8.0. A note on using cmake is here. The rel builds were used -- everything was compiled using CMAKE_BUILD_TYPE=Release.

Tests were done for:

• 5.6 - 5.6.21, 5.6.31, 5.6.41, 5.6.51
• 5.7 - all point releases from 5.7.10 to 5.7.43
• 8.0 - all point releases from 8.0.13 to 8.0.34

I used the cy10a_bee config and it is here for 5.6, 5.7 and 8.0. For 8.0 releases older than 8.0.19 I changed innodb_idle_flush_pct=1 to loose_innodb_idle_flush_pct=1.

Benchmark

The Insert Benchmark was run in one setup - the database was cached by InnoDB.

The benchmark used the Beelink server explained here that has 8 cores, 16G RAM and 1TB of NVMe SSD with XFS and Ubuntu 22.04. 

The benchmark is run with 1 client and 1 table. The benchmark is a sequence of steps.

• l.i0
• insert 20 million rows per table
• l.x
• create 3 secondary indexes. I usually ignore performance from this step.
• l.i1
• insert and delete another 50 million rows per table with secondary index maintenance. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail. 
• q100, q500, q1000
• do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 1200 seconds.

Results

The Summary sections linked below have tables for absolute and relative throughput. Absolute throughput is just the QPS per version. The relative throughput is (QPS for my version / QPS for base version). The base version is 5.6.21 for the 5.6-only report, 5.7.10 for the 5.7-only report, 8.0.13 for the 8.0-only report and 5.6.21 for the report that compares 5.6, 5.7 and 8.0.

Reports are here for

• Some MySQL 5.6 releases
• Average throughput drops by ~5% from 5.6.21 to 5.6.51 for all benchmark steps. See the relative throughput table in the Summary.
• CPU overhead doesn't change for write-heavy benchmark steps (l.i0, l.x, l.i1) and increases by ~5% for read-write steps (q100, q500, q1000). See the cpupq column (CPU/operation) in the Metrics tables.
• All MySQL 5.7 releases
• Average throughput drops by 5% to 7% for all benchmark steps. See the relative throughput table in the Summary.
• CPU overhead doesn't change much for write-heavy benchmark steps (l.i0, l.x, l.i1) and increases by ~6% for read-write steps (q100, q500, q1000). See the cpupq column (CPU/operation) in the Metrics tables.
• All MySQL 8.0 releases
• Average throughput drops by 17% to 20% for some benchmark steps (l.i0, q100, q500, q1000) and increases by 1% for others (l.x, l.i1). See the relative throughput table in the Summary.
• It is hard to explain the results for the write-heavy benchmark steps in terms of the iostat and vmstat results in the Metrics tables. But for CPU overhead increases by 17% to 19% for the read-heavy benchmark steps. See the cpupq column (CPU/operation) in the Metrics tables.
• Comparing MySQL 5.6, 5.7 and 8.0
• Average throughput drops almost in half for some benchmark steps (l.i0, q100, q500, q1000), drops by ~17% for l.i1 and increases by 21% for l.x. See the relative throughput table in the Summary.
• CPU overhead increases explain the performance regressions and for the read-write benchmark steps the CPU/operation overhead grows by 1.9X. See the cpupq column (CPU/operation) in the Metrics tables.