MyRocks vs InnoDB on cached sysbench

This post compares MyRocks and InnoDB using sysbench with a cached database. The first goal is to understand the differences in CPU overhead between MyRocks and InnoDB. The second goal is to identify things that can be improved in MyRocks.

tl;dr

• There are small regressions from MyRocks 5.6 to 8.0, usually <= 10%. I assume most of this is from changes in upstream MySQL that are in code above the storage engine layer.
• MyRocks uses more CPU than InnoDB on cached sysbench, thus InnoDB gets more QPS. While many conference papers explain IO read amplification as an issue for an LSM, the more likely issue is CPU read amplification as shown in the results here.
• There are a few yet to be explained issues that hurt MyRocks write throughput on a 2-socket server
  
• There is one microbenchmark for which MyRocks was (almost) always much faster than InnoDB -- update-index. The reason is that non-unique secondary index maintenance is read free with MyRocks (all done via a few Put() operations).

Updates:

• changing the CPU frequency governor makes QPS ~2X better on the worst-case results with the Socket2 server (see here)

Builds

I compiled both upstream and FB MySQL from source and tested the following builds:

• MySQL 8.0.37 - I used InnoDB from upstream MySQL 8.0.37
• MyRocks 5.6.35 - FB MyRocks 5.6.35 at git sha 4f3a57a1 (as of 23/10/16) with RocksDB 8.7.0
• MyRocks 8.0.28 - FB MyRocks 8.0.28 at git sha 4edf1eec_(as of 23/12/02) with RocksDB 8.7.0
• MyRocks 8.0.32 - FB MyRocks 8.0.32 at git sha 49b37dfe (as of 24/05/29) with RocksDB 9.2.1

Hardware

I tested on five servers:

• Small servers
• SER4 - Beelink SER 4700u (see here) with 8 cores and a Ryzen 7 4700u CPU 
• PN53 - ASUS ExpertCenter PN53 (see here) with 8 cores and an AMD Ryzen 7 7735HS CPU. The CPU on the PN53 is newer than the CPU on the SER4.
• Medium server
• C2D - a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU and 16 cores
• Big servers
• Socket2 - SuperMicro SuperWorkstation (see here) with 2-sockets, 12 cores/socket, 64G RAM and ext4 (SW RAID 0 over 2 NVMe devices)
• Dell32 - Dell Precision 7865 Tower (see here) with a 32-core AMD Ryzen Threadripper PRO 5975WX, 128G RAM and ext4 (SW RAID 0 over 2 NVMe devices)

All servers use Ubuntu 22.04 with ext4. AMD SMT and Intel HT are disabled in all cases.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by MyRocks and InnoDB.

The benchmark is run with:

• SER4, PN53 - 1 thread, 1 table and 30M rows
• C2D - 12 threads, 8 tables and 10M rows per table
• Socket2 - 16 threads, 8 tables and 10M rows per table
• Dell32 - 24 threads, 8 tables and 10M rows per table
• each microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise
• prepared statements were enabled

The command lines for my helper scripts were:

# PN53, SER4 -> 1 client

bash r.sh 1 30000000 300 600 nvme0n1 1 1 1

# C2D -> 12 clients

bash r.sh 8 10000000 300 600 md0 1 1 12

# Socket2 -> 16 clients

bash r.sh 8 10000000 300 600 md0 1 1 16

# Dell32 -> 24 clients

bash r.sh 8 10000000 300 600 md0 1 1 24

Results

For the results below I split the 42 microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. The spreadsheet with all data is here. For each microbenchmark group there is a table with summary statistics. I don't have charts because that would use too much space, but the results per microbenchmark are in the spreadsheets.

The numbers in the spreadsheets and the tables below are the relative QPS which is (QPS for my version) / (QPS for base case). When the relative throughput is > 1 then that version is faster than the base case.

For all results below the base case is InnoDB from MySQL 8.0.37.

Results: SER4

This server has 8 cores. The benchmark is run with 1 client (thread, connection). The numbers in the tables are the relative QPS as explained above.

Summary:

• There are regressions in MyRocks from 5.6.35 to 8.0. I assume most are from upstream in code above the storage engine layer. Regressions are largest for writes.
• The worst-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.53) points-covered-si_range=100
• point-2 - (0.67) random-points_range=100
• range-1 - (0.46) scan_range=100
• range-2 - (0.63) read-only_range=100
• writes - (0.59) update-inlist_range=100
• The best-case for MyRocks 8.0.32 vs InnoDB
• Note that all of the best-case results occur for the pre microbenchmarks that are run before the write-heavy tests which fragment the LSM tree.
• point-1 - (0.77) point-query.pre_range=100
• point-2 - (0.78) random-points.pre_range=10
• range-1 - (0.65) range-notcovered-si.pre_range=100
• range-2 - (0.71) read-only.pre_range=10
• writes - (1.80) update-index_range=100

