Thursday, December 7, 2017

Insert benchmark: IO-bound, high-concurrency, fast server, part 2

This is similar to the previous insert benchmark result for IO-bound and high-concurrency except it uses 1 table rather than 16 to determine how a storage engine behaves with more contention.

tl;dr
  • Inserts are much faster for MyRocks
  • The InnoDB PK uses 2X more space for the 1 table test than the 16 table test. I filed bug 88827.
  • MyRocks secondary index scans have a similar performance to InnoDB
  • MyRocks PK scans are ~2X slower than InnoDB 5.6 on the 16 table test but ~3X faster on the 1 table test. This might also be bug 88827.

Configuration

Start by reading my previous post. The test still uses 2B rows but there is only one table here when the previous test used 16 tables. The load still uses 16 concurrent clients. The read-write test still uses 16 read clients and 16 write clients. But the scan test uses 1 client here versus 16 clients on the previous test and the scan test takes longer to finish.

While I have results for InnoDB from FB MySQL I exclude them from the graphs to improve readability.

Results

All of the data for the 1-table tests is here and for the 16-table tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

For most of the results below I compare rates for this test with rates for the 16-table test and skip the graphs that show HW efficiency metrics.

Size

This graph shows the database size when the load ends for the 16 table and 1 table tests. For MyRocks and TokuDB the database size is similar for both tests. The InnoDB result is odd because the size is almost 1.25X larger for the 1 table test. From SHOW TABLE STATUS the data_length was about 2X larger for the 1 table test. From iostat output the PK scan for the 1 table test reads ~205gb while the 16 table test reads ~125gb. So the PK uses almost 2X more space than it should when there are concurrent inserters to the same table. The inserts are multi-row and the PK is auto-inc so the inserts grow the b-tree to the right. I filed bug 88827 for this.


Load

This graph shows the insert rate for the 16 and 1 table tests. Some engines get more inserts/second with 1 table, others get more with 16 tables:
  • More with 16 tables: ~1.1X more for MyRocks, ~2X more for TokuDB
  • More with 1 table: 1.3X more for InnoDB 5.6, 1.4X more for InnoDB 5.7 and 8.0



Scan

These graphs show the scan times relative to the scan time for InnoDB 5.6.35. A value > 1 means the engine is slower than InnoDB. The first graph is from the 16 table test and the second is from the 1 table test. In both cases the MyRocks secondary index scan is about as fast as InnoDB. But the PK scan MyRocks is more than 2X slower than InnoDB in the 16 table test and 2X to 3X faster than InnoDB for the 1 table test.

What might explain the difference in PK scan times? MyRocks was ~2X slower than InnoDB in the 16 table test and ~3X faster than InnoDB in the 1 table test. That is a change of 6X. The output from vmstat and iostat can help for the 16 table and 1 table tests. This is in the q5 section which is the second scan of the PK and I will compare myrocks.jun16.none with inno5635.

  • First, the InnoDB PK uses ~2X more space for the 1 table test, so there is 2X more data to scan. But with hand waving that should explain only 2X of the 6X change.
  • On the 16 table test InnoDB 5.6 gets ~3X more MB/s of storage reads compared to MyRocks: 2353.7 vs ~828.1. But on the 1 table test InnoDB 5.6 gets less storage read MB/s than MyRocks: 67.4 vs 94.8.
  • One reason for getting less read MB/s from storage is using more CPU and that appears true in this case. The Mcpu/o column has the CPU overhead per row read. For the 16 table test it is 1.278 for InnoDB 5.6 vs 1.100 for MyRocks. On the 1 table test it is 3.547 for InnoDB 5.6 vs 1.807 for MyRocks. So InnoDB is only using ~1.2X more CPU than MyRocks on the 16 table test but ~2X more CPU on the 1 table test.



Insert benchmark: IO-bound, high-concurrency, fast server

This post explains the insert benchmark with an IO-bound and high-concurrency workload for MyRocks, InnoDB and TokuDB. The goal is to understand throughput and efficiency for different storage engines and different versions of MySQL. The previous tests used an in-memory workload.

