Insert benchmark: in-memory, low-concurrency, fast server

This post explains the insert benchmark with an in-memory and low-concurrency workload for MyRocks, InnoDB and TokuDB. The goal is to understand throughput and efficiency for different storage engines and different versions of MySQL.

tl;dr - for an in-memory, low-concurrency workload

• MyRocks and InnoDB 5.6 have similar insert rates but InnoDB 5.6 has a better query rate.
• MyRocks index scans are slower than InnoDB 5.6: 1.23X slower for the PK and 2.15X slower for the secondary indexes. More CPU overhead is the cause.
• MyRocks is more space efficient. InnoDB uses ~1.6X more space than MyRocks.
• MyRocks is more write efficient. InnoDB writes ~3X more to storage per insert on the load and ~15X more on the read-write test.
• MyRocks uses more CPU than InnoDB 5.6: 16% more on the load, 37% more on read-write.
• InnoDB 8.0 is 10% to 20% slower than InnoDB 5.6 and uses ~20% more CPU than InnoDB 5.6.

Configuration

The insert benchmark is described here. The test server has 48 HW threads, fast SSD and 256gb of RAM. The database block cache (buffer pool) was large enough to cache the database. The test was run with 1 table and 1 query client. For the read-write tests there is a writer client that runs concurrent with the query client. The insert benchmark loaded the table with 100M rows, then did a full scan of each index (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Tests were run for MyRocks, InnoDB from upstream MySQL, InnoDB from FB MySQL and TokuDB. 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. Compression was not used. 
• Upstream 5.6.35, 5.7.17, 8.0.1, 8.0.2 and 8.0.3 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases.
• InnoDB from FB MySQL 5.6.35 was compiled on June 16 with git hash 52e058.
• TokuDB was from Percona Server 5.7.17. Compression was not used.

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

Results

All of the data for the tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

Load

The graphs below have rates for each configuration relative to the rate for InnoDB 5.6.35. The graphs show the insert rate, the CPU overhead per insert and the storage write rate per insert.

• MyRocks has the best insert rate. It gets 1.06X more than InnoDB 5.6.35.
• MyRocks has better write efficiency. InnoDB writes ~3X more to storage per insert.
• MyRocks uses 1.16X more CPU/insert than InnoDB 5.6.35. I assume the extra CPU use is from background tasks (compaction).
• There is a regression from 5.6 to 8.0 for InnoDB as InnoDB 8.0.3 gets 0.78X the insert rate and uses 1.21X the CPU compared to InnoDB 5.6.35. I assume this is from new code above the storage engine.

Size

This is the database size at the end of the load. The values are absolute and rounded to the nearest GB. MyRocks is more space efficient than InnoDB. Uncompressed InnoDB uses 1.6X more space than uncompressed MyRocks.

Scan

This graph shows the number of seconds to scan the PK index and all of the secondary indexes. The number for the PK scan is from the second scan of it.

• MyRocks is 1.23X slower on the PK scan and 2.15X slower on the secondary scans compared to InnoDB 5.6.35. MyRocks uses more CPU for these scans based on vmstat data for q5 (the second PK scan) and q4 (one of the secondary scans) - the Mcpu/o column is the CPU overhead per row fetched.
• Scans are faster for InnoDB starting in 5.7. InnoDB 5.6 does the PK scan 1.4X slower and the secondary scans 1.2X slower.
• I don't know why the secondary scan for InnoDB from FB MySQL is slower than from upstream. There was a perf bug we added, and recently fixed, for concurrent secondary scans.

Read-write, 1000 inserts/second

This section has results for the read-write test when the writer is limited to 1000 inserts/second. The first graph has the QPS for short range queries. The second graph has the KB written to storage per insert. Both use values relative to the value for InnoDB 5.6.35.

• InnoDB 5.6.35 has the best QPS.
• MyRocks gets 0.79X the QPS compared to InnoDB 5.6.35. The problem is CPU overhead which will be obvious in the next section (read-write, 100 writes/second).
• MyRocks is more write efficient. InnoDB 5.7 and 8.0 write ~15X more to storage per insert than MyRocks. The rate for InnoDB 5.6.35 is an outlier and I think the cause is furious flushing.
• InnoDB 8.0.3 gets 0.87X the QPS compared to InnoDB 5.6.35. The regression here is smaller than the regression for the load.

Read-write, 100 inserts/second

This section has results for the read-write test when the writer is limited to 100 inserts/second. The first graph has the QPS for short range queries. The second graph has the CPU overhead per query. Both use values relative to the value for InnoDB 5.6.35. Results are similar to the previous section.

• InnoDB 5.6.35 has the best QPS.
• MyRocks gets 0.75X the QPS compared to InnoDB 5.6.35. MyRocks uses 1.37X more CPU/query.
• InnoDB 8.0.3 gets 0.85X the QPS compared to InnoDB 5.6.35. It uses 1.19X more CPU/query than InnoDB 5.6.35. I assume the problem is new code above the storage engine.