Sysbench, in-memory, small server: InnoDB & MyRocks

In this post I compare MyRocks and InnoDB using in-memory sysbench and a small server. Previous posts explained performance for MySQL from 5.0 to 8.0 for MyISAM and InnoDB. In this post I limit the results to MySQL 5.6.35 for MyRocks and both 5.6.35 and 5.7.17 for InnoDB.

tl;dr - the QPS for MyRocks relative to InnoDB in MySQL 5.6 where 1.0 means they are equal

• For write heavy tests MyRocks gets ~0.80 vs InnoDB except on the update-index test where it benefits from read-free secondary index maintenance
• For scan heavy tests MyRocks gets between 0.70 and 0.94 vs InnoDB
• For point query tests MyRocks gets between 0.71 and 0.80 vs InnoDB
• For in-list query tests MyRocks gets between 0.26 and 0.45 vs InnoDB

Not really tl;dr

• Range scans are much faster for InnoDB in 5.7 than in 5.6
• QPS for InnoDB in MySQL 5.7 is frequently worse than in 5.6 because there is more CPU overhead per query. I assume this is the cost from more features. Most of the overhead appears to be in code above the storage engine (optimizer, parser, etc). This test uses low concurrency and doesn't highlight great improvements at high concurrency in InnoDB 5.7. The random-points section below has more details.
• MyRocks read performance suffers more than InnoDB when the database is subject to a write-heavy workload prior to the read-heavy tests. It costs more CPU time to search the LSM structures after random changes. This is more true for in-memory workloads than for IO-bound. Note that many bulk-loads done by benchmarks don't show this cost because they insert data in key order. Benchmarks that do bulk-load and then query can be misleading, especially if the bulk-load is in key order.

From the ratios above you might ask why I like MyRocks. Some of the performance differences are things we need to fix and work has been in progress, so better results are coming. But I like it because it is more efficient than InnoDB for two reasons. First, it uses less space than InnoDB (less space-amplification) so you need less SSD to store the same data. Second, it writes less to storage per transaction so SSD devices last longer. InnoDB, WiredTiger and mmapv1 are all increasing the rate at which I must replace SSDs on my test servers. Finally, this workload is in-memory and MyRocks is usually better for IO-bound than for in-memory. 

Configuration

The tests used InnoDB from upstream MySQL 5.6.35 and 5.7.17 and then MyRocks from FB MySQL 5.6.35. The MyRocks build is from August 15 with git hash 0d76ae. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. My use of sysbench is described here. The my.cnf files are here for the i3 NUC and i5 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, used the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention. For all tests the binlog was enabled but fsync was disabled for the binlog and database redo log. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream. Compression was not used for InnoDB or MyRocks.

Sysbench is run with 2 tables and 2M rows per table. Each test is repeated for 1 and 2 clients. Each test runs for 600 seconds except for the insert-only test which runs for 300 seconds. The database fits in RAM.

I repeat tests on an i5 NUC and i3 NUC. The i5 NUC has more RAM, a faster SSD and faster CPU than the i3 NUC, but I disabled turbo boost on the i5 NUC many months ago to reduce variance in performance and with that the difference in CPU performance between these servers is much smaller.

Results

All of the data for the tests is on github for the i3 NUC and the i5 NUC. Results for each test are listed separately below. For all tests but scan the result has the QPS for the test with 1 client relative to the QPS for InnoDB in MySQL 5.6.35. The tests are explained here.

Graphs

There are 4 types of tests and I provided a graph for each type: write-heavy, scan-heavy, point-query, inlist-query. Fortunately the results within each group are similar and one graph per group is sufficient. The tests are explained here.

The write-heavy group includes update-inlist, update-one, update-index, update-nonindex, delete and insert. For write-heavy InnoDB does better than MyRocks on all of the tests except update-index where MyRocks benefits from read-free secondary index maintenance. The workload is low-concurrency so InnoDB in 5.7 suffers from more CPU overhead without getting a return from its high-concurrency improvements. The write-heavy tests do a read-modify-write per query, InnoDB is more efficient for the reads so it gets better QPS than MyRocks. There are two numbers for each test - the first is for the i5 NUC, the second is for the i3 NUC and each is the QPS for MyRocks relative to InnoDB 5.6.

