Linkbench: IO-bound, small server, MyRocks and InnoDB

This post explains MySQL performance for Linkbench on a small server. This used a low-concurrency IO-bound workload to measure response time, IO and CPU efficiency. Tests were run for MyRocks and InnoDB on an Intel NUC. The previous post used an in-memory workload.
'
tl;dr - for IO-bound linkbench

• 99th percentile response times are more than 2X better with MyRocks than InnoDB
• Throughput results are mixed -- InnoDB 5.6 loads faster, MyRocks does transactions faster. The relative load rate for MyRocks is ~0.84 the InnoDB rate. The relative transaction rate for MyRocks is ~1.14X the InnoDB rate.
• CPU efficiency is mixed. MyRocks uses more CPU than InnoDB 5.6 on the load and less on transactions.
• MyRocks is more write efficient. Modern InnoDB writes ~1.6X more to storage per insert and ~25X more per transaction compared to MyRocks.
• MyRocks is more space efficient. Uncompressed InnoDB uses ~1.5X more space than uncompressed MyRocks and ~3X more space than compressed MyRocks.
• There is a regression from MySQL 5.6.35 to 8.0.2. InnoDB 8.0.2 gets 0.75X the load rate and 0.90X the transaction rate compared to InnoDB 5.6.35. New CPU overhead is the problem as InnoDB 8.0.2 uses 1.27X more CPU for the load and 1.15X more CPU for transactions compared to InnoDB 5.6.35.

Configuration

I used my Linkbench repo and helper scripts to run linkbench with maxid1=80M, loaders=1 and requestors=1 so there will be 2 concurrent connections doing the load and 1 connection running transactions after the load finishes. My linkbench repo has a recent commit that changes the Linkbench workload and this test included that commit. The test pattern is 1) load and 2) transactions. The transactions were run in 12 1-hour loops and I share results from the last hour. The test server is the i5 NUC described here were 4 HW threads, SSD and 16gb of RAM. The database is larger than RAM after the load.

Tests were run for MyRocks from FB MySQL 5.6.35 and InnoDB from upstream MySQL. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.

• MyRocks was compiled on October 16 with git hash 1d0132. Tests were repeated without and with compression. The configuration without compression is called MySQL.none in the rest of this post. The configuration with compression is called MySQL.zstd and used zstandard for the max level, no compression for L0/L1/L2 and lz4 for the other levels.
• Upstream 5.6.35, 5.7.17 and 8.0.2 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases. I also set innodb_purge_threads=1 to reduce mutex contention.

The performance schema was enabled for upstream InnoDB. It was disabled at compile time for MyRocks because FB MySQL still has user & table statistics for monitoring.

Graphs

All of the data is here. I adjusted iostat metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact. The first two graphs show the load and transaction rates relative to the rate for InnoDB from MySQL 5.6.35.

• p99 response times are more than 2X better for MyRocks than InnoDB 5.6
• MyRocks gets ~0.84X the load rate and ~1.14X the transaction rate compared to InnoDB 5.6
• InnoDB 8.0 gets 0.75X the load rate and 0.90X the transaction rate compared to InnoDB 5.6

CPU efficiency is mixed. These graphs have CPU overhead per insert during the load and per transaction. This is measured by the us and sy columns from vmstat. The values on the graph are relative to InnoDB from MySQL 5.6.35.

MyRocks uses more CPU than InnoDB 5.6 on the load and and less for transactions. Some of the CPU overhead for the load is from compaction and that is decoupled from the user connections doing inserts.

There is a CPU regression from MySQL 5.6.35 to 8.0.2 for InnoDB. InnoDB 8.0.2 uses 1.27X more CPU than InnoDB 5.6.35 for the load and 1.15X more for transactions. I assume this is from new code.

MyRocks is more write efficient. These graphs have the KB written to storage per insert during the load and per transaction. The values on the graphs are relative to InnoDB from MySQL 5.6.35. Modern InnoDB writes ~1.6X more to storage per insert and ~25X more per transaction compared to MyRocks. An SSD will last longer with MyRocks.

MyRocks is more space efficient. Uncompressed InnoDB uses more space than MyRocks after both the load and transaction tests. It uses ~1.5X more than uncompressed MyRocks and ~3X more than compressed MyRocks. One problem for InnoDB is B-Tree fragmentation. Leveled compaction in MyRocks wastes less space. The graph has the database size in GB (not using relative values here).