myrocks-5.6.35	min	max	avg	median
point-1	0.44	1.00	0.75	0.75
point-2	0.72	0.86	0.80	0.81
range-1	0.58	0.76	0.67	0.68
range-2	0.68	0.89	0.77	0.77
writes	0.71	2.39	1.08	0.95
myrocks-8.0.28	min	max	avg	median
point-1	0.41	0.78	0.64	0.66
point-2	0.36	0.77	0.59	0.66
range-1	0.46	0.65	0.57	0.57
range-2	0.64	0.75	0.68	0.68
writes	0.61	1.92	0.88	0.78
myrocks-8.0.32	min	max	avg	median
point-1	0.53	0.77	0.69	0.71
point-2	0.67	0.78	0.72	0.73
range-1	0.46	0.65	0.56	0.57
range-2	0.63	0.71	0.66	0.66
writes	0.59	1.80	0.83	0.73

Results: PN53

This server has 8 cores. The benchmark is run with 1 client (thread, connection). The numbers in the tables are the relative QPS as explained above.

Summary:

• There are regressions in MyRocks from 5.6.35 to 8.0. I assume most are from upstream in code above the storage engine layer. Regressions are largest for writes.
• The worst-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.47) points-covered-si_range=100
• point-2 - (0.67) random-points_range=100
• range-1 - (0.45) scan_range=100 (0.45)
• range-2 - (0.70) read-only_range=100
• writes - (0.65) update-inlist_range=100
• The best-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.79) point-query.pre_range=100, points-covered-pk.pre_range=100, points-notcovered-pk.pre_range=100
• point-2 - (0.80) random-points.pre_range=1000
• range-1 - (0.70) range-notcovered-si.pre_range=100
• range-2 - (0.78) read-only.pre_range=10
• writes - (1.54) update-index_range=100

myrocks-5.6.35	min	max	avg	median
point-1	0.66	0.95	0.81	0.80
point-2	0.74	0.87	0.80	0.78
range-1	0.59	0.73	0.68	0.71
range-2	0.73	0.87	0.79	0.78
writes	0.78	1.83	1.01	0.90
myrocks-8.0.28	min	max	avg	median
point-1	0.38	0.83	0.61	0.63
point-2	0.35	0.78	0.56	0.59
range-1	0.48	0.71	0.62	0.62
range-2	0.71	0.80	0.75	0.75
writes	0.66	1.60	0.87	0.79
myrocks-8.0.32	min	max	avg	median
point-1	0.47	0.79	0.70	0.73
point-2	0.67	0.80	0.74	0.74
range-1	0.45	0.70	0.60	0.61
range-2	0.70	0.78	0.73	0.73
writes	0.65	1.54	0.84	0.76

Results: C2D

This server has 16 cores. The benchmark is run with 12 clients (threads, connections). The numbers in the tables are the relative QPS as explained above.

Summary:

• Regressions from MyRocks 5.6.35 to MyRocks 8.0 are smaller here (with more concurrency) than for the small servers (with low concurrency)
• The worst-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.46) points-covered-si_range=100
• point-2 - (0.57) random-points_range=10
• range-1 - (0.35) scan_range=100
• range-2 - (0.60) read-only_range=100
• writes - (0.54) delete_range=100
• The best-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.77) point-query.pre_range=100, points-covered-pk.pre_range=100, points-notcovered-pk.pre_range=100
• point-2 - (0.78) random-points.pre_range=10
• range-1 - (0.70) range-notcovered-si.pre_range=100
• range-2 - (0.72) read-only.pre_range=10
• writes - (1.94) update-index_range=100

myrocks-5.6.35	min	max	avg	median
point-1	0.43	0.94	0.64	0.67
point-2	0.52	0.78	0.62	0.57
range-1	0.46	0.65	0.51	0.49
range-2	0.58	0.73	0.64	0.63
writes	0.65	1.87	0.86	0.72
myrocks-8.0.28	min	max	avg	median
point-1	0.44	0.82	0.62	0.67
point-2	0.35	0.80	0.58	0.60
range-1	0.36	0.71	0.59	0.63
range-2	0.64	0.74	0.68	0.68
writes	0.67	1.98	0.89	0.77
myrocks-8.0.32	min	max	avg	median
point-1	0.46	0.77	0.65	0.71
point-2	0.57	0.78	0.69	0.73
range-1	0.35	0.70	0.58	0.60
range-2	0.60	0.72	0.65	0.64
writes	0.54	1.94	0.84	0.74

