MyRocks vs InnoDB, the insert benchmark and a disk array

This compares MyRocks and InnoDB using the insert benchmark. The test server has a disk array. The workload generates a lot of secondary index maintenance which can stress the server's random IO capacity.

tl;dr - the average insert rate is much better for MyRocks than for InnoDB

Configuration

The test server has 2 sockets, 8 cores (16 HW threads) per socket, 64GB of RAM and 14 disks with SW RAID 0 and a 2MB RAID stripe.

I tested MyRocks versus InnoDB from MySQL 5.6.26 and 5.7.10 from Oracle. Two configurations were tested for MyRocks. The first is the regular configuration described here. The second is the configuration optimized for load performance and called load-optimized. The load-optimized configuration sets rocksdb_bulk_load=1 to disable unique index checks and uses a smaller memtable to reduce the number of comparisons per insert.

The command line to run the insert benchmark for MyRocks is here. These are links to the my.cnf files for default MyRocks, load-optimized MyRocks, MySQL 5.6 and MySQL 5.7. It is possible that I misconfigured background IO rates for InnoDB. MyRocks is much easier to configure. I have results for 6 configurations:

• myrocks.def is MyRocks with the default configuration
• myrocks.opt is MyRocks with the load-optimized configuration
• mysql56.zlib is MySQL 5.6.26, InnoDB with zlib compression
• mysql57.zlib is MySQL 5.7.10, InnoDB with zlib compression
• mysql56.none is MySQL 5.6.26, InnoDB without compression
• mysql57.none is MySQL 5.7.10, InnoDB without compression

Results

This is a summary of the load performance for people like me who prefer tables. The legend for the table below is:

• #rows - number of rows inserted. The target is 1B but the test was stopped early for InnoDB because I wasn't willing to wait one week.
• #secs - number of seconds for which the test ran.
• avg_ips - average rate for rows inserted per second during the test. Note that the rate declines significantly over time for InnoDB.
• last_ips - rows inserted per second for the last 100,000 inserts.

My summary of the table below is:

• I didn't wait for the InnoDB tests to finish as that might have taken more 1 week.
• There is a big difference between the insert rates for MyRocks and InnoDB.
• There is a big difference between the insert rates for the default and load-optimized configurations of MyRocks. This difference is much smaller as the number of insert threads is increased.  The load-optimized configuration is 2.87X faster at 1 thread, 1.48X faster at 4 threads and 1.15X faster at 8 threads.
• The insert rates for MyRocks increase with the number of insert threads. The results here are from a test with 1 thread. When I repeated a test with 8 threads the insert rate was ~50,000/second for MyRocks while the rates for InnoDB did not improve from those below.

#rows           #secs  avg_ips last_ips        engine

1000000000       96775  10333   10276          myrocks.def

1000000000       25009  39986   30339          myrocks.opt

 788300000      169511   4650    2834          mysql56.zlib

 470200000      164890   2852    1552          mysql57.zlib

 553700000      164989   3356    1925          mysql56.none

 524500000      168829   3107    1690          mysql57.none

Graphs

This is the insert rate over time for load-optimized MyRocks. There are a few stalls which I have yet to investigate. Otherwise the throughput is almost steady over time.

This is the insert rate for the default configuration of MyRocks. This has fewer stalls than the load-optimized configuration which is reasonable since there is less stress on compaction. Again the insert rate is almost steady over time.

This is the insert rate for all of the InnoDB configurations. The insert rate degrades significantly from the start of the test. I don't understand why MySQL56.zlib has the best throughput. InnoDB page flushing tuning is a complex art.

This is the insert rate over time for the InnoDB configurations using log scale for the y-axis. That makes it easier to see the difference as the rate declines.