• update-inlist - 0.60 & 0.77
• update-one - 0.78 & 0.80
• update-index - 1.15 & 1.85
• update-nonindex - 0.65 & 0.77
• delete - 0.78 & 0.97
• insert - 0.78 & 0.86

For scan-heavy InnoDB does better on all of the tests and given the range scan improvements to InnoDB in MySQL 5.7 the comparisons will get harder. MyRocks suffers from more CPU overhead in the merge iterator. There are two numbers for each test - the first is for the i5 NUC, the second is for the i3 NUC and each is the QPS for MyRocks relative to InnoDB 5.6. The graph is for read-write with range-size=100.

• read-write with range-size=100 - 0.67 & 0.75
• read-write with range-size=10000 - 0.85 & 0.87
• read-only with range-size=100 - 0.69 & 0.79
• read-only.pre with range-size=10000 - 0.94 & 0.92
• read-only with range-size=10000 - 0.85 & 0.89

The point-query group includes the point-query test run before and then after the write-heavy tests. InnoDB does better on both of the tests because MyRocks uses more CPU per query. There are two numbers for each test - the first is for the i5 NUC, the second is for the i3 NUC and each is the QPS for MyRocks relative to InnoDB 5.6. The graph is for the test run after the write-heavy tests.

• point-query run before - 0.75 & 0.80
• point-query run after - 0.71 & 0.73

The inlist-query group includes the hot-points test and the random-points tests run before and after the write-heavy tests. InnoDB does better on all tests because MyRocks uses more QPS per query. There are two numbers for each test - the first is for the i5 NUC, the second is for the i3 NUC and each is the QPS for MyRocks relative to InnoDB 5.6. The graph is for the test run after the write-heavy tests.

• hot-points - 0.32 & 0.36
• random-points run before - 0.44 & 0.45
• random-points run after - 0.26 & 0.32

update-inlist

Here and the sections that follow have the QPS and relative QPS. The relative QPS is the QPS for the engine relative to the QPS for InnoDB in MySQL 5.6.35 from the result with one client. Values are provided for the i3 and i5 NUC.

InnoDB loses ~5% of QPS from 5.6 to 5.7. InnoDB does better than MyRocks.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
2201    1.00    3349    1.00    InnoDB-5.6
2050    0.93    3192    0.95    InnoDB-5.7.17
1698    0.77    2017    0.60    MyRocks-5.6

update-one

InnoDB loses ~12% of QPS from 5.6 to 5.7. InnoDB does better than MyRocks.

i3 NUC          i5 NUC

QPS     ratio   QPS     ratio   engine
9650    1.00    10969   1.00    InnoDB-5.6
8097    0.84     9648   0.88    InnoDB-5.7.17
7705    0.80     8555   0.78    MyRocks-5.6

update-index

MyRocks does much better than InnoDB because secondary index maintenance is read free.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3134    1.00     5489   1.00    InnoDB-5.6
2983    0.95     5861   1.07    InnoDB-5.7.17
5809    1.85     6306   1.15    MyRocks-5.6

update-nonindex

See comments in the update-index section above. MyRocks QPS here is similar to the QPS for update-index, but InnoDB QPS is ~2X better here than for update-index.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
7860    1.00    10148   1.00    InnoDB-5.6
6337    0.81     9135   0.90    InnoDB-5.7.17
6066    0.77     6565   0.65    MyRocks-5.6

delete

Not much to add here.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
14590   1.00    19900   1.00    InnoDB-5.6
12758   0.87    17621   0.89    InnoDB-5.7.17
14216   0.97    15447   0.78    MyRocks-5.6

read-write with range-size=100

Range scans are more efficient in InnoDB 5.7 than in 5.6. MyRocks gets less QPS because it uses more CPU per query

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 9844   1.00    11591   1.00    InnoDB-5.6
10152   1.03    12290   1.06    InnoDB-5.7.17
 7366   0.75     7747   0.67    MyRocks-5.6

read-write with range-size=10000

Range scans are more efficient in InnoDB 5.7 than in 5.6 and the benefit is larger here than the previous test because the range scans are larger (10,000 rows vs 100). MyRocks gets less QPS because it uses more CPU per query

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
283     1.00    335     1.00    InnoDB-5.6
442     1.56    500     1.49    InnoDB-5.7.17
246     0.87    285     0.85    MyRocks-5.6

read-only with range-size=100

