The journey to explain performance regressions in MySQL

I have spent much time documenting performance regressions from new CPU overheads in MySQL. Soon I will be spending much time trying to explain the source of the regressions with perf, flamegraphs and more. This post is one step in the journey and it shows how performance changes from MySQL 5.6.35 through 8.0.37 using 5 different servers. Previous posts showed the impact of LTO and PGO.

Questions I try to answer in this post:

• Where in the release cycle do the biggest regressions occur?
  
• Are the regressions smaller on the larger servers? One hypothesis is that the regressions are larger at low-concurrency and smaller at high-concurrency because of improvements to InnoDB and other parts of the server. However, it would be great to get the improvements at high-concurrency without regressions at low-concurrency.

tl;dr

• The largest regressions occur between major versions (5.6.51 to 5.7.10, 5.7.44 to 8.0.11)
• There are also large regressions from 8.0.11 to 8.0.37 but not from 5.7.10 to 5.7.44
• In many cases, MySQL 8.0.37 is slower than 5.6.35 at high-concurrency and the problem is obvious on microbenchmarks that do simple SQL (point queries). The standard opinion that MySQL 8.0.x is faster at high-concurrency is far from true.
• Regressions are larger with low-concurrency than with high-concurrency. Much work was done to improve MySQL for high-concurrency. Alas, regressions (some of code bloat and memory stalls) hurts low-concurrency performance and reduces the benefit from the high-concurrency improvements.

Builds

I used InnoDB from MySQL 5.6.35, 5.6.51, 5.7.10, 5.7.44, 8.0.11, 8.0.28 and 8.0.37. The compiler was gcc 11.4.0.

Hardware

I tested on five servers:

• Small servers
• SER4 - Beelink SER 4700u (see here) with 8 cores and a Ryzen 7 4700u CPU 
• PN53 - ASUS ExpertCenter PN53 (see here) with 8 cores and an AMD Ryzen 7 7735HS CPU. The CPU on the PN53 is newer than the CPU on the SER4.
• Medium server
• C2D - a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU and SMT disabled so there are 16 cores
• Big servers
• Socket2 - SuperMicro SuperWorkstation (see here) with 2-sockets, 12 cores/socket, 64G RAM and ext4 (SW RAID 0 over 2 NVMe devices), HT disabled.
• Dell32 - Dell Precision 7865 Tower (see here) with a 32-core AMD Ryzen Threadripper PRO 5975WX, 128G RAM and ext4 (SW RAID 0 over 2 NVMe devices), SMT disabled.

All servers use Ubuntu 22.04 with ext4. 

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by MyRocks and InnoDB.

The benchmark is run with:

• SER4, PN53 - 1 thread, 1 table and 30M rows
• C2D - 12 threads, 8 tables and 10M rows per table
• Socket2 - 16 threads, 8 tables and 10M rows per table
• Dell32 - 24 threads, 8 tables and 10M rows per table
• each microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise
• prepared statements were enabled

The command lines for my helper scripts were:

# PN53, SER4

bash r.sh 1 30000000 300 600 nvme0n1 1 1 1

# C2D

bash r.sh 8 10000000 300 600 md0 1 1 12

# Socket2

bash r.sh 8 10000000 300 600 md0 1 1 16

# Dell32

bash r.sh 8 10000000 300 600 md0 1 1 24

Results

For the results below I split the 42 microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. The spreadsheets with all data is here. For each microbenchmark group there is a table with summary statistics. I don't have charts because that would use too much space, but the results per microbenchmark are in the spreadsheets.

The numbers in the spreadsheets are the relative QPS which is (QPS for my version) / (QPS for base case). When the relative throughput is > 1 then that version is faster than the base case.

For all results below the base case is MySQL 5.6.35.

Results: 5.7.10

Summary

• The base case is 5.6.35 and slower or faster is in comparison to 5.6.35
• Small servers
• 5.7.10 is ~18% slower on SER4 except for range-2
• 5.7.10 is ~12% slower on PN53 except for range-2
• Medium server
• 5.7.10 is slower and faster for reads and then much faster for writes
• The update-index microbenchmark has the largest improvement (relative QPS is 3.57)
• Large servers
• 5.7.10 has similar perf or is faster except for range-1
• The update-index microbenchmark has the largest improvement (relative QPS is > 4)

Educated guesses:

• I don't see a large regression for range-2 on the small servers. Relative to range-1, the range-2 microbenchmark group spends more time in query execution vs parse or optimize. Perhaps the CPU regressions in the other microbenchmark groups is from new CPU overhead prior to query execution. Note that the range-2 group has microbenchmarks are the classic sysbench transaction minus writes (see here).

Results for SER4

