Linkbench: in-memory, small server, MyRocks and InnoDB

This post explains MySQL performance for Linkbench on a small server. This used a low-concurrency and in-memory workload to measure response time, IO and CPU efficiency. Tests were run for MyRocks and InnoDB on an Intel NUC. I wrote a similar report a few months ago. The difference here is that I used an updated compiler toolchain, include results for MyRocks and use an updated version of Linkbench.
'
tl;dr - for in-memory linkbench

• InnoDB from MySQL 5.6.35 has ~20% better insert and load rates than MyRocks.
• MyRocks is less CPU efficient. MyRocks uses 28% more CPU than InnoDB 5.6.35 for the load and 14% more for transactions.
• MyRocks is more write efficient. Modern InnoDB writes ~3X more to storage per insert and ~25X more per transaction.
• MyRocks is more space efficient. Uncompressed InnoDB uses ~1.5X more space than uncompressed MyRocks after 12 hours of transactions.
• There is a CPU regression from MySQL 5.6.35 to 8.0.2. The CPU overhead is 20% larger for 8.0.2 on the load and 8% larger on transactions. The insert and load rates are 20% smaller for 8.0.2 than for 5.6.35. 

Configuration

I used my Linkbench repo and helper scripts to run linkbench with maxid1=10M, 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.

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 but the database cache was made large enough to cache the ~10gb database.

• MyRocks was compiled on October 16 with git hash 1d0132. Compression was not used.
• 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 iostat currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact. The data also includes p99 response time metrics for the most frequent transaction types in Linkbench and that was similar for MyRocks and modern InnoDB.

The first two graphs show the load and transaction rates relative to InnoDB from MySQL 5.6.35. InnoDB from 5.6.35 does better than MyRocks on both load and transaction rates. Load and transaction rates drop by ~20% from MySQL 5.6.35 to 8.0.2.

Next is the 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. The CPU overheads explain the load and insert rates above. MyRocks uses ~28% more CPU than InnoDB 5.6.35 for the load and ~14% more for transactions. But there is also a CPU regression for InnoDB from MySQL 5.6 to 8.0 because InnoDB with 8.0.2 uses ~20% more CPU on the load and 8% more for transactions.

Next is 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 ~3X more to storage per insert and ~25X more per transaction. An SSD will last longer with MyRocks.

Uncompressed MyRocks uses more space than uncompressed InnoDB after the load. But modern InnoDB uses ~1.5X more space than MyRocks after 12 hours of transactions. One problem for InnoDB is B-Tree fragmentation. Leveled compaction in MyRocks wastes less space.