Range scans are more efficient in InnoDB 5.7 than in 5.6. MyRocks gets less QPS because it uses more CPU per query.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 9644   1.00    11120   1.00    InnoDB-5.6
11369   1.18    12247   1.10    InnoDB-5.7.17
 7604   0.79     7660   0.69    MyRocks-5.6

read-only.pre with range-size=10000

Range scans are more efficient in InnoDB 5.7 than in 5.6 and the benefit is larger here than the previous test because the range scans are larger (10,000 rows vs 100). MyRocks gets less QPS because it uses more CPU per query.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
226     1.00    272     1.00    InnoDB-5.6
358     1.58    394     1.45    InnoDB-5.7.17
208     0.92    256     0.94    MyRocks-5.6

read-only with range-size=100000

See the comment in the previous section. This test was run after the write-heavy tests. The test for the previous section was run before the write-heavy tests. QPS for InnoDB here and above is similar, but QPS for MyRocks here is 5% to 10% less because it costs more to search the LSM structures after random updates. I have written more about mistakes to avoid when doing a benchmark with an LSM and if you only do read-only tests before fragmenting the LSM tree you might be an optimist.

One thing I didn't notice until now is that QPS for InnoDB on read-write is larger than for read-only for tests with range-size=10000 but not for range-size=100. I don't know why yet.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
221     1.00    272     1.00    InnoDB-5.6
359     1.62    393     1.44    InnoDB-5.7.17
197     0.89    230     0.85    MyRocks-5.6

point-query.pre

InnoDB uses more CPU/query in 5.7 than in 5.6 so QPS is smaller. MyRocks has the same problem.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
18329   1.00    20505   1.00    InnoDB-5.6
16592   0.91    18126   0.88    InnoDB-5.7.17
14627   0.80    15462   0.75    MyRocks-5.6

point-query

See the comment in the previous section. This test was run after the write-heavy tests. The test for the previous section was run before the write-heavy tests. QPS for InnoDB here and above is similar, but QPS for MyRocks here is 7% to 11% less because it costs more to search the LSM structures after random updates.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
18554   1.00    19257   1.00    InnoDB-5.6
16883   0.91    17723   0.92    InnoDB-5.7.17
13557   0.73    13721   0.71    MyRocks-5.6

random-points.pre

The QPS decrease for InnoDB from 5.6 to 5.7 is much smaller here than for point-query.pre above. I assume that most of the QPS decrease from 5.6 to 5.7 is from code above InnoDB (parse, optimize, etc) and the query here fetches 100 rows by PK using an in-list. So the per-query overhead here is amortized over 100 rows versus 1 row above.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3040    1.00    3203    1.00    InnoDB-5.6
2931    0.96    3121    0.97    InnoDB-5.7.17
1374    0.45    1394    0.44    MyRocks-5.6

random-points

See the comment in the previous section. This test was run after the write-heavy tests. The test for the previous section was run before the write-heavy tests. QPS for InnoDB here and above is similar, but QPS for MyRocks here is 30% to 40% less because it costs more to search the LSM structures after random updates. The cost here is greater than the cost above for read-only and point-query because this test spends a larger fraction of the per-query time in the storage engine, so it is more sensitive to storage engine overheads. This is the same reason that the 5.6 to 5.7 regression is smaller here for InnoDB than for the tests that preceded random-points.pre.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3036    1.00    3223    1.00    InnoDB-5.6
2947    0.97    3123    0.97    InnoDB-5.7.17
 962    0.32     847    0.26    MyRocks-5.6

hot-points

This is like random-points except it fetches the same values. It is run after the write-heavy tests. It is always in-memory. InnoDB can benefit from the adaptive hash index. But I am curious why the QPS here for MyRocks is closer to the random-points test run before the write-heavy tests.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3666    1.00    4242    1.00    InnoDB-5.6
3458    0.94    3898    0.92    InnoDB-5.7.17
1334    0.36    1341    0.32    MyRocks-5.6

insert

InnoDB loses 12% to 15% of the insert rate in MySQL 5.7 because the per-insert CPU overhead is larger. MyRocks suffers the same problem.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
9340    1.00    11244   1.00    InnoDB-5.6
7853    0.84     9892   0.88    InnoDB-5.7.17
8074    0.86     8785   0.78    MyRocks-5.6