MyRocks vs InnoDB on cached sysbench: revised

A few weeks ago I shared results for sysbench with InnoDB and MyRocks on a variety of servers. The worst-case for MyRocks occurred on a 2-socket server with the write microbenchmarks. After some performance debugging I learned that changing the CPU frequency governor from schedutil to performance increased QPS by ~2X for the worst cases (see here) with MyRocks. Note that for Ubuntu 22.04 the default for the CPU frequency governor is schedutil.

This blog post shares results for the 2-socket server after I repeated all tests with the performance CPU frequency governor.

tl;dr

• MyRocks uses ~1.4X more CPU than InnoDB for this benchmark which means that MyRocks gets ~70% of the QPS compared to InnoDB for CPU-bound benchmarks. Note that compression is enabled for MyRocks but not for InnoDB. That will increase the CPU for MyRocks on the microbenchmarks that do writes. Here I ignore the benefits from compression, but they are a big deal in production.
• the largest regressions from early MyRocks 5.6.35 to modern MyRocks 8.0.32, occur on writes and range queries and a typical regression is ~5% 
• MySQL with InnoDB is faster in 8.0.37 (on average) than 5.6.35 for writes and some queries, but also up to 28% slower on some point and range queries

Builds

I tested the following builds for FB MyRocks:

• 5635-210407 - FB MyRocks 5.6.35 at git sha f896415f (as of 21/04/07) with RocksDB 6.19.0
• 5635-231016 - FB MyRocks 5.6.35 at git sha 4f3a57a1 (as of 23/10/16) with RocksDB 8.7.0
• 8032-231204 - FB MyRocks 8.0.32 at git sha e3a854e8 (as of 23/12/04) with RocksDB 8.7.0
• 8032-240529 - FB MyRocks 8.0.32 at git sha 49b37dfe (as of 24/05/29) with RocksDB 9.2.1
• 8032-240529-LTO - same as 8032-240529 except adds link-time optimization

I also compiled upstream MySQL 5.6.35, 5.6.51, 5.7.10, 5.7.44, 8.0.11 and 8.0.37 from source. For 8.0.37 I also created a binary for 8.0.37 with link-time optimization (LTO) enabled via -DWITH_LTO=ON.

Hardware

I tested on one server that I call Socket2. It is a SuperMicro SuperWorkstation (see here) with 2-sockets, 12 cores/socket, 64G RAM and ext4 (SW RAID 0 over 2 NVMe devices). It uses Ubuntu 22.04 with ext4 and Intel HT is disabled. As described above, the server uses the performance CPU frequency governor.

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 16 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 was:

# Socket2 -> 16 clients

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

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.

Results: MyRocks 5.6.35 vs 8.0.32

The numbers in the tables are the relative QPS as explained above.

The base case is 5635-210407, FB MyRocks 5.6.35 as of 21/04/07. The tables below compare it with:

• 5635-231016 - FB MyRocks 5.6.35 as of 23/10/16
• 8032-231204 - FB MyRocks 8.0.32 as of 23/12/04
• 8032-240529 - FB MyRocks 8.0.32 as of 24/05/29
• 8032-240529-LTO - FB MyRocks 8.0.32 as of 24/05/29 with link-time optimization

This shows the relative QPS as: (QPS for 5635-231016) / (QPS for 5635-210407)

• the max (1.24) occurs on points-covered-si.pre_range=100
• the min (0.90) occurs on range-notcovered-pk.pre_range=100 

5635-231016	min	max	avg	median
point-1	0.93	1.24	1.02	0.98
point-2	0.94	0.98	0.96	0.95
range-1	0.90	0.98	0.94	0.94
range-2	0.92	0.99	0.95	0.96
writes	0.94	0.99	0.96	0.95

This shows the relative QPS as: (QPS for 8032-231204) / (QPS for 5635-210407)

• the max (1.28) occurs on points-covered-si.pre_range=100
• the min (0.74) occurs on  scan_range=100

8032-231204	min	max	avg	median
point-1	0.86	1.28	0.98	0.92
point-2	0.91	1.08	0.98	0.94
range-1	0.74	1.02	0.95	0.96
range-2	0.97	0.99	0.98	0.98
writes	0.81	0.98	0.92	0.93

This shows the relative QPS as: (QPS for 8032-240529) / (QPS for 5635-210407)

• the max (1.24) occurs on points-covered-si.pre_range=100
• the min (0.67) occurs on scan_range=100
• the min for writes (0.79) occurs on insert_range=100
• from vmstat metrics the CPU overhead (cpu/o) grows and the context switch rate (cs/o) drops from 5635-210407 to 8032-240529

