The value of write efficiency for the insert benchmark

I shared results last week for the insert benchmark. The test server for that result had fast SSD courtesy of several NVMe NAND flash devices. I repeated tests on a server with slower SSD and the results are interesting. Regardless of the storage device, MyRocks did better when the database was larger than RAM and InnoDB 5.7.10 did better when the database fit in RAM. But the difference between MyRocks and InnoDB 5.7.10 for the in memory workload is much smaller on slow SSD than it is on fast SSD. It is always risky to ignore the context from benchmark results and the important context here is the performance of the storage device.

Slower SSD hurts InnoDB more than it hurts MyRocks because MyRocks is more efficient for writes. Spending less IO on writes saves IO to do more writes or more reads as I previously demonstrated for Linkbench.

When the database fits in RAM there are no reads from storage. But there are many writes to storage whether or not the database is larger than RAM and the insert benchmark workload is always IO heavy. From looking at PMP stacks InnoDB throughput is limited by the rate at which the redo log is written and InnoDB in 5.6.26 has more CPU overhead from mutex contention than in 5.7.10. Note that the binlog was enabled but sync-on-commit was disabled so there was more stress on redo log throughput.

Workload

The workload is fully described in my previous post. 500m rows are inserted for the in memory test and 2b rows for the larger than memory test. All tests use 16 clients, each client inserts to a different table and each table has 3 secondary indexes to maintain.

The slower SSD provides about 10k page reads second versus more than 100k/second provided by the fast SSD server.  Both servers have similar CPUs and RAM.

By in memory I really mean that the database working set fits in memory but it is easier to ignore that and just claim the database fits in memory. Sorry for the confusion.

Throughput

The throughput listed below is the average insert rate during the test. Things that I notice in the results include:

• InnoDB loses more throughput when going from fast to slow SSD and from in memory to larger than memory workloads.
• Restating the previous point, MyRocks throughput is more stable when going from fast to slow SSD and from in memory to larger than memory workloads.
• InnoDB in MySQL 5.7.10 scales much better than 5.6.26 for in memory write-heavy workloads

Throughput for in memory database

            slow-SSD  fast-SSD
MyRocks        83766    102712
InnoDB-5.7    124782    268873
InnoDB.5.6     66251    111111

Throughput for larger than memory database
            slow-SSD  fast-SSD
MyRocks        86401   115234
InnoDB-5.7     38207    65604
InnoDB-5.6     14784    52812

IO Efficiency

Things that I notice in the results include:

• the value for wKB/i for MyRocks is somewhat smaller than InnoDB for the in memory database and much smaller than InnoDB for the larger than memory database. I expected this.
• InnoDB in MySQL 5.7 is able to sustain higher IO rates than in 5.6 thanks to many performance improvements in InnoDB.
• Assuming the slow SSD saturates around 400 MB/s of IO, then writing less per insert for MyRocks means it gets more inserts/second when IO saturates.

Legend:
* IPS - average inserts/second
* rMB/s, wMB/s - read & write MB/s via iostat
* rKB/i, wKB/i - read & write KB per insert via iostat
* note that iostat usually overstates bytes written by 2X
  for RocksDB because it counts bytes trimmed as bytes written

IO metrics for in memory database
             IPS     rMB/s   wMB/s   rKB/i   wKB/i
- slow SSD
MyRocks       83766    0      280     0       3.34
InnoDB-5.7   124782    0      533     0       4.27
InnoDB-5.6    66251    0      281     0       4.24
- fast SSD
MyRocks      102712    0      339     0       3.30
InnoDB-5.7   268873    0     1163     0       4.35
InnoDB-5.6   111111    0      451     0       4.05

IO metrics for larger than memory database
             IPS     rMB/s   wMB/s   rKB/i   wKB/i
- slow SSD
MyRocks       86401   86      346     1.00    4.00
InnoDB-5.7    38207   42      385     1.21   10.08
InnoDB-5.6    14784   36      169     2.44   11.41
- fast SSD
MyRocks      115234  118      457     1.03    3.96
InnoDB-5.7    65604   75      878     1.14   13.38
InnoDB-5.6    52812   71      525     1.34    9.94