This is part 2 in my (possibly) final series on performance regressions in MySQL using cached sysbench as the workload. Part 1 of this series is here. Part 1 documents performance regressions from MySQL 5.6 to 8.0. This post and the ones that follow cover performance differences between InnoDB in upstream MySQL 8.0.32, InnoDB in FB MySQL 8.0.32 and MyRocks in FB MySQL 8.0.32.
I don't claim that the MyRocks CPU overhead isn't relevant, but this workload (CPU-bound, database is cached) is a worst-case for it.
tl;dr
- InnoDB in FB MySQL 8.0.32 is about 10% slower than InnoDB from upstream 8.0.32, once a few perf bugs get fixed
- MyRocks in FB MySQL 8.0.32 uses more CPU than InnoDB, thus QPS for CPU-bound workloads is lower than for InnoDB. On the c8r16 server it gets between 55% and 70% of the QPS relative to InnoDB. On the c8r32 server it gets between 61% and 75% of the QPS relative to InnoDB.
- Fixing bug 1506 is important for InnoDB in FB MySQL
- MyRocks does much better at update-index because it does blind writes rather than RMW for non-unique secondary index maintenance
Much more detail on the benchmark and hardware is here. I am trying to avoid repeating that information in the posts that follow.
Tests were run on four different servers. Results in this post are only from c8r16 and c8r32. Posts that follow will have results for c24r64 and c32r128. The servers are:
- c8r16
- The c8r16 name stands for 8 CPU cores and 16G of RAM.
- c8r32
- The c8r32 name stands for 8 CPU cores and 32G of RAM.
- c24r64
- The c24r64 name stands for 24 CPU cores and 64G of RAM.
- c32r128
- The c32r128 name stands for 32 CPU cores and 128G of RAM.
Builds
In part 1 results were provided for InnoDB from upstream MySQL 5.6, 5.7 and 8.0 and then from MyRocks from FB MySQL 5.6 and 8.0. Here and in the posts that follow I use InnoDB from upstream MySQL 8.0.32, InnoDB from FB MySQL 8.0.32 and MyRocks from FB MySQL 8.0.32. Everything was compiled with gcc, CMAKE_BUILD_TYPE =Release, -O2 and -fno-omit-frame-pointer.
The encoded names for these builds is:
- my8032_rel_o2nofp
- InnoDB from upstream MySQL 8.0.32
- fbmy8032_rel_o2nofp_end_241023_ba9709c9_971
- FB MySQL 8.0.32 at git hash ba9709c9 (as of 2024/10/23) using RocksDB 9.7.1. This supports InnoDB and MyRocks.
- fbmy8032_rel_o2nofp_241023_ba9709c9_971_bug1473_1481_1482_1506
- FB MySQL 8.0.32 at git hash ba9709c9 (as of 2024/10/23) using RocksDB 9.7.1 with patches applied for bugs 1473, 1481, 1482 and 1506, This supports InnoDB and MyRocks.
The my.cnf files are:
The bugs mentioned above are specific to FB MySQL and have simple fixes:
- 1473
- is_thd_db_read_only_by_name accounts for ~2% of CPU time on CPU-bound and write-heavy microbenchmarks
- 1481
- ha_statistic_increment accounts for ~5% of CPU time on CPU-bound table scans
- 1482
- calls to clock_gettime in sql/optimizer.cc account for ~4% of CPU time on several microbenchmarks
- 1505
- the default for yield_check_frequency is zero which makes MySQL waste much time in calls to thd_wait_yield. A better default is 10. Too many my.cnf options isn't a big deal. Too many options with bad default values is a big deal. The workaround for this is to set yield_check_frequency=10 in my.cnf
- 1506
- this is limited to InnoDB in the FB MySQL tree. It is from changes in the concurrency ticket code and reduces CPU-bound table scan performance by ~20%.
Relative QPS
The charts and summary statistics that follow use a number that I call the relative QPS (rQPS) where:
- rQPS is: (QPS for my version) / (QPS for base version)
- base version is InnoDB from upstream MySQL 8.0.32 (my8032_rel_o2nofp)
- my version is one of the other versions
Results
The microbenchmarks are split into three groups: point queries, range queries, writes. The tables below have summary statistics for InnoDB and MyRocks using the relative QPS (the same data as the charts).
Results are provided in two formats: charts and summary statistics. The summary statistics table have the min, max, average and median relative QPS per group (group = point, range and writes).
The spreadsheets and charts are also here. I don't know how to prevent the microbenchmark names on the x-axis from getting truncated in the png files I use here but they are easier to read on the spreadsheet.
The charts use NT=1, NT=16 and NT=24 to indicate whether sysbench was run with 1, 16 or 24 threads. The charts and table use the following abbreviations for the DBMS versions:
- fbinno-nofix
- InnoDB from fbmy8032_rel_o2nofp_end_241023_ba9709c9_971
- fbinno-somefix
- InnoDB from fbmy8032_rel_o2nofp_241023_ba9709c9_971_bug1473_1481_1482_1506
- myrocks-nofix
- MyRocks from fbmy8032_rel_o2nofp_end_241023_ba9709c9_971
- myrocks-somefix
- MyRocks from fbmy8032_rel_o2nofp_241023_ba9709c9_971_bug1473_1481_1482_1506
Results: c8r16 with InnoDB
Summary:
- The y-axis on the charts doesn't start at 0 to improve readability
- InnoDB from FB MySQL with the bug fixes listed above is between 4% and 10% slower than InnoDB from upstream
- The worst regression for InnoDB from FB MySQL occurs on long range scans where FB MySQL is ~30% slower than upstream without the fix for bug 1506
Results: c8r16 with MyRocks
Summary
- The y-axis on most of the charts doesn't start at 0 to improve readability. There are two charts for writes and the second truncates outliers to improve readability.
- MyRocks has more CPU overhead per query than InnoDB, thus CPU-bound QPS is much lower with MyRocks than with InnoDB. Improving this isn't trivial as flamegraphs show that the extra CPU is spread out over many functions.
- Results for range queries are worse than for point queries because MyRocks uses bloom filters for point queries
- MyRocks does much better at update-index because it does blind writes rather than RMW for non-unique secondary index maintenance
There are two charts for writes. The second chart truncates the y-axis to improve readability for all but the outlier result from update-index.
.png)
Results: c8r32 with InnoDB
Summary
- The y-axis on the charts doesn't start at 0 to improve readability.
- InnoDB from FB MySQL with the bug fixes listed above is between 4% and 10% slower than InnoDB from upstream
- The worst regression for InnoDB from FB MySQL occurs on long range scans where FB MySQL is ~30% slower than upstream without the fix for bug 1506
Results: c8r32 with MyRocks
Summary
- The y-axis on most of the charts doesn't start at 0 to improve readability. There are two charts for writes and the second truncates outliers to improve readability.
- MyRocks has more CPU overhead per query than InnoDB, thus CPU-bound QPS is much lower with MyRocks than with InnoDB. Improving this isn't trivial as flamegraphs show that the extra CPU is spread out over many functions.
- Results for range queries are worse than for point queries because MyRocks uses bloom filters for point queries
- MyRocks does much better at update-index because it does blind writes rather than RMW for non-unique secondary index maintenance
There are two charts for writes. The second chart truncates the y-axis to improve readability for all but the outlier result from update-index.
.png)
No comments:
Post a Comment