Wednesday, February 8, 2017

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.


Thursday, February 2, 2017

Why MyRocks?

This is yet another attempt to explain why MyRocks is interesting. MySQL continues to get more popular and should soon become #1 on the db-engines ranking. Popularity isn't the only thing about MySQL that is improving -- performance, manageability and availability are also much better and cloud vendors, especially Amazon, are doing remarkable work to make MySQL in the cloud much easier than it used to be.
While Oracle has been a great owner of MySQL we still benefit from external contributions to the core DBMS. But new storage engines are unlikely, even ones that had great promise like TokuDB and PBXT, because the storage engine API is hard to implement. I am surprised and thrilled by the progress we are making with MyRocks. It is in production for us today and I expect it to be in production elsewhere within the next 12 months. I am grateful to work with talented teams (MyRocks, MySQL & RocksDB) and understanding management.

Efficient performance is the reason for MyRocks. We want to provide performance similar to InnoDB but with much better storage efficiency. The technical message is that MyRocks has less space and write amplification than InnoDB without sacrificing too much read amplification. For a workload I care about it uses 1/2 the space and writes at 1/10 the rate of InnoDB. The less technical message is that with MyRocks a deployment needs less SSD and the SSD will last longer.

The other important question is when to use MyRocks. My goal is for MyRocks to be an alternative to InnoDB for any workload where the database is larger than RAM. While we have optimizations for in-memory workloads with RocksDB I don’t focus on that today. I also assume that too many workloads use too much RAM today. If you have fast storage with SSD and the database fits in RAM then you probably have too much RAM. So my pitch is to use more fast storage and less RAM and use less space, less power and possibly fewer servers.