5.7.10, SER4	min	max	avg	median
point-1	0.78	0.87	0.83	0.82
point-2	0.79	0.92	0.84	0.82
range-1	0.80	1.04	0.85	0.84
range-2	0.86	1.25	1.03	0.98
writes	0.78	1.17	0.89	0.82

Results for PN53

5.7.10, PN53	min	max	avg	median
point-1	0.82	0.89	0.85	0.84
point-2	0.84	0.95	0.88	0.87
range-1	0.83	0.99	0.88	0.88
range-2	0.92	1.27	1.06	1.00
writes	0.85	1.29	0.94	0.89

Results for C2D

5.7.10, C2D	min	max	avg	median
point-1	0.82	1.44	1.00	0.94
point-2	0.90	1.20	1.06	1.08
range-1	0.83	1.12	0.91	0.90
range-2	1.16	1.30	1.23	1.22
writes	1.64	3.57	2.34	2.40

Results for Socket2

5.7.10, Socket2	min	max	avg	median
point-1	0.86	1.48	1.06	0.99
point-2	0.99	1.16	1.10	1.15
range-1	0.90	0.92	0.91	0.91
range-2	1.04	1.32	1.17	1.15
writes	1.05	4.97	1.61	1.27

Results for Dell32

5.7.10, Dell32	min	max	avg	median
point-1	0.89	2.15	1.29	1.06
point-2	1.07	1.56	1.39	1.55
range-1	0.89	0.97	0.94	0.95
range-2	0.96	1.20	1.11	1.16
writes	1.23	4.52	1.80	1.61

Results: 5.7.44

Summary

• The base case is 5.6.35 and slower or faster is in comparison to 5.6.35
• Results for 5.7.44 are similar to results for 5.7.10 -- there are few regressions during 5.7. This is easier to see on the spreadsheet (scroll to the bottom of each tab).
• Small servers
• 5.7.44 is ~15% slower on SER4 except for range-2
• 5.7.44 is ~12% slower on PN53 except for range-2
• Medium server
• 5.7.44 is slower and faster for reads and then much faster for writes
• The update-index microbenchmark has the largest improvement (relative QPS is 3.96)
• Large servers
• 5.7.44 has similar perf or is faster except for range-1
• The update-index microbenchmark has the largest improvement (relative QPS is > 5)

Educated guesses

• For range-2 see the explanation in the 5.7.10 results above

Results for SER4

5.7.44, SER4	min	max	avg	median
point-1	0.85	0.89	0.86	0.85
point-2	0.78	0.99	0.88	0.85
range-1	0.80	0.93	0.82	0.81
range-2	0.85	1.21	1.01	0.96
writes	0.74	1.29	0.90	0.85

Results for PN53

5.7.44, PN53	min	max	avg	median
point-1	0.86	0.92	0.89	0.88
point-2	0.86	1.02	0.92	0.88
range-1	0.83	0.90	0.86	0.84
range-2	0.90	1.23	1.04	0.99
writes	0.82	1.17	0.92	0.89

Results for C2D

5.7.44, C2D	min	max	avg	median
point-1	0.86	1.44	1.03	0.94
point-2	0.91	1.29	1.11	1.13
range-1	0.85	1.00	0.88	0.86
range-2	1.15	1.26	1.20	1.20
writes	1.45	3.96	2.46	2.49

Results for Socket2

5.7.44, Socket2	min	max	avg	median
point-1	0.88	1.54	1.10	0.99
point-2	0.98	1.36	1.18	1.22
range-1	0.78	0.87	0.85	0.86
range-2	1.04	1.26	1.14	1.12
writes	0.75	5.45	1.61	1.26

Results for Dell32

5.7.44, Dell32	min	max	avg	median
point-1	0.91	2.13	1.32	1.03
point-2	1.06	1.72	1.48	1.65
range-1	0.85	0.93	0.91	0.91
range-2	0.98	1.16	1.09	1.13
writes	1.15	5.44	1.95	1.68

Results: 8.0.11

Summary

• The base case is 5.6.35 and slower or faster is in comparison to 5.6.35
• Small servers
• 8.0.11 is ~20% slower on SER4 except for range-2
• 8.0.11 is ~20% slower on PN53 except for range-2
• 8.0.11 is 5% to 10% slower than 5.7.44. This is easier to see on the spreadsheet.
• Medium server
• 8.0.11 is slower and faster for reads and then much faster for writes
• The update-index microbenchmark has the largest improvement (relative QPS is 3.28)
• 8.0.11 is often 5% to 10% slower than 5.7.44. On the update-index benchmark the relative QPS here is 3.28 vs 3.96 in 5.7.10. This is easier to see on the spreadsheet.
• Large servers
• 8.0.11 is slower and faster for reads and then much faster for writes
• The update-index microbenchmark has the largest improvement (relative QPS is 1.24 and 1.66)
• There are big regressions from 5.7.44 to 8.0.11. The largest one might be for update-index which had a relative QPS of (5.45, 5.44) in 5.7.10 which drops to (3.58, 2.35) in 5.7.44. This is easier to see on the spreadsheet.

