Insert benchmark, in-memory, Intel NUC: MyRocks vs InnoDB

I repeated my performance tests using more storage engines, newer versions of MyRocks and possibly better my.cnf settings. This post explains the in-memory insert benchmark on Intel NUC servers with results for MyRocks and InnoDB.

tl;dr

• MyRocks sustains higher insert rates
• InnoDB writes about 10X more per insert during the load and more than 30X per insert during one of the read-write tests compared to MyRocks. Better write efficiency with MyRocks means that SSD devices last longer and I replaced many SSDs this year.
• Index scans are about 2X faster on InnoDB compared to MyRocks.
• InnoDB suffers more write stalls during the read-write test on the i3 NUC
• InnoDB gets more read QPS during the read-write test compared to MyRocks but there is a regression from 5.6 to 5.7

Configuration

The tests used a MyRocks build from FB MySQL 5.6.35 and August 15 with git hash 0d76ae and then upstream InnoDB 5.6.35 and 5.7.17. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark 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, use the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention on the small servers. For all tests the binlog was enabled but fsync was disabled for the binlog and I set flush_log_at_trx_commit=2 for both engines. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

In this test the database is cached by MyRocks and InnoDB. The insert benchmark loaded 10M rows into one table, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Results

All of the data for the graphs is here. Note that Linux iostat can overstate bytes written by RocksDB by 2X because it counts bytes trimmed as bytes written. The data in github does not adjust for that. The numbers here for wKB/i does by dividing the measured value by 2 -- which is an estimate. Note that even if my adjustment were exact this value is still missing bytes written by flash GC and when I measured that in the past it was much worse for InnoDB.

Results: load

This section has results for the load.

This graph has the insert rate for each engine relative to the rate for MyRocks. I am not surprised that MyRocks has a better insert rate than InnoDB because it does less work per insert than InnoDB. MyRocks secondary index maintenance is read free and MyRocks has much less write amplification. There is a regression in the insert rate for InnoDB from 5.6 to 5.7 and I will cover that in detail in another post.

These tables have more data for the i3 NUC including the average insert rate (IPS), the CPU overhead per insert (CPU/i) and KB written to storage per insert (wKB/i). The first table has the absolute values and the second the values relative to MyRocks. InnoDB writes ~10X more to storage per insert compared to MyRocks. I appreciate that MyRocks helps my SSD devices last longer. The variance in CPU/i values are harder to explain but the CPU overhead includes foreground and background processing as well as anything else running on the test server.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         24570   2004     0.78
InnoDB-5.6      18797   1737     8.87
InnoDB-5.7      15083   3497    10.36

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.77    0.87    11.37
InnoDB-5.7      0.61    1.75    13.28

These tables have more data for the i5 NUC. There is less difference between the engines here than on the i3 NUC. But InnoDB continues to write more than 10X to storage per insert compared to MyRocks.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         31847   1526     0.77
InnoDB-5.6      29586   1461     8.06
InnoDB-5.7      25907   1491     9.66

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.93    0.96    10.47
InnoDB-5.7      0.81    0.98    12.55

Results: scan

This section has results for the index scans. There are 5 scans: 1 is the PK, 2/3/4 are the secondary indexes and then 5 is the PK again. The 2+3+4 column has the time to scan all secondary indexes. This time is about 2X worse for MyRocks and we expect to make that better.  The PK scan is also slower I will write more about that in another post.

Index scans are slower on MyRocks than InnoDB. I assume there is more CPU overhead from merging iterators from different levels of the LSM tree. In this case all data is cached by the database so there are no reads from storage.

#seconds for each index scan
1       2       3       4       5       2+3+4   index/engine
-       -       -       -       -       -----   ------------
7       8       8       8       5       24      MyRocks
4       4       4       4       4       12      InnoDB-5.6
3       4       7       3       3       14      InnoDB-5.7

Results for the i5 NUC are similar to the i3 NUC.

#seconds for each index scan
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       7       8       7       5       22      MyRocks
3       4       3       4       3       11      InnoDB-5.6
3       3       3       2       3        8      InnoDB-5.7

And a graph of the results from the 2+3+4 column because some readers like graphs.

Results: read-write, 1000 writes/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The tables below have the most interesting data. The  metrics are explained here. All of the engines were able to sustain the target insert rate on average (ips.av). The value is 999 rather than 1000 because of implementation artifacts. There were more write stalls on InnoDB than MyRocks based on the 99th percentile insert rate as ips.99 is lower for InnoDB on the i3 NUC. InnoDB sustained a better query rate than MyRocks, although there is a regression from 5.6 to 5.7 but that is a topic for another post. InnoDB writes more than 30X to storage per insert compared to MyRocks.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     3684    3329     1.24    7307   1.8    2.5   26.9  MyRocks
999    994     4817    4454    43.90    5970   3.4   43.8   28.8  InnoDB-5.6
999    995     3748    3540    40.27   13834   3.4   40.2   51.8  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     4390    4006     1.26    6004   1.8    2.5   26.4  MyRocks
999    998     5798    5410    44.12    4846   3.4   44.1   28.1  InnoDB-5.6
999    998     5184    4917    40.62    5186   3.4   40.6   26.9  InnoDB-5.7

Results: read-write, 100 writes/second

This section has results for the read-write tests where the writer does 100 inserts/second. The metrics are explained here. Because the write rate is lower than in the previous section, the reads account for more of the overhead. The Mcpu/q result is larger for MyRocks than for InnoDB-5.6 which might explain why InnoDB does more read QPS. Again, InnoDB writes more to storage per insert and there is a regression from 5.6 to 5.7.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     3683    3416     1.35    6706   1.8   0.3    24.7  MyRocks
100    100     4729    4499    51.85    5457   3.4   5.2    25.8  InnoDB-5.6
100    100     3906    3717    52.94   12636   3.4   5.3    49.4  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     4444    4159     1.38    5538   1.8    0.3   24.6  MyRocks
100    100     5937    5548    51.95    4309   3.4    5.2   25.6  InnoDB-5.6
100    100     5349    4976    55.24    4803   3.4    5.5   25.7  InnoDB-5.7

These graphs show the read QPS relative to MyRocks for the read-write tests from above.