Insert benchmark: IO-bound, high-concurrency, fast server, part 2

This is similar to the previous insert benchmark result for IO-bound and high-concurrency except it uses 1 table rather than 16 to determine how a storage engine behaves with more contention.

tl;dr

• Inserts are much faster for MyRocks
• The InnoDB PK uses 2X more space for the 1 table test than the 16 table test. I filed bug 88827.
• MyRocks secondary index scans have a similar performance to InnoDB
• MyRocks PK scans are ~2X slower than InnoDB 5.6 on the 16 table test but ~3X faster on the 1 table test. This might also be bug 88827.

Configuration

Start by reading my previous post. The test still uses 2B rows but there is only one table here when the previous test used 16 tables. The load still uses 16 concurrent clients. The read-write test still uses 16 read clients and 16 write clients. But the scan test uses 1 client here versus 16 clients on the previous test and the scan test takes longer to finish.

While I have results for InnoDB from FB MySQL I exclude them from the graphs to improve readability.

Results

All of the data for the 1-table tests is here and for the 16-table 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.

For most of the results below I compare rates for this test with rates for the 16-table test and skip the graphs that show HW efficiency metrics.

Size

This graph shows the database size when the load ends for the 16 table and 1 table tests. For MyRocks and TokuDB the database size is similar for both tests. The InnoDB result is odd because the size is almost 1.25X larger for the 1 table test. From SHOW TABLE STATUS the data_length was about 2X larger for the 1 table test. From iostat output the PK scan for the 1 table test reads ~205gb while the 16 table test reads ~125gb. So the PK uses almost 2X more space than it should when there are concurrent inserters to the same table. The inserts are multi-row and the PK is auto-inc so the inserts grow the b-tree to the right. I filed bug 88827 for this.

Load

This graph shows the insert rate for the 16 and 1 table tests. Some engines get more inserts/second with 1 table, others get more with 16 tables:

• More with 16 tables: ~1.1X more for MyRocks, ~2X more for TokuDB
• More with 1 table: 1.3X more for InnoDB 5.6, 1.4X more for InnoDB 5.7 and 8.0

Scan

These graphs show the scan times relative to the scan time for InnoDB 5.6.35. A value > 1 means the engine is slower than InnoDB. The first graph is from the 16 table test and the second is from the 1 table test. In both cases the MyRocks secondary index scan is about as fast as InnoDB. But the PK scan MyRocks is more than 2X slower than InnoDB in the 16 table test and 2X to 3X faster than InnoDB for the 1 table test.

What might explain the difference in PK scan times? MyRocks was ~2X slower than InnoDB in the 16 table test and ~3X faster than InnoDB in the 1 table test. That is a change of 6X. The output from vmstat and iostat can help for the 16 table and 1 table tests. This is in the q5 section which is the second scan of the PK and I will compare myrocks.jun16.none with inno5635.

• First, the InnoDB PK uses ~2X more space for the 1 table test, so there is 2X more data to scan. But with hand waving that should explain only 2X of the 6X change.
• On the 16 table test InnoDB 5.6 gets ~3X more MB/s of storage reads compared to MyRocks: 2353.7 vs ~828.1. But on the 1 table test InnoDB 5.6 gets less storage read MB/s than MyRocks: 67.4 vs 94.8.
• One reason for getting less read MB/s from storage is using more CPU and that appears true in this case. The Mcpu/o column has the CPU overhead per row read. For the 16 table test it is 1.278 for InnoDB 5.6 vs 1.100 for MyRocks. On the 1 table test it is 3.547 for InnoDB 5.6 vs 1.807 for MyRocks. So InnoDB is only using ~1.2X more CPU than MyRocks on the 16 table test but ~2X more CPU on the 1 table test.