tl;dr - the average insert rate is much better for MyRocks than for InnoDB
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
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
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.