8032-240529	min	max	avg	median
point-1	0.86	1.24	0.98	0.93
point-2	0.89	1.08	0.97	0.94
range-1	0.67	1.01	0.93	0.94
range-2	0.96	0.99	0.97	0.97
writes	0.79	0.96	0.90	0.91

This shows the relative QPS as: (QPS for 8032-240529-LTO) / (QPS for 5635-210407)

• the max (1.26) occurs on points-covered-si.pre_range=100
• the min (0.67) occurs on scan_range=100
• the min for writes (0.80) occurs on insert_range=100
• from vmstat metrics the CPU overhead (cpu/o) grows and the context switch rate (cs/o) drops from 5635-210407 to 8032-240529-LTO

8032-240529-LTO	min	max	avg	median
point-1	0.79	1.26	0.99	0.93
point-2	0.91	1.10	0.98	0.94
range-1	0.67	1.02	0.94	0.95
range-2	0.97	1.01	0.99	1.00
writes	0.80	0.98	0.93	0.93

Results: InnoDB 5.6, 5.7 and 8.0

The numbers in the tables are the relative QPS as explained above.

The tables below show the relative QPS. The base case is InnoDB from MySQL 5.6.35. It is compared with InnoDB from 5.6.51, 5.7.10, 5.7.44, 8.0.11, 8.0.37 and 8.0.37 with link-time optimization.

This shows the relative QPS as: (QPS for InnoDB+MySQL 5.7.44) / (QPS for InnoDB+MySQL 5.6.35)

• the max (3.16) occurs on update-index_range=100
• the min (0.77) occurs on scan_range=100

5.7.44	min	max	avg	median
point-1	0.87	1.57	1.11	0.99
point-2	0.98	1.38	1.19	1.25
range-1	0.77	0.87	0.85	0.86
range-2	1.02	1.26	1.13	1.12
writes	1.07	3.16	1.43	1.25

This shows the relative QPS as: (QPS for InnoDB+MySQL 8.0.37) / (QPS for InnoDB+MySQL 5.6.35)

• the max (3.16) occurs on update-index_range=100 which is much less than the result in 5.7.44
• the min (0.65) occurs on scan_range=100
• the median values here are much less than the values above for InnoDB 5.7.44 because there are significant performance regressions in 8.0

8.0.37	min	max	avg	median
point-1	0.74	1.26	0.91	0.84
point-2	0.78	1.12	0.96	0.99
range-1	0.65	0.75	0.72	0.72
range-2	0.88	1.08	0.97	0.96
writes	0.96	2.05	1.22	1.12

This shows the relative QPS as : (QPS for InnoDB 8.0.37+LTO) / (QPS for InnoDB 5.6.35)

• the max(2.18) occurs on update-index_range=100 which is much less than the result in 5.7.44
• the min (0.64) occurs on scan_range=100
• LTO improves QPS by ~4%

8.0.37-LTO	min	max	avg	median
point-1	0.77	1.37	0.95	0.88
point-2	0.82	1.15	0.99	1.02
range-1	0.64	0.79	0.75	0.75
range-2	0.91	1.11	1.00	1.00
writes	0.99	2.18	1.27	1.17

Results: MyRocks vs InnoDB

The numbers in the tables are the relative QPS as explained above.

The base case is InnoDB from MySQL 8.0.37. The tables below show the relative QPS for MyRocks:

• 5635-231016 - FB MyRocks 5.6.35 as of 23/10/16
• 8032-231204 - FB MyRocks 8.0.32 as of 24/05/29

This shows the relative QPS as (QPS for FB MyRocks 5635-231016) / (QPS for InnoDB 8.0.37)

• the max (1.06) occurs on update-index_range=100
• the min (~0.50) occurs on many tests
• the median is ~0.70 which means that MyRocks uses ~1.4X more CPU than InnoDB

5635-231016	min	max	avg	median
point-1	0.52	0.94	0.74	0.78
point-2	0.61	0.87	0.72	0.68
range-1	0.56	0.77	0.65	0.65
range-2	0.69	0.77	0.73	0.73
writes	0.55	1.06	0.71	0.64

This shows the relative QPS as: (QPS for FB MyRocks 8032-240529) / (QPS for InnoDB 8.0.37)

• the max (1.02) occurs on update-index_range=100
• the min (~0.50) occurs on many tests
• the median is ~0.70 which means that MyRocks uses ~1.4X more CPU than InnoDB
• the worst cases here are ~0.50 when using the performance CPU frequency governor. It was ~0.25 when using schedutil.

8032-240529	min	max	avg	median
point-1	0.51	0.84	0.71	0.78
point-2	0.61	0.85	0.73	0.74
range-1	0.50	0.77	0.64	0.63
range-2	0.70	0.80	0.75	0.75
writes	0.51	1.02	0.67	0.61