Small Datum: Yet another Insert Benchmark result: MyRocks, MySQL and a small server

While trying to explain a Postgres performance problem I repeated the Insert Benchmark on a small server for MyRocks from MySQL 5.6 and 8.0. This post explains those results. The previous report for a cached workload is here.

tl;dr

Disclaimers

The low-concurrency results here are worse than the results from a bigger server with more concurrency because the result here depends more on CPU overheads and MySQL keeps on growing code paths, while on the bigger server the cost from new CPU overheads is offset by other improvements.
Some of the regressions here are similar to what I measure for InnoDB and the problem is likely code above the storage engine layer.
For MyRocks 8.0.28 compared to 5.6.35

Results for most benchmark steps aren't surprising and MyRocks 8.0.28 gets between 80% and 95% of the throughput compared to MyRocks 5.6.35
Results for the qr1000.L6 benchmark step with the IO-bound workload are odd. MyRocks 8.0.28 gets only 39% of the throughput compared to MyRocks 5.6.35. From the metrics I see that MyRocks 8.0.28 does ~2X more read IO/query (see rpq) and uses ~2X more CPU/query (see cpupq). I have yet to explain this.

Build + Configuration

This report has results for MyRocks 5.6.35 and 8.0.28. The cza1_bee config was used and they are here.

The builds tested are:

fbmy5635_rel_202203072101.cza1_bee

MyRocks 5.6.35 from code as of 2022-03-07 at git hash e7d976ee with RocksDB 6.28.2, cza1_bee config

fbmy5635_rel_20230529_850.cza1_bee

MyRocks 5.6.35 from code as of 2023-05-29 at git hash b739eac1 with RocksDB 8.5.0, cza1_bee config

fbmy8028_rel_20220829_752.cza1_bee

MyRocks 8.0.28 from code as of 2022-08-29 at git hash a35c8dfeab, RocksDB 7.5.2, cza1_bee config

fbmy8028_rel_20230619_831.cza1_bee

MyRocks 8.0.28 from code as of 2023-06-19 at git hash 6164cf0274, RocksDB 8.3.1, cza1_bee config

fbmy8028_rel_221222.cza1_bee

MyRocks 8.0.28 from code as of 2023-12-22 at git hash 2ad105fc, RocksDB 8.7.0 at git hash 29005f0b, cza1_bee config

fbmy8028_rel_231222_870.cza1_bee_cfx

MyRocks 8.0.28 from code as of 2023-12-22 at git hash 2ad105fc, RocksDB 8.7.0 at git hash 29005f0b, cza1_bee config, indexes use a separate column family

The Benchmark

The benchmark is run with 1 client. It is explained here and was run in two setups

cached - database has 30M rows and fits in memory
IO-bound - database has 800M rows and is larger than memory,

The test server was named SER4 in the previous report. It has 8 cores, 16G RAM, Ubuntu 22.04 and XFS using 1 m.2 device.

The benchmark steps are:

l.i0

insert X million rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client. X is 30M for cached and 800M for IO-bound.

create 3 secondary indexes per table. There is one connection per client.

l.i1

use 2 connections/client. One inserts Y rows and the other does deletes at the same rate as the inserts. Each transaction modifies 50 rows (big transactions). This step is run for a fixed number of inserts, so the run time varies depending on the insert rate. Y is 80M for cached and 4M for IO-bound.

l.i2

like l.i1 but each transaction modifies 5 rows (small transactions) and Y is 20M for cached and 1M for IO-bound.
Wait for X seconds after the step finishes to reduce variance during the read-write benchmark steps that follow.

qr100

use 3 connections/client. One does range queries for Z seconds and performance is reported for this. The second does does 100 inserts/s and the third does 100 deletes/s. The second and third are less busy than the first. The range queries use covering secondary indexes. This step is run for a fixed amount of time. If the target insert rate is not sustained then that is considered to be an SLA failure. If the target insert rate is sustained then the step does the same number of inserts for all systems tested. Z is 3600 for cached and 1800 for IO-bound.

qp100

like qr100 except uses point queries on the PK index

qr500

like qr100 but the insert and delete rates are increased from 100/s to 500/s

qp500

like qp100 but the insert and delete rates are increased from 100/s to 500/s

qr1000

like qr100 but the insert and delete rates are increased from 100/s to 1000/s

qp1000

like qp100 but the insert and delete rates are increased from 100/s to 1000/s

Results

The performance reports are here for cached and for IO-bound.

The summary has 3 tables. The first shows absolute throughput by DBMS tested X benchmark step. The second has throughput relative to the version on the first row of the table. The third shows the background insert rate for benchmark steps with background inserts and all systems sustained the target rates. The second table makes it easy to see how performance changes over time.

Below I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is my version and $base is the version of the base case. When relative QPS is > 1.0 then performance improved over time. When it is < 1.0 then there are regressions. The Q in relative QPS measures:

insert/s for l.i0, l.i1, l.i2
indexed rows/s for l.x
range queries/s for qr100, qr500, qr1000
point queries/s for qp100, qp500, qp1000

Below I use colors to highlight the relative QPS values with red for <= 0.95, green for >= 1.05 and grey for values between 0.95 and 1.05.

From the summary for cached:

the base case is fbmy5635_rel_202203072101 (MyRocks 5.6.35 from 2022)
comparing fbmy8028_rel_231222_870 (latest MyRocks 8.0.28) with the base case

l.i0

relative QPS is 0.72 in fbmy8028_rel_231222_870

l.x - I ignore this for now
l.i1, l.i2

relative QPS is 0.85, 0.82 in fbmy8028_rel_231222_870

qr100, qr500, qr1000

relative QPS is 1.06, 1.24, 1.06 in fbmy8028_rel_231222_870

qp100, qp500, qp1000

relative QPS is 0.96, 0.95, 0.96 in fbmy8028_rel_231222_870

From the summary for IO-bound:

the base case is fbmy5635_rel_202203072101 (MyRocks 5.6.35 from 2022)
comparing fbmy8028_rel_231222_870 (latest MyRocks 8.0.28) with the base case

l.i0

relative QPS is 0.72 in fbmy8028_rel_231222_870

l.x - I ignore this for now
l.i1, l.i2

relative QPS is 0.86, 0.80 in fbmy8028_rel_231222_870

qr100, qr500, qr1000

relative QPS is 0.80, 0.80, 0.39 in fbmy8028_rel_231222_870
the 0.39 value is an outlier. From the metrics I see that MyRocks 8.0.28 does ~2X more read IO/query (see rpq) and uses ~2X more CPU/query (see cpupq). I have yet to explain this.