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

This post explains the insert benchmark with an in-memory and high-concurrency workload for MyRocks, InnoDB and TokuDB. The goal is to understand throughput and efficiency for different storage engines and different versions of MySQL. The previous test used a low-concurrency, in-memory workload with 1 query client and 1 table. This test uses 16 concurrent clients and 16 tables.

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

• InnoDB 5.7 has the best insert rate. The rate for MyRocks is better than InnoDB 5.6 but we have work to do to close the gap with modern InnoDB.
• InnoDB 5.6 has the best query rates. MyRocks gets ~0.80X the QPS compared to it.
• MyRocks index scans are slower than InnoDB 5.6: 1.54X slower for the PK and 2.35X 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 ~10X more on the read-write test.
• MyRocks uses 1.24X more CPU than InnoDB 5.6 on queries but less CPU on loads
• InnoDB 8.0 is 2.4X faster than InnoDB 5.6 on loads but gets 0.89X the QPS of InnoDB 5.6 on queries because it used 1.11X more CPU.

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 16 tables and 16 query clients. For the read-write tests there is an additional writer client for each query client. The insert benchmark loaded the tables with 500M rows (~32M rows/table), 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 per writer 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 per writer client. The scan and read-write tests use a client per table. With 16 tables there are 16 concurrent clients.

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 load is concurrent and there are 16 clients each loading a separate table . 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.

• InnoDB 5.7 has the best insert rate and is 2.47X better than InnoDB 5.6. MyRocks is 1.51X better than InnoDB 5.6.
• MyRocks has the best CPU efficiency. 
• MyRocks has better write efficiency. InnoDB writes ~3X more to storage per insert.
• InnoDB from FB MySQL does much better than upstream 5.6. I assume several changes account for the improvement.

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. The x-axis truncates the result for FbInno5635 on the secondary index scan. That result was lousy due to a bug that has been fixed. The scans are concurrent and there are 16 clients each scanning indexes from separate tables.

• MyRocks is 1.54X slower on the PK scan and 2.35X 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

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.77X 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 writes ~10X more to storage per insert.
• InnoDB 8.0.3 gets 0.90X the QPS compared to InnoDB 5.6.35. New CPU overhead is the cause.

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.78X the QPS compared to InnoDB 5.6.35. MyRocks uses 1.24X more CPU/query.
• InnoDB 8.0.3 gets 0.89X the QPS compared to InnoDB 5.6.35. It uses 1.11X more CPU/query than InnoDB 5.6.35. I assume the problem is new code above the storage engine.