Perf regressions in MySQL from 5.6.21 to 8.0.35 using sysbench and a medium server, part 1

This post has results from sysbench for all releases of MySQL 8.0 and 5.7 along with some releases of 5.6. All tests used InnoDB. I previously shared results from a small server and here I share results for both the medium and small servers -- they are similar. The goal is to document where things get faster or slower over time for a medium-concurrency and CPU-bound workload. The focus is on CPU regressions. 

Because I include graphs from the medium and small servers there are more graphs so I will split my report into several blog posts. This post has results comparing some releases from each of 5.6, 5.7 and 8.0.

tl;dr

• Results like this are motivation for using changepoint detection as part of the upstream dev cycle
• Bug 102037 was fixed in 8.0.31. This arrived in 8.0.22 and was reported by me with help from sysbench. With the fix, QPS is doubled for SELECT statements with large inlists. See the results below in Point queries, part 2
• MySQL 8.0 uses more CPU/operation than MySQL 5.6
• For point queries, MySQL 8.0 gets ~75% of the QPS vs MySQL 5.6
• For range queries without aggregation, MySQL 8.0 gets 60% to 70% of the QPS vs MySQL 5.6
• For range queries with aggregation, MySQL 8.0 and 5.6 get similar QPS. Perf for MySQL 5.7 is much better than 5.6, but with new CPU overheads in 8.0 that benefit is fading.
• For writes, MySQL 8.0 gets up to 4X more QPS than MySQL 5.6 but the perf improvements for writes. are degrading as new releases add new CPU overhead

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

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

The benchmark was run via: bash r.sh 4 20000000 600 1200 md127 1 1 8

Results

For the results below I split the benchmark steps into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query groups part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. The spreadsheet with all data and charts is here and is easier to read. A spreadsheet with all results for the small server is here.

All of the charts have relative throughput on the y-axis where that is (QPS for $me) / (QPS for $base), $me is a version (for example 5.7.24) and $base is the base version. The base version is the oldest version being compared and the goal is to see whether QPS is better or worse over time. The y-axis doesn't start at zero to improve readability.

The results here include some releases from each of MySQL 5.6, 5.7 and 8.0. The goal is to show how performance has changed over time. Each section has two graphs -- one from the medium server, one from the small server.

Point queries, part 1

Summary

• MySQL 8.0 gets ~75% of the throughput relative to MySQL 5.6
• Ignoring bug fixes, like the benefit  from fixing bug 102037 in MySQL 8.0.31, the perf regressions are mostly slow and steady with new new release.

Point queries, part 2

Summary

• MySQL 8.0 gets ~75% of the throughput relative to MySQL 5.6
• Ignoring bug fixes, like the benefit  from fixing bug 102037 in MySQL 8.0.31, the perf regressions are mostly slow and steady with new new release.

Range queries, part 1

Summary

• MySQL 8.0 gets 60% to 70% of the throughput relative to MySQL 5.6
• Perf regressions are mostly slow and steady with new new release

Range queries, part 2

Summary

• MySQL 8.0 and 5.6 get similar throughput
• Perf regressions are mostly slow and steady with new new release. Perf for MySQL 5.7 is much better than 5.6 but that benefit is slowly fading in 8.0 because there is new CPU overhead.

Writes

Summary

• MySQL 8.0 gets up to 4X more QPS than MySQL 5.6
• Perf regressions are mostly slow and steady with new new release and the difference between modern MySQL and MySQL 5.6 is shrinking.