RocksDB, InnoDB and TokuDB vs sysbench

This compares RocksDB and InnoDB storage engines using sysbench and extends the results I shared yesterday. I continue to get great performance from MyRocks and this is a bonus as my motivation for using it is better storage efficiency.

tl;dr - MyRocks is usually equal to or faster than InnoDB on these tests

Configuration

The test server for InnoDB and RocksDB has 2 sockets, 24 cores (48 HW threads), 256G of RAM and a fast SSD. I also ran tests for TokuDB on an older server, but the HW is too much slower so I won't share the results here. From past experience, TokuDB does poorly on these tests.

For all tests I disabled the binlog and sync-on-commit. The my.cnf files were similar to my previous post but included extra settings inherited from production.

I used my sysbench branch in three configurations:

• single-threaded & CPU-bound - engines used a buffer pool large enough to cache the database. Writes were fast thanks to the fast SSD. Tests were run for one database client, sysbench process & database table. The table had 10M rows.
• multi-threaded & CPU-bound - engines used a buffer pool large enough to cache the database. Writes were fast thanks to the fast SSD. Tests were run for 8, 16, 24, 32, 40 and 48 concurrent clients. Tests used 8 sysbench processes and 8 tables (table per process). Each table had 10M rows.
• multi-threaded & IO-bound - engines used a 1G buffer pool and buffered IO so reads were fast from the OS page cache. Writes were fast thanks to the fast SSD. Tests were run for 8, 16, 24, 32, 40 and 48 concurrent clients. Tests used 8 sysbench processes and 8 tables (table per process). Each table had 10M rows.

Four tests were run for each configuration:

• point-query - fetch all columns of one row by primary key lookup (command line). Performance is measured as queries per second (QPS) and each query is an auto-commit SELECT statement.
• read-only - the sysbench read only transaction (command line). Performance is measured as transactions per second (TPS).
• read-write - the sysbench read write transaction (command line). Performance is measured as transactions per second (TPS).
• increment - increment a non-indexed column of one row by primary key lookup (command line). Performance is measured as queries per second (QPS) and each query is an auto-commit UPDATE statement. The BEGIN and COMMIT statements are not used.

Finally, I tested the following binaries:

• My56.Rocks - Facebook MySQL 5.6, RocksDB engine with zlib compression
• My56.Inno - MySQL 5.6.29, InnoDB
• My56.Inno.zlib - MySQL 5.6.29, InnoDB with zlib compression
• My57.Inno - MySQL 5.7.10, InnoDB
• My57.Inno.zlib - MySQL 5.7.10, InnoDB with zlib compression

Single-threaded & CPU-bound

The first graph is for QPS on the point-query and increment tests. The second graph is for TPS on the read-only and read-write tests. Data for the graphs is here.

Performance summary:

• RocksDB is comparable with InnoDB for point queries, increment and read-only.
• RocksDB is comparable with uncompressed InnoDB for read-write. Compressed InnoDB is slower for read-write and I didn't try to explain it.
• Compressed InnoDB matches uncompressed InnoDB on the point-query test because compression code isn't hit during the test as the buffer pool is large enough to cache all pages as uncompressed.
• Compressed InnoDB is slower on increment because some (de)compression operations are done in the foreground and increase response time.

Multi-threaded & CPU-bound

Tests were run for 8 to 48 concurrent clients using 8 sysbench processes and a different table per process. Data for the graphs is here.

Performance summary:

• All engines were great on the point-query and read-only workloads but MySQL 5.6 is faster than 5.7. The difference for point-query is ~12% at 8 clients and ~4% at 48 clients. The difference for read-only is ~12% at 8 clients and ~10% at 48 clients.
• InnoDB in MySQL 5.6 suffers on the increment workload. I did not debug it. InnoDB in MySQL 5.7 was ~15% faster than MyRocks in 5.6.
• Compressed InnoDB in MySQL 5.6 and 5.7 suffers on the read-write workload. Given that MyRocks matched InnoDB in MySQL 5.6 I wonder if the 5.7 improvements are engine independent.

Multi-threaded & IO-bound

All engines used a 1G buffer pool for these tests with the database cached by the OS. Tests were run for 8 to 48 concurrent clients using 8 sysbench processes and a different table per process. All data for the graphs is here.

Performance summary:

• For point-query and read-only uncompressed InnoDB did best, MyRocks was next best and compressed InnoDB was the worst
• For read-write uncompressed InnoDB in MySQL 5.7 and MyRocks were best. Other engines were similar and not as good.
• For increment MyRocks was the best, followed by InnoDB in MySQL 5.7 followed by InnoDB in MySQL 5.6.