Perf regressions in MySQL/InnoDB, a big server & sysbench, part 2

My last post has results for MySQL/InnoDB on a big server and the database fit in the InnoDB buffer pool. Here I have results where the database fits in the OS page cache but not the InnoDB buffer pool.

The context for the results is short-running queries, in-memory (cached by the OS) with high-concurrency (20 clients) on a big server (30-cores). The goals are:

1. Understand the impact of compiler optimizations
2. Document how performance has changed from MySQL 5.6 to 5.7 to 8.0
3. Document performance with fast storage (reading from the OS page cache is fast)

tl;dr

• The rel_lto build improves QPS by up to 3%
• 8.0 releases look much better here with a big server & high-concurrency than on the small server with low-concurrency.
• For changes from 5.6 to 8.0
• Point queries - version 8.0.32 gets about 3X more QPS than 5.6.51 on most of the microbenchmarks. This is much better than the previous result where the database fits in the buffer pool.
• Range queries - version 8.0.32 gets about 14% more QPS than 5.6.51. This is much better than the previous result where the database fits in the buffer pool.
• Writes - version 8.0.32 gets the same QPS as version 5.6.51. This is much worse than the previous result where the database fits in the buffer pool.

Benchmark

A description of how I run sysbench is here. Tests use the a c2-standard-60 server on GCP with 30-cores, hyperthreading disabled, 240G RAM and 3TB of local attached NVMe. The sysbench tests were run for 20 clients, 600 seconds per microbenchmark using 4 tables with 50M rows per table. All tests use the InnoDB storage engine. The test database fits in the InnoDB buffer pool.

I used a similar configuration (my.cnf) for all versions which is here for 5.6, 5.7, 8.0.2x and 8.0.3x.

Builds

I tested MySQL versions 5.6.51, 5.7.40, 8.0.22, 8.0.28, 8.0.31 and 8.0.32 using multiple builds for each version. For each build+version the full set of sysbench microbenchmarks was repeated. More details on the builds are in the previous post. To save time I only tested all builds for 8.0.31 and for other versions used the rel_lto build.

Results: all versions

The spreadsheet is here. See the 56_to_80.redo.4g tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 5.6.51 using the rel_lto build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. There is much variance within each of the microbenchmark groups:

• Point queries - most of the microbenchmarks get about 3X more QPS in 8.0 than 5.6. The exceptions are hot-points_range=100, point-query.pre_range=100 and point-query.range=100. Two of the exceptions select one row per query while the microbenchmarks that are 3X faster tend to have a large in-list.
• Range queries - most of the microbenchmarks have a relative throughput between 0.8 and 1.2 with 8.0 compared to 5.6.51. There are two outliers that are more than 3X faster in 8.0 -- range-notcovered-si.pre_range=1000 and range-notcovered-si_range=1000 which use oltp_points_covered.lua. The two exceptions do a range scan on a non-covering secondary index so there will be more reads from the OS page cache for these.
• Writes - there is not much variance in the microbenchmarks except for read-write* which use the classic sysbench transaction that includes range queries. Perhaps their improvement in 8.0 vs 5.6 is mostly do to the improvements in range queries, but their cousin (read-only*) which uses the same SQL excluding the writes doesn't show such an improvement. This is a mystery.

Summary statistics:

my5651_rel	my5740_rel_lto	my8022_rel_lto	my8028_rel_lto	my8031_rel_lto	my8032_rel_lto
Point: avg	1.71	2.67	2.75	2.71	2.70
Point: median	1.58	2.88	2.98	3.15	3.19
Point: min	1.03	1.02	1.01	0.99	0.98
Point: max	5.11	3.73	3.76	3.70	3.37
Point: stddev	0.897	0.794	0.861	0.844	0.840
Range: avg	1.29	1.47	1.50	1.42	1.42
Range: median	1.25	1.24	1.19	1.15	1.14
Range: min	0.81	0.71	0.73	0.68	0.62
Range: max	2.29	3.47	3.60	3.44	3.44
Range: stddev	0.368	0.834	0.897	0.847	0.858
Write: avg	1.22	1.09	1.07	1.06	1.06
Write: median	1.18	1.02	1.01	1.00	1.01
Write: min	0.98	0.89	0.95	0.94	0.95
Write: max	1.52	1.55	1.33	1.33	1.33
Write: stddev	0.200	0.220	0.142	0.134	0.132

Results: MySQL 8.0.31

The spreadsheet is here. See the my8031.redo.4g tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 8.0.31 using the rel_withdbg build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. For each group of microbenchmarks:

• Point queries - there is little variance across the microbenchmarks
• Range queries - the full table scan test (scan_range=10) shows the best improvement from the rel_lto build. I don't understand the noisy result for the read-only_range=10000 microbenchmark. Perhaps buffer pool writeback was still in progress as that microbenchmark is run shortly after the write-heavy microbenchmarks.
• Writes -  there is little variance across the microbenchmarks

Summary statistics:

rel_withdbg	rel_o2	rel_native	rel	rel_o2_lto	rel_native_lto	rel_lto
Point: avg	0.98	1.02	1.02	1.01	1.03	1.03
Point: median	0.99	1.02	1.02	1.01	1.03	1.03
Point: min	0.95	0.96	0.97	1.00	1.01	1.01
Point: max	1.01	1.03	1.03	1.02	1.06	1.05
Point: stddev	0.017	0.021	0.018	0.007	0.011	0.011
Range: avg	0.98	0.98	0.98	1.00	1.00	1.01
Range: median	0.97	0.98	0.98	1.01	1.01	1.02
Range: min	0.96	0.89	0.86	0.93	0.90	0.81
Range: max	1.07	1.06	1.03	1.02	1.04	1.11
Range: stddev	0.028	0.040	0.038	0.021	0.034	0.061
Write: avg	0.98	0.98	0.99	0.99	0.99	1.00
Write: median	0.98	0.98	0.99	0.98	0.98	0.99
Write: min	0.97	0.98	0.98	0.98	0.98	0.99
Write: max	0.98	0.99	1.00	1.00	1.01	1.01
Write: stddev	0.003	0.005	0.006	0.007	0.011	0.008