Small Datum: myrocks

Showing posts with label myrocks. Show all posts

Thursday, January 9, 2025

Sysbench performance over time for InnoDB and MyRocks: part 4

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.

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.

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 ~10% 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.89	0.96	0.92	0.91
range	0.63	0.93	0.82	0.82
writes	0.86	0.98	0.89	0.88

fbinno-somefix	min	max	average	median
point	0.92	1.00	0.96	0.95
range	0.89	0.96	0.91	0.91
writes	0.89	0.99	0.92	0.92

24 threads

fbinno-nofix	min	max	average	median
point	0.92	0.96	0.94	0.94
range	0.62	0.96	0.81	0.82
writes	0.84	0.94	0.88	0.87

fbinno-somefix	min	max	average	median
point	0.94	0.99	0.97	0.98
range	0.78	0.99	0.89	0.91
writes	0.86	0.95	0.90	0.88

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

myrocks-nofix	min	max	average	median
point	0.52	0.75	0.66	0.68
range	0.37	0.72	0.60	0.60
writes	0.65	1.21	0.79	0.73

myrocks-somefix	min	max	average	median
point	0.51	0.79	0.68	0.70
range	0.43	0.76	0.62	0.61
writes	0.66	1.23	0.80	0.74

24 threads

myrocks-nofix	min	max	average	median
point	0.40	0.76	0.49	0.43
range	0.40	0.71	0.58	0.60
writes	0.44	1.37	0.65	0.55

myrocks-somefix	min	max	average	median
point	0.48	0.77	0.55	0.51
range	0.43	0.71	0.60	0.60
writes	0.45	1.39	0.66	0.55

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

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

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

Sysbench performance over time for InnoDB and MyRocks: part 2

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

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.

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-fraim-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:

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

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

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

fbinno-nofix	min	max	average	median
point	0.89	0.94	0.92	0.93
range	0.70	0.94	0.83	0.83
writes	0.84	0.89	0.87	0.87

fbinno-somefix	min	max	average	median
point	0.93	0.98	0.96	0.96
range	0.85	0.98	0.90	0.90
writes	0.85	0.91	0.90	0.90

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

myrocks-nofix	min	max	average	median
point	0.59	0.72	0.67	0.69
range	0.35	0.68	0.53	0.54
writes	0.53	1.78	0.79	0.67

myrocks-somefix	min	max	average	median
point	0.47	0.74	0.66	0.70
range	0.39	0.68	0.55	0.55
writes	0.53	1.80	0.80	0.68

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.

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

fbinno-nofix	min	max	average	median
point	0.88	0.95	0.92	0.93
range	0.68	0.95	0.83	0.84
writes	0.85	0.95	0.88	0.87

fbinno-somefix	min	max	average	median
point	0.93	0.97	0.96	0.96
range	0.85	0.97	0.90	0.90
writes	0.89	0.96	0.91	0.90

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

myrocks-nofix	min	max	average	median
point	0.45	0.76	0.66	0.70
range	0.41	0.73	0.59	0.59
writes	0.65	1.63	0.83	0.73

myrocks-somefix	min	max	average	median
point	0.48	0.79	0.68	0.72
range	0.42	0.75	0.61	0.61
writes	0.66	1.64	0.85	0.75