Sysbench performance over time for InnoDB and MyRocks: part 3

This is part 3 in my (possibly) final series on performance regressions in MySQL using cached sysbench as the workload. For previous posts, see part 1 and part 2. 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 24 cores and 64G 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
• MyRocks is ~35% slower than InnoDB from upstream as it uses ~1.5X more CPU/query
• Fixing bug 1506 is important for InnoDB in FB MySQL
• For writes, MyRocks does worse at high concurrency than at low
• while MyRocks does much better than InnoDB for update-index at 1 thread, that benefit goes away at 16 threads

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. 

Results here are from the c24r64 server with 24 CPU cores and 64G of RAM. The benchmarks were repeated for 1 and 16 threads. On the charts below that is indicated by NT=1 and NT=16.

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.

The my.cnf files are:

• my.cnf.cz11a_$x for InnoDB from upstream MySQL for c8r16, c8r32, c24r64, c32r128
• my.cnf.cia1_$x for InnoDB from FB MySQL for c8r16, c8r32, c24r64, c32r128
• my.cnf.cza2_$x for MyRocks from FB MySQL for c8r16, c8r32, c24r64, c32r128

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 9% slower than InnoDB from upstream
• Fixing bug 1506 is important for InnoDB in FB MySQL

1 thread

fbinno-nofix	min	max	average	median
point	0.88	1.01	0.94	0.95
range	0.68	0.97	0.83	0.83
writes	0.86	0.95	0.90	0.89
fbinno-somefix	min	max	average	median
point	0.94	1.05	0.97	0.96
range	0.88	1.03	0.92	0.91
writes	0.88	0.96	0.92	0.93

16 threads

fbinno-nofix	min	max	average	median
point	0.93	0.96	0.94	0.94
range	0.65	0.95	0.83	0.85
writes	0.88	0.94	0.91	0.91
fbinno-somefix	min	max	average	median
point	0.94	0.97	0.95	0.95
range	0.85	0.96	0.91	0.91
writes	0.89	0.96	0.92	0.91

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 ~35% slower than upstream InnoDB
• For writes, MyRocks does worse at high concurrency than at low

1 thread

myrocks-nofix	min	max	average	median
point	0.46	0.78	0.67	0.70
range	0.48	0.73	0.63	0.64
writes	0.65	1.49	0.81	0.73
myrocks-somefix	min	max	average	median
point	0.46	0.78	0.66	0.69
range	0.51	0.73	0.65	0.64
writes	0.66	1.54	0.82	0.74

16 threads

myrocks-nofix	min	max	average	median
point	0.52	0.77	0.63	0.63
range	0.46	0.73	0.63	0.61
writes	0.51	1.01	0.67	0.61
myrocks-somefix	min	max	average	median
point	0.55	0.79	0.63	0.62
range	0.53	0.74	0.65	0.65
writes	0.50	1.01	0.67	0.62

Results: c24r64 with InnoDB and point queries

Summary

• results are stable here, InnoDB from FB MySQL is no worse than 10% slower than upstream

Results: c24r64 with MyRocks and point queries

Summary

• 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

Results: c24r64 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: c24r64 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: c24r64 with InnoDB and writes

Summary

• results are stable here, InnoDB from FB MySQL is no worse than ~10% slower than upstream

Results: c24r64 with MyRocks and writes

Summary

• while MyRocks does much better than InnoDB for update-index at 1 thread, that benefit goes away at 16 threads. It does better at update-index because it does blind writes rather than RMW for non-unique secondary index maintenance. Perhaps the issue at high concurrency is memory system stalls because this server has 2 sockets.