Checking MyRocks 5.6 for regressions with the Insert Benchmark and a medium server, part 2

This is a follow up to my previous post where I use the Insert Benchmark to search for performance regressions in MyRocks 5.6. I use 5.6 rather than 8.0 because the MyRocks 5.6 builds go back further in time.

tl;dr - a hand waving summary

• Modern MyRocks uses RocksDB 8.5.0 and classic MyRocks uses RocksDB 6.19
• Perf regressions in modern MyRocks are from new CPU overheads
• The load benchmark step (l.i0) is faster in modern MyRocks 
• The write heavy benchmark step (l.i1) is ~5% slower in modern MyRocks
• The read heavy benchmark steps (q100, q500, q100) are ~10% slower in modern MyRocks
• Modern MyRocks has ~13% less write-amplification

Builds

I started with the builds from my previous post, removed the fbmy5635_rel_jun23_7e40af677 build and then added 3 builds that use MyRocks as of what is used by fbmy5635_rel_202305292102 but upgraded RocksDB to 8.3.2, 8.4.3 and 8.5.0

I used MyRocks from FB MySQL 5.6.35 using the rel build (CMAKE_BUILD_TYPE=Release, see here) with source from 2021 through 2023. The versions are:

• fbmy5635_rel_202104072149 - from 20210407 at git hash (f896415fa0 MySQL, 0f8c041ea RocksDB), RocksDB 6.19
• fbmy5635_rel_202203072101 - from 20220307 at git hash (e7d976ee MySQL, df4d3cf6fd RocksDB), RocksDB 6.28.2
• fbmy5635_rel_202205192101 - from 20220519 at git hash (d503bd77 MySQL, f2f26b15 RocksDB), RocksDB 7.2.2
• fbmy5635_rel_202208092101 - from 20220809 at git hash (877a0e585 MySQL, 8e0f4952 RocksDB), RocksDB 7.3.1
• fbmy5635_rel_202210112144 - from 20221011 at git hash (c691c7160 MySQL, 8e0f4952 RocksDB), RocksDB 7.3.1
• fbmy5635_rel_202302162102 - from 20230216 at git hash (21a2b0aa MySQL, e5dcebf7 RocksDB), RocksDB 7.10.0
• fbmy5635_rel_202304122154 - from 20230412 at git hash (205c31dd MySQL, 3258b5c3 RocksDB), RocksDB 7.10.2
• fbmy5635_rel_202305292102 - from 20230529 at git hash (b739eac1 MySQL, 03057204 RocksDB), RocksDB 8.2.1
• fbmy5635_rel_20230529_832 - from 20230529 at git hash (b739eac1 MySQL) but with RocksDB at version 8.3.2
• fbmy5635_rel_20230529_843 - from 20230529 at git hash (b739eac1 MySQL) but with RocksDB at version 8.4.3
• fbmy5635_rel_20230529_850 - from 20230529 at git hash (b739eac1 MySQL) but with RocksDB at version 8.5.0

Benchmark

The insert benchmark was run in two setups.

• cached by RocksDB - all tables are cached by RocksDB
• IO-bound - the database is larger than memory

The benchmark used a c2-standard-30 server from GCP with Ubuntu 22.04, 15 cores, hyperthreads disabled, 120G of RAM and 1.5T of storage from RAID 0 over 4 local NVMe devices with XFS.

The benchmark is run with 8 clients and 8 tables (client per table). The benchmark is a sequence of steps.

• l.i0
• insert X million rows per table where X is 20 for cached and 500 for IO-bound
• l.x
• create 3 secondary indexes. I usually ignore performance from this step.
• l.i1
• insert and delete another 200 million rows per table with secondary index maintenance. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail. This step took ~20,000 seconds.
• q100, q500, q1000
• do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 3600 seconds.

Configurations

I used two config (my.cnf) files: base and c5. The c5 config adds rocksdb_max_subcompactions=4.

Results

Performance reports are here for Cached by RocksDB (for base and for c5 config) and for IO-bound (for base and for c5 config).

Results: average throughput

This section explains the average throughput tables in the Summary section. I use relative throughput to save on typing where relative throughput is (throughput for some version  / throughput for base case). When relative throughput is > 1 then some version is faster than the base case. Unless stated ...

• Base case is fbmy5635_rel_202104072149, the oldest build that uses RocksDB 6.19
• Some version is fbmy5635_rel_20230529_850, the newest build that uses RocksDB 8.5.0

For Cached by RocksDB with the base config (see here)

• Relative throughput for (l.i0, l.x, l.i1, q100, q500, q1000) is (0.96, 0.91, 0.95, 0.91, 0.90, 0.90)
• Modern MyRocks gets 5% to 10% less throughput vs an old build that used RocksDB 6.19. The regression arrives slowly -- a small decrease in each new release.
• From the HW perf metrics new CPU overhead probably explains the perf regressions
• For l.i1 the CPU/operation overhead grew from 90 to 103 or 14.4% (see cpupq)
• For q100 the CPU/operation overhead grew from 206 to 227 or 10.1% (see cpupq)

For Cached by RocksDB with the c5 config (see here)

• Relative throughput for (l.i0, l.x, l.i1, q100, q500, q1000) is (1.04, 1.00, 1.00, 0.95, 0.95, 0.94)
• Modern MyRocks does the same or better than old MyRocks for the write-intensive steps and gets ~5% less throughput for the read-intensive steps.

For IO-bound with the base config (see here)

• Relative throughput for (l.i0, l.x, l.i1, q100, q500, q1000) is (1.03, 0.92, 0.94, 0.91, 0.91, 0.90)
• Modern MyRocks gets ~10% less throughput on the read-intensive steps, 6% less on the most important write-intensive step (l.i1) but is 3% faster at the load step (l.i0).
• From the HW perf metrics new CPU overhead probably explains the perf regressions
• For l.i1 the CPU/operation overhead grew from 115 to 126 or 9.5% (see cpupq)
• For q100 the CPU/operation overhead grew from 249 to 282 or 13.2% (see cpupq)

For IO-bound with the c5 config (see here)

• Relative throughput for (l.i0, l.x, l.i1, q100, q500, q1000) is (1.08, 0.92, 0.94, 0.91, 0.91, 0.90)
• Modern MyRocks gets ~10% less throughput on the read-intensive steps, 6% less on the most important write-intensive step (l.i1) but is 8% faster at the load step (l.i0).
• Modern MyRocks has ~13% less write-amplification. See the wkbpi column (KB written to storage/insert) from the l.i1 step where it drops from 2.100 with RocksDB 6.19 to 1.825 with RocksDB 8.5.0.