Perf regressions in MySQL from 5.6.21 to 8.0.34 using the Insert Benchmark and a medium server, part 1

This post has results from the Insert Benchmark for some MySQL 5.6 releases, all 5.7 releases and all 8.0 releases using a medium server and a cached workload. Previous posts have results from a small server for cached and IO-bound workloads. A 5-part series for the medium server and MySQL is here.

short and less truthy tl;dr

• There were big improvements from MySQL 5.6 to 5.7
• There have been slow regressions from MySQL 5.7 to 8.0

long and more truthy tl;dr

• For write-heavy benchmark steps
• MySQL 8.0.35 is ~1.5X faster than 5.6.x on write-heavy benchmark steps
• MySQL 8.0.35 is ~14% slower than the peak 5.7 result. 
• For read-heavy benchmark steps
• MySQL 8.0.35 is ~15% slower than 5.6.x on read-heavy benchmark steps
• MySQL 8.0.35 is ~6% slower than the peak 5.7 result. And MySQL 8.0.34, without the fix for bug 105595, is ~26% slower than the peak 5.7 result. I assume this is code bloat. 
• The results for MySQL 8.0 here are much better than on a small server. The difference is likely that improvements in MySQL 5.7 and 8.0 help workloads with more concurrency to offset the losses from code bloat.
• Fixing bug 109595 improves read QPS on q100, q500, q1000 by ~20% and this is with a cached workload. The improvement should be much larger on an IO-bound one that is in progress.

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

I used the cy10a22_gcp_c2s30 config and it is here for 5.6, 5.7 and 8.0 (pre-8.0.30, post 8.0.30). 

Benchmarks

Tests were run on a c2-standard-30 server from GCP with 15 cores, hyperthreads disabled, 120G RAM, Ubuntu 22.04 and 1.5T of local SSD via 4 NVMe devices with XFS and SW RAID 0. The test tables are cached by InnoDB.

The Insert Benchmark was run in a cached setup and all tables were cached by InnoDB.

The benchmark is run with 8 clients and 8 tables, each client uses a separate table. The benchmark is a sequence of steps and I often call each of these a benchmark step:

• 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 100 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 1800 seconds.

Reports: some MySQL 5.6, 5.7 and 8.0 releases

This has results for MySQL 5.6.21, 5.6.51, 5.7.10, 5.7.20, 5.7.30, 5.7.43, 8.0.13, 8.0.14, 8.0.28, 8.0.28, 8.0.30, 8.0.34 and 8.0.35.

From the Summary there are three tables (absolute performance in operations/s, performance relative to MySQL 5.6.21 and average background write rates for q100, q500, q1000.

• For write-heavy benchmark steps
• MySQL 8.0.35 is ~1.5X faster than 5.6.x on write-heavy benchmark steps
• MySQL 8.0.35 is ~14% slower than the peak 5.7 result. I assume this is code bloat.
• For read-heavy benchmark steps
• MySQL 8.0.35 is ~15% slower than 5.6.x on read-heavy benchmark steps
• MySQL 8.0.35 is ~6% slower than the peak 5.7 result. I assume this is code bloat.
• The results for MySQL 8.0 here are much better than on a small server. The difference is likely that improvements in MySQL 5.7 and 8.0 help workloads with more concurrency to offset the losses from code-bloat.
• Fixing bug 109595 improves read QPS on q100, q500, q1000 by ~20% and this is with a cached workload. The improvement should be much larger on an IO-bound one that is in progress.

Reports: all 8.0 releases

This has results for MySQL 8.0.13 through 8.0.34.

From the Summary there are three tables (absolute performance in operations/s, performance relative to MySQL 5.6.21 and average background write rates for q100, q500, q1000.

• Perf for the initial load step (l.i0) was lousy for 8.0.13 through 8.0.15. It has declined by ~8% from 8.0.16 to 8.0.35.
• Perf for the index create step (l.x) was lousy for 8.0.18 through 8.0.26
• Perf for the write-only step (l.i1) improved by ~11% from 8.0.13 to 8.0.35
• Perf for the read-only steps is ~2% better in 8.0.35 than 8.0.13. But it declined by ~15% from 8.0.13 through 8.0.34 then gets better in 8.0.35 thanks to the fix for  bug 109595.

Reports: all 5.7 releases releases

This has results for MySQL 5.7.10 through 5.7.43.

From the Summary there are three tables (absolute performance in operations/s, performance relative to MySQL 5.6.21 and average background write rates for q100, q500, q1000.

• Perf for the write-heavy benchmark steps is 1% to 4% slower in 5.7.43 than 5.7.10
• Perf for the read-heavy benchmark steps is ~3% slower in 5.7.43 than 5.7.10

Reports: some 5.6 releases 

This has results for MySQL 5.6.21, 5.6.31, 5.6.41 and 5.6.51.

From the Summary there are three tables (absolute performance in operations/s, performance relative to MySQL 5.6.21 and average background write rates for q100, q500, q1000.

• Perf in 5.6.51 is about the same as 5.6.21