MyRocks, InnoDB and in-memory insert performance

This is yet another result to understand an insert-only workload. In this case the database is small enough to be cached by the storage engine. Previous results were explained here, here and here. From several tests it is apparent that MyRocks and InnoDB from MySQL 5.6 have similar throughput while InnoDB from MySQL 5.7 does much better for cached & insert-only when the server has fast SSD.

In this result I changed a few options to reduce the chance that compaction stalls will occur with MyRocks. I increased level0_slowdown_writes_trigger to 20 and level0_stop_writes_trigger to 30. I also ran tests with universal compaction. We probably have some work to do here -- improving default option values and making compaction throttling less spiky.

tl;dr

• MyRocks was faster than InnoDB in MySQL 5.6 and 5.7 at 1 and 2 concurrent clients
• MyRocks and InnoDB/MySQL-5.6 have similar throughput at 4+ concurrent clients
• InnoDB/MySQL-5.7 is fastest at 8+ concurrent clients
• Disabling the binlog helps MyRocks more than InnoDB. We have work in progress to make binlog processing faster for MyRocks.
• MyRocks with leveled compaction and the binlog is limited by compaction stalls in moving data from level 0 to level 2.

Note that MyRocks did great when able to avoid the bottlenecks from binlog processing and compaction stalls. That is apparent in the result for universal compaction with the binlog disabled. 

Test

For this test I inserted 400M rows using the insert benchmark. The test was run for 1, 2, 4, 8, 12 and 16 concurrent clients. There was a table per client and each table has 3 secondary indexes. The inserts are in PK order. The database cache was large enough to cache the database.

Tests were repeated with the binlog enabled and disabled. For MyRocks I used a build from November 21 to match what was used in previous tests. For MyRocks I also ran tests with leveled and universal compaction. This is the first time I tried universal compaction with MyRocks.

Results

The numbers in the table below are the average insert rate, larger is better.

1       2       4       8       12      16      concurrency
34465   61843   106185  131536  131926  121988  innodb-5.6.26, binlog
35217   63857   108666  133200  133023  125196  innodb-5.6.26, no binlog
31461   60855   114058  201918  258398  281690  innodb-5.7.10, binlog
32962   62490   117028  206292  263678  282486  innodb-5.7.10, no binlog

leveled compaction
38465   71467   113186  125392  125865  125984  myrocks, binlog
44092   81950   132275  142298  139519  134998  myrocks, no binlog

universal compaction
37474   71162   115042  133556  135181  134003  myrocks, binlog
43006   79888   134590  198511  238379  252207  myrocks, no binlog

Graphs

These graphs show the row insert rate per 5 second interval for the test with 16 clients. The graph for InnoDB-5.7 ends earliest because it finishes the load first. The graph for MyRocks with leveled compaction has the most variance when ingest is faster than compaction from level 0 to level 2. Ignoring the variance, MyRocks throughput declines less over time than InnoDB.

The next graph is for InnoDB-5.7. While throughput declines over time there is not much variance.

The next graph is for InnoDB-5.6. It has more variance and loses more throughput over time compared to InnoDB-5.7. MySQL has been busy making InnoDB better.

The next graphs are for MyRocks with leveled and then universal compaction. There are more stalls with leveled compaction. The average throughput is similar and binlog processing is the bottleneck. We have work in progress to improve leveled compaction and the overhead from the binlog.