tl;dr - for an IO-bound, high-concurrency workload
  • MyRocks gets ~3X more inserts/s vs InnoDB 5.6 and ~2X more vs InnoDB 5.7/8.0
  • MyRocks has the best QPS. MyRocks gets ~10X and ~4X more than InnoDB on read-write tests because it does less IO per read and per write.
  • MyRocks PK scans are ~2X slower than InnoDB but secondary index scans are almost as fast as InnoDB. Alas, scans get faster in InnoDB 5.7. Readahead helps MyRocks.
  • MyRocks is more space efficient. InnoDB is almost 2X larger than uncompressed MyRocks and almost 4X larger than compressed MyRocks.
  • MyRocks is more write efficient. InnoDB writes ~5X more to storage per insert on the load and ~15X more on the read-write test.

Configuration

The insert benchmark is described here. The test server has 48 HW threads, fast SSD and 50gb of RAM. The database block cache (buffer pool) was set to 10gb for MyRocks and TokuDB and to 35gb for InnoDB. The database was much larger than 50gb. The test was run with 16 tables and 16 query clients. For the read-write tests there is an additional writer client for each query client. The insert benchmark loaded the tables with 2b rows (125M rows/table), then did a full scan of each index (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second per writer 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 per writer client. The scan and read-write tests use a client per table. With 16 tables there are 16 concurrent clients.

Tests were run for MyRocks, InnoDB from upstream MySQL, InnoDB from FB MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on October 16 with git hash 1d0132. Tests were repeated with and without compression. The configuration without compression is called MySQL.none in the rest of this post. The configuration with compression is called MySQL.zstd and used zstandard for the max level, no compression for L0/L1/L2 and lz4 for the other levels.
  • Upstream 5.6.35, 5.7.17, 8.0.1, 8.0.2 and 8.0.3 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases.
  • InnoDB from FB MySQL 5.6.35 was compiled on June 16 with git hash 52e058.
  • TokuDB was from Percona Server 5.7.17. Tests were done without compression and then with zlib compression.
The performance schema was enabled for upstream InnoDB and TokuDB. It was disabled at compile time for MyRocks and InnoDB from FB MySQL because FB MySQL 5.6 has user & table statistics for monitoring.

Results

All of the data for the tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

Load

The load is concurrent and there are 16 clients each loading a separate table . The graphs below have rates for each configuration relative to the rate for InnoDB 5.6.35. The graphs show the insert rate, the CPU overhead per insert, the storage write rate per insert and the storage read rate per insert.
  • MyRocks is ~3X faster than InnoDB 5.6 and more than 2X faster than InnoDB 5.7/8.0
  • MyRocks has the best CPU efficiency. InnoDB uses ~3X more CPU/insert.
  • InnoDB from FB MySQL does much better than upstream 5.6.

MyRocks and TokuDB have better write efficiency. InnoDB 5.6 writes ~5X more to storage per insert than uncompressed MyRocks and ~7X more than compressed MyRocks. I don't understand why the write rate is larger for InnoDB 5.7/8.0 than for 5.6.
MyRocks and TokuDB read less from storage per insert. There are two possible reasons for this. First, non-unique secondary index maintenance is read free. Second, the index might be smaller with them and remain in cache. I am not certain which of these explains it.

Size

This is the database size at the end of the load. The values are absolute. MyRocks is more space efficient than InnoDB. InnoDB uses almost 2X more space than uncompressed MyRocks and almost 4X more space than compressed MyRocks.


Scan

This graph shows the scan time for the PK index and and all of the secondary indexes. The value is relative to the time for InnoDB 5.6.35. The absolute value in seconds is here in the scan section. The number for the PK scan is from the second scan of it. The graph excludes FbInno5635 for readability and that version of FbInno5635 has a perf bug for concurrent scans (since fixed). The scans are concurrent and there are 16 clients each scanning indexes from separate tables.

The graph has two extra configurations: MyRocks.none.ra, MyRocks.zstd.ra. Both of these enabled filesystem readahead in RocksDB. For this workload InnoDB suffers from b-tree fragmentation for the secondary indexes but probably not for the PK. That might explain the MyRocks perf results for PK vs secondary scans.
  • MyRocks PK scans are ~2X slower than InnoDB 5.6
  • MyRocks secondary index scans are slightly faster than InnoDB 5.6
  • Readahead is a big deal for MyRocks index scans
  • Scans are faster for InnoDB starting in 5.7


Read-write, 1000 inserts/second

This section has results for the read-write test when the writer is limited to 1000 inserts/second, there are 16 tables and a reader/writer pair per table. The first graph has the QPS for short range queries. The second graph has the CPU/query. Both use values relative to InnoDB 5.6.35. All storage engines sustained the target insert rate of 16,000 rows/second.
  • MyRocks gets more than 6X the QPS compared to InnoDB
  • MyRocks uses less CPU and gets more QPS than InnoDB because it is more read and write efficient
This graph shows iostat read operations per query. The values are relative to InnoDB 5.6.35. The rate for InnoDB is ~10X the rate for uncompressed MyRocks and ~100X the rate for compressed MyRocks. I think the MyRocks indexes are larger than cache so I don't understand why this difference is so large. But I am happy about it.

This graph shows KB written to storage per insert. The values are relative to InnoDB 5.6.35. InnoDB writes ~15X more to storage per insert compared to MyRocks.


Read-write, 100 inserts/second

This section has results for the read-write test when the writer is limited to 100 inserts/second, there are 16 tables and a reader/writer pair per table. The graph has the QPS for short range queries with values relative to the value for InnoDB 5.6.35. MyRocks gets ~4X more QPS than InnoDB here while it got 10X or more in the previous section. The reason is that the write rate is lower on this test, so InnoDB uses less write IO and has more capacity for reads.



Wednesday, December 6, 2017

Insert benchmark: in-memory, high-concurrency, fast server - part 2

This is similar to the previous insert benchmark result for in-memory and high-concurrency except it uses 1 table rather than 16 to determine how a storage engine behaves with more contention. The results for 16 vs 1 table are more interesting on the IO-bound test where there are more stalls in the 1-table results.

One example of performance lost from contention is the per-index mutex for InnoDB which is locked during pessimistic changes to the B-Tree. I know this has been improved over the years but the problem has not been eliminated.

Configuration

Start by reading my previous post. The test still uses 500M rows but there is only one table here when the previous test used 16 tables. The load test still uses 16 concurrent clients. The read-write test still uses 16 read clients and 16 write clients. But the scan test uses 1 client here versus 16 clients on the previous test and the scan test takes longer to finish.

Results

All of the data for the tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

For most of the results below I compare rates for this test with rates for the 16-table test and skip the graphs that show HW efficiency metrics.

Load

This is interesting:
  • Some engines get more inserts/second with 16 tables - 1.12X more for MyRocks, 1.20X more for InnoDB 5.7, 1.17X more for InnoDB 8.0 and 3.26X more for TokuDB
  • InnoDB 5.6 gets more inserts/second with 1 table - 1.04X more for FB MySQL and 1.14X more for upstream



Scan

Scan results for 1 table are similar to scan results for 16 tables. The MyRocks scans are ~2X slower than InnoDB and InnoDB scans got faster with 5.7.


Read-write with 1000 inserts/second

The QPS for 1 table is similar to the QPS for 16 tables. I didn't mention this on the previous test but the 16 concurrent writers should sustain ~16,000 inserts/second. If they don't then the engine has a performance problem. For this test using 1 table, the October 16 build of MyRocks didn't sustain the target write rate. The average rate for it was 15677 while other engines get 15842 or better and the data is in the ips.av column here. Note that the max that my ibench client code will sustain is ~15845/second rather than 16,000 and I have yet to fix that. Regardless I will look at this the next time I run the test to understand whether MyRocks has a problem.


Read-write with 100 inserts/second

The QPS for 1 table is similar to the QPS for 16 tables.

Insert benchmark: in-memory, high-concurrency, fast server

This post explains the insert benchmark with an in-memory and high-concurrency workload for MyRocks, InnoDB and TokuDB. The goal is to understand throughput and efficiency for different storage engines and different versions of MySQL. The previous test used a low-concurrency, in-memory workload with 1 query client and 1 table. This test uses 16 concurrent clients and 16 tables.

tl;dr - for an in-memory, high-concurrency workload
  • InnoDB 5.7 has the best insert rate. The rate for MyRocks is better than InnoDB 5.6 but we have work to do to close the gap with modern InnoDB.
  • InnoDB 5.6 has the best query rates. MyRocks gets ~0.80X the QPS compared to it.
  • MyRocks index scans are slower than InnoDB 5.6: 1.54X slower for the PK and 2.35X slower for the secondary indexes. More CPU overhead is the cause.
  • MyRocks is more space efficient. InnoDB uses ~1.6X more space than MyRocks.
  • MyRocks is more write efficient. InnoDB writes ~3X more to storage per insert on the load and ~10X more on the read-write test.
  • MyRocks uses 1.24X more CPU than InnoDB 5.6 on queries but less CPU on loads
  • InnoDB 8.0 is 2.4X faster than InnoDB 5.6 on loads but gets 0.89X the QPS of InnoDB 5.6 on queries because it used 1.11X more CPU.

Configuration

The insert benchmark is described here. The test server has 48 HW threads, fast SSD and 256gb of RAM. The database block cache (buffer pool) was large enough to cache the database. The test was run with 16 tables and 16 query clients. For the read-write tests there is an additional writer client for each query client. The insert benchmark loaded the tables with 500M rows (~32M rows/table), then did a full scan of each index (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second per writer 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 per writer client. The scan and read-write tests use a client per table. With 16 tables there are 16 concurrent clients.

Tests were run for MyRocks, InnoDB from upstream MySQL, InnoDB from FB MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on October 16 with git hash 1d0132. Compression was not used. 
  • Upstream 5.6.35, 5.7.17, 8.0.1, 8.0.2 and 8.0.3 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases.
  • InnoDB from FB MySQL 5.6.35 was compiled on June 16 with git hash 52e058.
  • TokuDB was from Percona Server 5.7.17. Compression was not used.
The performance schema was enabled for upstream InnoDB and TokuDB. It was disabled at compile time for MyRocks and InnoDB from FB MySQL because FB MySQL 5.6 has user & table statistics for monitoring.

Results

All of the data for the tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

Load

The load is concurrent and there are 16 clients each loading a separate table . The graphs below have rates for each configuration relative to the rate for InnoDB 5.6.35. The graphs show the insert rate, the CPU overhead per insert and the storage write rate per insert.
  • InnoDB 5.7 has the best insert rate and is 2.47X better than InnoDB 5.6. MyRocks is 1.51X better than InnoDB 5.6.
  • MyRocks has the best CPU efficiency. 
  • MyRocks has better write efficiency. InnoDB writes ~3X more to storage per insert.
  • InnoDB from FB MySQL does much better than upstream 5.6. I assume several changes account for the improvement.

Size

This is the database size at the end of the load. The values are absolute and rounded to the nearest GB. MyRocks is more space efficient than InnoDB. Uncompressed InnoDB uses 1.6X more space than uncompressed MyRocks.

Scan

This graph shows the number of seconds to scan the PK index and all of the secondary indexes. The number for the PK scan is from the second scan of it. The x-axis truncates the result for FbInno5635 on the secondary index scan. That result was lousy due to a bug that has been fixed. The scans are concurrent and there are 16 clients each scanning indexes from separate tables.
  • MyRocks is 1.54X slower on the PK scan and 2.35X slower on the secondary scans compared to InnoDB 5.6.35. MyRocks uses more CPU for these scans based on vmstat data for q5 (the second PK scan) and q4 (one of the secondary scans) - the Mcpu/o column is the CPU overhead per row fetched.
  • Scans are faster for InnoDB starting in 5.7


Read-write, 1000 inserts/second

This section has results for the read-write test when the writer is limited to 1000 inserts/second. The first graph has the QPS for short range queries. The second graph has the KB written to storage per insert. Both use values relative to the value for InnoDB 5.6.35.
  • InnoDB 5.6.35 has the best QPS
  • MyRocks gets 0.77X the QPS compared to InnoDB 5.6.35. The problem is CPU overhead which will be obvious in the next section (read-write, 100 writes/second).
  • MyRocks is more write efficient. InnoDB writes ~10X more to storage per insert.
  • InnoDB 8.0.3 gets 0.90X the QPS compared to InnoDB 5.6.35. New CPU overhead is the cause.



Read-write, 100 inserts/second

This section has results for the read-write test when the writer is limited to 100 inserts/second. The first graph has the QPS for short range queries. The second graph has the CPU overhead per query. Both use values relative to the value for InnoDB 5.6.35. Results are similar to the previous section.
  • InnoDB 5.6.35 has the best QPS.
  • MyRocks gets 0.78X the QPS compared to InnoDB 5.6.35. MyRocks uses 1.24X more CPU/query.
  • InnoDB 8.0.3 gets 0.89X the QPS compared to InnoDB 5.6.35. It uses 1.11X more CPU/query than InnoDB 5.6.35. I assume the problem is new code above the storage engine. 



Insert benchmark: in-memory, low-concurrency, fast server

This post explains the insert benchmark with an in-memory and low-concurrency workload for MyRocks, InnoDB and TokuDB. The goal is to understand throughput and efficiency for different storage engines and different versions of MySQL.

tl;dr - for an in-memory, low-concurrency workload
  • MyRocks and InnoDB 5.6 have similar insert rates but InnoDB 5.6 has a better query rate.
  • MyRocks index scans are slower than InnoDB 5.6: 1.23X slower for the PK and 2.15X slower for the secondary indexes. More CPU overhead is the cause.
  • MyRocks is more space efficient. InnoDB uses ~1.6X more space than MyRocks.
  • MyRocks is more write efficient. InnoDB writes ~3X more to storage per insert on the load and ~15X more on the read-write test.
  • MyRocks uses more CPU than InnoDB 5.6: 16% more on the load, 37% more on read-write.
  • InnoDB 8.0 is 10% to 20% slower than InnoDB 5.6 and uses ~20% more CPU than InnoDB 5.6.

Configuration

The insert benchmark is described here. The test server has 48 HW threads, fast SSD and 256gb of RAM. The database block cache (buffer pool) was large enough to cache the database. The test was run with 1 table and 1 query client. For the read-write tests there is a writer client that runs concurrent with the query client. The insert benchmark loaded the table with 100M rows, then did a full scan of each index (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.

Tests were run for MyRocks, InnoDB from upstream MySQL, InnoDB from FB MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on October 16 with git hash 1d0132. Compression was not used. 
  • Upstream 5.6.35, 5.7.17, 8.0.1, 8.0.2 and 8.0.3 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases.
  • InnoDB from FB MySQL 5.6.35 was compiled on June 16 with git hash 52e058.
  • TokuDB was from Percona Server 5.7.17. Compression was not used.
The performance schema was enabled for upstream InnoDB and TokuDB. It was disabled at compile time for MyRocks and InnoDB from FB MySQL because FB MySQL 5.6 has user & table statistics for monitoring.

Results

All of the data for the tests is here. I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

Load

The graphs below have rates for each configuration relative to the rate for InnoDB 5.6.35. The graphs show the insert rate, the CPU overhead per insert and the storage write rate per insert.
  • MyRocks has the best insert rate. It gets 1.06X more than InnoDB 5.6.35.
  • MyRocks has better write efficiency. InnoDB writes ~3X more to storage per insert.
  • MyRocks uses 1.16X more CPU/insert than InnoDB 5.6.35. I assume the extra CPU use is from background tasks (compaction).
  • There is a regression from 5.6 to 8.0 for InnoDB as InnoDB 8.0.3 gets 0.78X the insert rate and uses 1.21X the CPU compared to InnoDB 5.6.35. I assume this is from new code above the storage engine.

Size

This is the database size at the end of the load. The values are absolute and rounded to the nearest GB. MyRocks is more space efficient than InnoDB. Uncompressed InnoDB uses 1.6X more space than uncompressed MyRocks.

Scan

This graph shows the number of seconds to scan the PK index and all of the secondary indexes. The number for the PK scan is from the second scan of it.
  • MyRocks is 1.23X slower on the PK scan and 2.15X slower on the secondary scans compared to InnoDB 5.6.35. MyRocks uses more CPU for these scans based on vmstat data for q5 (the second PK scan) and q4 (one of the secondary scans) - the Mcpu/o column is the CPU overhead per row fetched.
  • Scans are faster for InnoDB starting in 5.7. InnoDB 5.6 does the PK scan 1.4X slower and the secondary scans 1.2X slower.
  • I don't know why the secondary scan for InnoDB from FB MySQL is slower than from upstream. There was a perf bug we added, and recently fixed, for concurrent secondary scans.


Read-write, 1000 inserts/second

This section has results for the read-write test when the writer is limited to 1000 inserts/second. The first graph has the QPS for short range queries. The second graph has the KB written to storage per insert. Both use values relative to the value for InnoDB 5.6.35.
  • InnoDB 5.6.35 has the best QPS.
  • MyRocks gets 0.79X the QPS compared to InnoDB 5.6.35. The problem is CPU overhead which will be obvious in the next section (read-write, 100 writes/second).
  • MyRocks is more write efficient. InnoDB 5.7 and 8.0 write ~15X more to storage per insert than MyRocks. The rate for InnoDB 5.6.35 is an outlier and I think the cause is furious flushing.
  • InnoDB 8.0.3 gets 0.87X the QPS compared to InnoDB 5.6.35. The regression here is smaller than the regression for the load.



Read-write, 100 inserts/second

This section has results for the read-write test when the writer is limited to 100 inserts/second. The first graph has the QPS for short range queries. The second graph has the CPU overhead per query. Both use values relative to the value for InnoDB 5.6.35. Results are similar to the previous section.
  • InnoDB 5.6.35 has the best QPS.
  • MyRocks gets 0.75X the QPS compared to InnoDB 5.6.35. MyRocks uses 1.37X more CPU/query.
  • InnoDB 8.0.3 gets 0.85X the QPS compared to InnoDB 5.6.35. It uses 1.19X more CPU/query than InnoDB 5.6.35. I assume the problem is new code above the storage engine.


Tuesday, December 5, 2017

tpcc-mysql, IO-bound, high-concurrency: MyRocks, InnoDB and TokuDB

This has results for tpcc-mysql with a high-concurrency workload when the database is larger than memory. Tests were run for MyRocks, InnoDB and TokuDB. I previously shared results for an in-memory workload. While the database is larger than RAM there are few reads from storage per transaction unlike many of the other IO-bound benchmarks I run.

tl;dr:
  • InnoDB from upstream 5.7 has the best throughput and gets 1.18X more TPMC than upstream InnoDB 5.6. MyRocks with and without compression does slightly better than upstream InnoDB 5.6.
  • InnoDB is more CPU efficient. MyRocks uses 1.1X to 1.2X more CPU/transaction than upstream InnoDB 5.6.
  • MyRocks is more write efficient. InnoDB writes ~3X more to storage per transaction.
  • MyRocks is more space efficient. Uncompressed InnoDB uses 1.8X more space than uncompressed MyRocks and 2.9X more space than compressed MyRocks.
  • InnoDB has a regression from 5.7 to 8.0 - less throughput, more CPU overhead. I assume the problem is from new code above the storage engine.
  • InnoDB from FB MySQL 5.6 is almost as fast as upstream InnoDB 5.7. I assume several changes account for that result.

Disclaimer

For several reasons this isn't TPC-C. But this is tpcc-mysql. Read committed was used for MyRocks while the official benchmark requires repeatable read for some of the transaction types. When MyRocks supports gap locks this test can be repeated using repeatable read for it. Repeatable read was used for InnoDB and TokuDB.

Configuration

I used tpcc-mysql from Percona with my helper scripts. The test was run with 1000 warehouses and 20 customers. The database is larger than memory. The test server has 48 HW threads, fast SSD and 50gb of RAM. The database block cache (buffer pool) was 10g for MyRocks, 10g for TokuDB and 35g for InnoDB.

The test pattern is load and then run 12 hours of transactions in 1 hour loops. Results are reported for the last hour of transactions. I used 1000 milliseconds as the response time SLA.

Tests were run for MyRocks, InnoDB from upstream MySQL, InnoDB from FB MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on October 16 with git hash 1d0132. Tests were repeated with and without compression. The configuration without compression is called MySQL.none in the rest of this post. The configuration with compression is called MySQL.zstd and used zstandard for the max level, no compression for L0/L1/L2 and lz4 for the other levels. 
  • Upstream 5.6.35, 5.7.17, 8.0.1, 8.0.2 and 8.0.3 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases.
  • InnoDB from FB MySQL 5.6.35 was compiled on June 16 with git hash 52e058.
  • TokuDB was from Percona Server 5.7.17. Tests were done without compression and then with zlib compression.
The performance schema was enabled for upstream InnoDB and TokuDB. It was disabled at compile time for MyRocks and InnoDB from FB MySQL because FB MySQL 5.6 has user & table statistics for monitoring.

Graphs

The graphs are from the 12th hour of the transaction test. All of the data is here.  I adjusted iostat bytes written metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact.

The first graph has the TPMC for an engine and the values are relative to the TPMC for InnoDB from upstream MySQL 5.6. TPMC is transaction throughput.
  • InnoDB from 5.7.17 does the best
  • MyRocks is slightly better than InnoDB from upstream 5.6.35.
  • MyRocks doesn't lose much throughput from using compression.
  • InnoDB from FB MySQL 5.6.35 has the second best throughput which is 1.16X more than upstream 5.6.35. I am not sure which changes explain that.
  • There is a regression from 5.7 to 8.x for upstream MySQL. I assume the cause is new code above the storage engine.

The next graph has the CPU overhead per TPMC and the values are relative to upstream InnoDB 5.6. A value > 1 means that the engine uses more CPU/transaction than the base case.
  • MyRocks uses 1.12X to 1.22X more CPU than InnoDB 5.6
  • InnoDB from FB MySQL uses less CPU than InnoDB from upstream 5.6
  • There is a CPU regression from upstream 5.7 to 8.x

This graph has the KB written to storage per TPMC and the values are relative to upstream InnoDB 5.6. MyRocks is more write efficient as InnoDB writes ~3X more to storage per transaction.
The next graph has iostat read operations per transaction. The values are relative to upstream InnoDB 5.6. The absolute values are small, most are less than 0.10. While the test database is larger than memory the working set almost fits in memory. I tried using 2X more warehouses, but that takes longer to load and didn't change the read/transaction rate by much to justify the extra time.

This graph shows the size of the database after the load and after 12 hours of transactions. Rows are added during the transaction test, the database is expected to grow and the growth is a function of the transaction rate. Fortunately MyRocks and InnoDB have similar transaction rates so it is mostly fair to compare database size.

MyRocks is more space efficient than InnoDB. Uncompressed InnoDB uses ~1.8X more space than uncompressed MyRocks and 2.9X more space than compressed MyRocks.

Monday, December 4, 2017

Linkbench: IO-bound, small server, MyRocks and InnoDB

This post explains MySQL performance for Linkbench on a small server. This used a low-concurrency IO-bound workload to measure response time, IO and CPU efficiency. Tests were run for MyRocks and InnoDB on an Intel NUC. The previous post used an in-memory workload.
'
tl;dr - for IO-bound linkbench
  • 99th percentile response times are more than 2X better with MyRocks than InnoDB
  • Throughput results are mixed -- InnoDB 5.6 loads faster, MyRocks does transactions faster. The relative load rate for MyRocks is ~0.84 the InnoDB rate. The relative transaction rate for MyRocks is ~1.14X the InnoDB rate.
  • CPU efficiency is mixed. MyRocks uses more CPU than InnoDB 5.6 on the load and less on transactions.
  • MyRocks is more write efficient. Modern InnoDB writes ~1.6X more to storage per insert and ~25X more per transaction compared to MyRocks.
  • MyRocks is more space efficient. Uncompressed InnoDB uses ~1.5X more space than uncompressed MyRocks and ~3X more space than compressed MyRocks.
  • There is a regression from MySQL 5.6.35 to 8.0.2. InnoDB 8.0.2 gets 0.75X the load rate and 0.90X the transaction rate compared to InnoDB 5.6.35. New CPU overhead is the problem as InnoDB 8.0.2 uses 1.27X more CPU for the load and 1.15X more CPU for transactions compared to InnoDB 5.6.35.

Configuration

I used my Linkbench repo and helper scripts to run linkbench with maxid1=80M, loaders=1 and requestors=1 so there will be 2 concurrent connections doing the load and 1 connection running transactions after the load finishes. My linkbench repo has a recent commit that changes the Linkbench workload and this test included that commit. The test pattern is 1) load and 2) transactions. The transactions were run in 12 1-hour loops and I share results from the last hour. The test server is the i5 NUC described here were 4 HW threads, SSD and 16gb of RAM. The database is larger than RAM after the load.

Tests were run for MyRocks from FB MySQL 5.6.35 and InnoDB from upstream MySQL. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on October 16 with git hash 1d0132. Tests were repeated without and with compression. The configuration without compression is called MySQL.none in the rest of this post. The configuration with compression is called MySQL.zstd and used zstandard for the max level, no compression for L0/L1/L2 and lz4 for the other levels.
  • Upstream 5.6.35, 5.7.17 and 8.0.2 were used with InnoDB. SSL was disabled and 8.x used the same charset/collation as previous releases. I also set innodb_purge_threads=1 to reduce mutex contention.
The performance schema was enabled for upstream InnoDB. It was disabled at compile time for MyRocks because FB MySQL still has user & table statistics for monitoring.

Graphs

All of the data is here. I adjusted iostat metrics for MyRocks because it currently counts bytes trimmed as bytes written which is an issue for RocksDB but my adjustment is not exact. The first two graphs show the load and transaction rates relative to the rate for InnoDB from MySQL 5.6.35.
  • p99 response times are more than 2X better for MyRocks than InnoDB 5.6
  • MyRocks gets ~0.84X the load rate and ~1.14X the transaction rate compared to InnoDB 5.6
  • InnoDB 8.0 gets 0.75X the load rate and 0.90X the transaction rate compared to InnoDB 5.6
CPU efficiency is mixed. These graphs have CPU overhead per insert during the load and per transaction. This is measured by the us and sy columns from vmstat. The values on the graph are relative to InnoDB from MySQL 5.6.35.

MyRocks uses more CPU than InnoDB 5.6 on the load and and less for transactions. Some of the CPU overhead for the load is from compaction and that is decoupled from the user connections doing inserts.

There is a CPU regression from MySQL 5.6.35 to 8.0.2 for InnoDB. InnoDB 8.0.2 uses 1.27X more CPU than InnoDB 5.6.35 for the load and 1.15X more for transactions. I assume this is from new code.
MyRocks is more write efficient. These graphs have the KB written to storage per insert during the load and per transaction. The values on the graphs are relative to InnoDB from MySQL 5.6.35. Modern InnoDB writes ~1.6X more to storage per insert and ~25X more per transaction compared to MyRocks. An SSD will last longer with MyRocks.
MyRocks is more space efficient. Uncompressed InnoDB uses more space than MyRocks after both the load and transaction tests. It uses ~1.5X more than uncompressed MyRocks and ~3X more than compressed MyRocks. One problem for InnoDB is B-Tree fragmentation. Leveled compaction in MyRocks wastes less space. The graph has the database size in GB (not using relative values here).