Results: Socket2

This server has 24 cores. The benchmark is run with 16 clients (threads, connections). The numbers in the tables are the relative QPS as explained above.

Updates:

• changing the CPU frequency governor makes QPS ~2X better on the worst-case results with the Socket2 server (see here)

Summary:

• Regressions from MyRocks 5.6.35 to MyRocks 8.0 are smaller here (with more concurrency) than for the small servers (with low concurrency)
• MyRocks does much worse for writes relative to InnoDB here (large server) than above on the small and medium servers. The update-index microbenchmark is much faster in MyRocks than InnoDB above and below on the Dell32 server, but here no the only 2-socket server it is slower.That will be explained in a future post.
• The worst-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.48) points-covered-si_range=100
• point-2 - (0.62) random-points_range=100
• range-1 - (0.48) scan_range=100
• range-2 - (0.71) read-only_range=100
• writes - (0.24) insert_range=100
• The best-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.83) points-notcovered-pk.pre_range=100
• point-2 - (0.86) random-points.pre_range=1000
• range-1 - (0.77) range-notcovered-si.pre_range=100
• range-2 - (0.81) read-only.pre_range=10000
• writes - (0.78) write-only_range=10000

myrocks-5.6.35	min	max	avg	median
point-1	0.52	0.93	0.73	0.78
point-2	0.59	0.87	0.71	0.67
range-1	0.55	0.78	0.63	0.62
range-2	0.67	0.76	0.72	0.72
writes	0.28	0.78	0.45	0.31
myrocks-8.0.28	min	max	avg	median
point-1	0.49	0.83	0.68	0.76
point-2	0.44	0.81	0.64	0.62
range-1	0.51	0.79	0.64	0.65
range-2	0.73	0.81	0.77	0.77
writes	0.25	0.81	0.45	0.30
myrocks-8.0.32	min	max	avg	median
point-1	0.48	0.83	0.69	0.75
point-2	0.62	0.86	0.73	0.72
range-1	0.48	0.77	0.62	0.61
range-2	0.71	0.81	0.75	0.75
writes	0.24	0.78	0.44	0.32

Results: Dell 32 

This server has 32 cores. The benchmark is run with 24 clients (threads, connections). The numbers in the tables are the relative QPS as explained above.

Summary:

• Regressions from MyRocks 5.6.35 to MyRocks 8.0 are smaller here (with more concurrency) than for the small servers (with low concurrency)
• MyRocks does much worse for writes relative to InnoDB here (large server) than above on the small and medium servers. That will be explained in a future post.
• The worst-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.44) points-notcovered-si_range=100
• point-2 - (0.49) random-points_range=100, random-points_range=1000
• range-1 - (0.44) scan_range=100
• range-2 - (0.67) read-only_range=100
• writes - (0.41) update-inlist_range=100
• The best-case for MyRocks 8.0.32 vs InnoDB
• point-1 - (0.80) point-query.pre_range=100, points-covered-si.pre_range=100
• point-2 - (0.80) random-points.pre_range=1000
• range-1 - (0.73) range-notcovered-si.pre_range=100
• range-2 - (0.78) read-only.pre_range=10000
• writes - (1.21) update-index_range=100

myrocks-5.6.35	min	max	avg	median
point-1	0.44	0.91	0.65	0.73
point-2	0.47	0.80	0.62	0.60
range-1	0.43	0.72	0.57	0.56
range-2	0.65	0.82	0.72	0.70
writes	0.48	1.36	0.68	0.58
myrocks-8.0.28	min	max	avg	median
point-1	0.43	0.83	0.62	0.71
point-2	0.36	0.79	0.58	0.55
range-1	0.44	0.75	0.61	0.63
range-2	0.69	0.79	0.74	0.73
writes	0.40	1.23	0.62	0.52
myrocks-8.0.32	min	max	avg	median
point-1	0.44	0.80	0.64	0.74
point-2	0.49	0.80	0.66	0.70
range-1	0.44	0.73	0.59	0.60
range-2	0.67	0.78	0.72	0.71
writes	0.41	1.21	0.61	0.51