Perf regressions in MySQL from 5.6 to 8.0 using the Insert Benchmark and a small server, Dec23: 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 small server and a cached workload. It follows up on work I published in October. The differences are that I updated the my.cnf files to make them more similar across releases and I added results for pre-GA releases from 5.7 and 8.0.

tl;dr

• For 4 of the 6 benchmark steps, MySQL 8.0.35 gets about 60% of the throughput relative to 5.6.51 with simple queries and a cached database. The regressions for random writes (l.i1) is less and index create (l.x) is faster in 8.0 than in 5.6.
• For MySQL 5.7 about half of the perf regressions occur between the last 5.6 release (5.6.51) and the first 5.7 release that I was able to compile (5.7.5). The rest are spread evenly across 5.7.
• For MySQL 8.0 almost all of the perf regressions are spread across 8.0 releases from 8.0.0 to 8.0.35. 
• For InnoDB there is at least one significant regression arriving in 8.0.30+ related to which code gets inlined by the compiler. This is somewhat obfuscated because two perf bugs that hurt this benchmark were fixed after 8.0.28. For more details see PS-8822.

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.51
• 5.7 - 5.7.5 thru 5.7.10, 5.7.20, 5.7.30, 5.7.44
• 8.0 - 8.0.0 thru 8.0.5, 8.0.11 thru 8.0.14, 8.0.20, 8.0.22, 8.0.28, 8.0.30, 8.0.34, 8.0.35

I used the cz10a_bee config and it is here for

• 5.6 - all point releases 
• 5.7 - 5.7.5 and 5.7.6, 5.7.7, 5.7.8 to 5.7.44
• 8.0 - 8.0.0 to 8.0.2, 8.0.3 to 8.0.4, 8.0.11 to 8.0.18, 8.0.19 to 8.0.35

Benchmark

The Insert Benchmark was run in one setup - a cached workload.

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 1800 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.5 for the 5.7-only report, 8.0.0 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 5.6, 5.7, 8.0, 5.6 thru 8.0.

The summaries linked below have three tables. The first table shows the absolute throughput (queries or writes/s). The second table shows the throughput for a given version relative to MySQL 5.6.21 (a value less than 1.0 means the version is slower). The third table shows the average background insert rate during q100, q500 and q1000. All versions sustained the targets, so that table can be ignored here.

By write-heavy I mean the l.i0, l.x and l.i1 benchmark steps. By read-heavy I mean the q100, q500 and q1000 benchmark steps. Below I use red and green to indicate when the relative QPS is down or up by more than 5% and grey otherwise.

From the Summary for 5.6, 5.7 and 8.0

• relative QPS is relative to MySQL 5.6.21
• relative QPS in 5.7.44 is (0.82, 1.35, 1.10) and  (0.73, 0.72, 0.72) for write- and read-heavy
• There are perf regressions for the l.i0, q100, q500, and q1000 benchmark steps. About half of that is from the 5.6 to 5.7 transition and the rest is a gradual loss during across 5.7. 
• For the l.x benchmark step perf improves from 5.6 to 5.7 and then gradually declines across 5.7.
• For the l.i1 benchmark step perf improves from 5.6 to 5.7 and is steady across 5.7
• relative QPS in 8.0.35 is (0.55, 1.24, 0.89) and (0.62, 0.61, 0.62) for write- and read-heavy
• Perf in 8.0.0 is close to 5.7.44 and 8.0.0 is the first (pre-GA) 8.0 release
• There are perf regressions for all benchmark steps. These are gradual across 8.0.

From the Summary for 8.0

• relative QPS is relative to MySQL 8.0.0
• relative QPS in 8.0.35 is (0.69, 0.92, 0.84) and (0.84, 0.85, 0.85) for write- and read-heavy

From the Summary for 5.7

• relative QPS is relative to MySQL 5.7.5
• relative QPS in 5.7.44 is (0.92, 0.83, 1.02) and (0.88, 0.87, 0.88) for write- and read-heavy

From the Summary for 5.6

• relative QPS is relative to MySQL 5.6.21
• relative QPS in 5.6.51 is (0.96, 1.02, 0.97) and (0.98, 0.97, 0.98) for write- and read-heavy