Results for SER4

8.0.11, SER4	min	max	avg	median
point-1	0.76	0.86	0.80	0.80
point-2	0.71	0.92	0.81	0.79
range-1	0.75	0.99	0.81	0.81
range-2	0.79	1.14	0.95	0.91
writes	0.64	1.36	0.81	0.74

Results for PN53

8.0.11, PN53	min	max	avg	median
point-1	0.77	0.83	0.80	0.81
point-2	0.78	0.93	0.84	0.82
range-1	0.77	0.95	0.82	0.80
range-2	0.84	1.17	0.98	0.93
writes	0.72	1.33	0.87	0.82

Results for C2D

8.0.11, C2D	min	max	avg	median
point-1	0.80	1.33	0.96	0.86
point-2	0.83	1.19	1.02	1.04
range-1	0.79	1.10	0.85	0.84
range-2	1.08	1.21	1.14	1.13
writes	1.27	3.28	2.09	2.14

Results for Socket2

8.0.11, Socket2	min	max	avg	median
point-1	0.84	1.50	1.05	0.94
point-2	0.91	1.25	1.08	1.12
range-1	0.81	0.90	0.86	0.87
range-2	0.98	1.22	1.07	1.07
writes	1.05	3.58	1.48	1.24

Results for Dell32

8.0.11, Dell32	min	max	avg	median
point-1	0.86	2.03	1.23	0.95
point-2	0.97	1.60	1.36	1.50
range-1	0.85	0.92	0.88	0.90
range-2	0.96	1.11	1.05	1.07
writes	1.16	2.35	1.63	1.66

Results: 8.0.37

Summary

• The base case is 5.6.35 and slower or faster is in comparison to 5.6.35
• Small servers
• 8.0.37 is 31% to 41% slower on SER4 except for range-2
• 8.0.37 is 29% to 34% slower on PN53 except for range-2
• 8.0.37 is 10% to 15% slower than 8.0.11. This is easier to see on the spreadsheet.
• Medium server
• 8.0.37 is 11% to 29% slower on most reads (point-1, point-2, range-1), has similar perf on range-2 and much faster for writes
• The update-index microbenchmark has the second the largest improvement (relative QPS is 2.75 vs 3.06 for update-inlist). Regressions are slowly reducing the improvements on the write-heavy microbenchmarks.
• 8.0.37 is often 10% slower than 8.0.11. This is easier to see on the spreadsheet.
• Large servers
• For both
• 8.0.37 is up to ~25% slower than 5.6.35
• 8.0.37 is ~10% slower than 8.0.11
• CPU regressions are slowly taking away the improvements for high-concurrency.
• For Socket2
• 8.0.37 is now slower than 5.6.35 on all microbenchmark groups except writes.
• For Dell32
• 8.0.37 is slower than 5.6.35 on some reads (point-1, range-1), faster on some reads (point-2) and similar on some reads (range-2). It remains faster on writes.

Results for SER4

8.0.37, SER4	min	max	avg	median
point-1	0.65	0.78	0.70	0.69
point-2	0.61	0.80	0.70	0.69
range-1	0.64	0.69	0.66	0.66
range-2	0.65	0.96	0.79	0.75
writes	0.47	1.02	0.62	0.59

Results for PN53

8.0.37, PN53	min	max	avg	median
point-1	0.66	0.78	0.71	0.70
point-2	0.70	0.83	0.75	0.71
range-1	0.67	0.71	0.69	0.69
range-2	0.74	0.98	0.85	0.82
writes	0.57	0.97	0.69	0.66

Results for C2D

8.0.37, C2D	min	max	avg	median
point-1	0.71	1.10	0.83	0.75
point-2	0.73	1.04	0.88	0.89
range-1	0.67	0.79	0.71	0.71
range-2	0.93	1.05	0.99	0.99
writes	1.25	3.06	1.91	1.91

Results for Socket2

8.0.37, Socket2	min	max	avg	median
point-1	0.74	1.23	0.90	0.83
point-2	0.77	1.07	0.93	0.96
range-1	0.66	0.74	0.71	0.72
range-2	0.87	1.07	0.96	0.95
writes	0.97	3.46	1.40	1.14

Results for Dell32

8.0.37, Dell32	min	max	avg	median
point-1	0.76	1.61	1.04	0.85
point-2	0.86	1.39	1.17	1.28
range-1	0.67	0.80	0.75	0.75
range-2	0.95	1.08	1.00	0.99
writes	1.08	3.03	1.66	1.55