This is part 4 in my (possibly) final series on performance regressions in MySQL using cached sysbench as the workload. For previous posts, see part 1, part 2 and part 3. This post covers 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 using a server with 32 cores and 128G of RAM.
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 from FB MySQL is no worse than ~10% slower than InnoDB from upstream
- Fixing bug 1506 is important for InnoDB in FB MySQL
- MyRocks is ~30% slower than upstream InnoDB at low concurrency and ~45% slower at high, as it uses ~1.5X more CPU/query
- For writes, MyRocks does worse at high concurrency than at low
Updates: For writes, MyRocks does worse at high concurrency than at low
I looked at vmstat metrics for the update-nonindex benchmark and the number of context switches per update is about 1.2X larger for MyRocks vs InnoDB at high concurrency.
Then I looked at PMP stacks and MyRocks has more samples for commit processing. The top stacks are here. This should not be a big surprise because MyRocks does more work at commit time (pushes changes from a per-session buffer into the memtable). But I need to look at this more closely.
I browsed the code in Commit_stage_manager::enroll_for, which is on the call stack for the mutext contention, and it is kind of complicated. I am trying to figure out how many mutexes are locked in there and figuring that out will take some time.
Benchmark, Hardware
Much more detail on the benchmark and hardware is here. I am trying to avoid repeating that information in the posts that follow.
Much more detail on the benchmark and hardware is here. I am trying to avoid repeating that information in the posts that follow.
Results here are from the c32r128 server with 32 CPU cores and 128G of RAM. The benchmarks were repeated for 1 and 24 threads. On the charts below that is indicated by NT=1 and NT=24.
Builds
The previous post has more detail on the builds, my.cnf files and bug fixes.
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.
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
Summary statistics: InnoDB
Summary:
- InnoDB from FB MySQL is no worse than ~10% slower than InnoDB from upstream
- Fixing bug 1506 is important for InnoDB in FB MySQL
1 thread
24 threads
Summary statistics: MyRocks
Summary:
- MyRocks does better at low concurrency than at high. The fix might be as simple as enabling the hyper clock block cache
- MyRocks is ~30% slower than upstream InnoDB at low concurrency and ~45% slower at high
- For writes, MyRocks does worse at high concurrency than at low
1 thread
24 threads
Results: c32r128 with InnoDB and point queries
Summary
- InnoDB from FB MySQL is no worse than 10% slower than upstream
Results: c32r128 with MyRocks and point queries
Summary
- at low concurrency the worst case for MyRocks are the tests that do point lookup on secondary indexes because that uses a range scan rather than a point lookup on the LSM tree, which means that bloom filters cannot be used
- at high concurrency the difference between primary and secondary index queries is less significant, perhaps this is dominated by mutex contention from the LRU block cache and solved by using hyper clock
Results: c32r128 with InnoDB and range queries
Summary
- the worst case for InnoDB from FB MySQL are the long range scans and fixing bug 1506 will be a big deal
Results: c32r128 with MyRocks and range queries
Summary
- while long range scans are the worst case here, bug 1506 is not an issue as that is InnoDB-only
Results: c32r128 with InnoDB and writes
Summary
- results are stable here, InnoDB from FB MySQL is no worse than ~10% slower than upstream but results at high concurrency are a bit worse than at low
Results: c32r128 with MyRocks and writes
Summary
- while MyRocks does much better than InnoDB for update-index because it does blind writes rather than RMW for non-unique secondary index maintenance
- MyRocks does worse at high concurrency than at low
No comments:
Post a Comment