tl;dr:
- InnoDB from MySQL 5.6 had the best throughput
- CPU efficiency is similar for MyRocks and InnoDB
- There is a CPU regression from MySQL 5.6 to 5.7 to 8.x
- Write efficiency was similar for all engines on the load test but much better for MyRocks and TokuDB on the transaction test.
Configuration
I used my Linkbench repo and helper scripts to run sysbench with maxid1=10M, 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 results with that change are labeled new config while results without the change are labeled old config. I share both. 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 has 48 HW threads, fast SSD and 256gb of RAM.
Tests were run for MyRocks, InnoDB from upstream 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 but the database cache was made large enough to cache the ~10gb database.
- MyRocks was compiled on August 15 with git hash 0d76ae. Compression was not used.
- InnoDB was from upstream 5.6.35, 5.7.17 and 8.0.2. The performance schema was enabled.
- TokuDB was from Percona Server 5.7.17. Compression was not used.
Load Results
All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. The results for the old config are similar.
- InnoDB 5.6 has the best insert rate but there is a regression from InnoDB in 5.6 to 5.7 to 8.0.2
- Write efficiency (wkb/i) is similar for all engines
- CPU efficiency (Mcpu/i) is similar for MyRocks and InnoDB
ips wkb/i Mcpu/i size wMB/s cpu engine
54283 1.60 83 14 86.7 4.5 myrocks
64402 1.02 72 16 65.6 4.6 inno5635
56414 1.03 77 16 58.2 4.3 inno5717
42954 1.02 97 16 45.1 4.2 inno802
21611 1.42 179 14 30.7 3.9 toku5717
legend:
* ips - inserts/second
* wkb/i - iostat KB written per insert
* Mcpu/i - normalized CPU time per insert
* wMB/s - iostat write MB/s, average
* size - database size in GB at test end
* cpu - average value of vmstat us + sy columns
Transaction Results
These are results from the 12th 1-hour loop of the transaction phase. All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. I will explain them. The results for the old config are similar.
- InnoDB 5.6 has the best transaction rate but there is a regression from 5.6 to 5.7 to 8.0.2
- Write efficiency (wkb/t) is much better for TokuDB and MyRocks than for InnoDB
- CPU efficiency (Mcpu/t) is similar for MyRocks and InnoDB
- Response times are similar between MyRocks and InnoDB
tps wkb/t Mcpu/t size un gn ul gl wMB/s engine
5489 0.78 642 15 0.3 0.1 0.5 0.5 4.3 myrocks
7239 5.15 524 26 0.3 0.1 0.4 0.2 37.3 inno5635
6463 5.17 580 26 0.3 0.1 0.5 0.3 33.4 inno5717
5855 5.25 623 25 0.3 0.1 0.6 0.3 30.7 inno802
3333 0.08 1043 18 0.6 0.2 1.0 0.8 10.0 toku5717
legend:
* tps - transactions/second
* wkb/t - iostat KB written per transaction
* Mcpu/t - normalized CPU time per transaction
* size - database size in GB at test end
* un, gn, ul, gl - 99th percentile response time in millisecs for UpdateNode, GetNode, UpdateList and GetLinkedList transactions
* wMB/s - iostat write MB/s, average
Charts
Charts for load and transaction throughput using the ips and tps columns from the tables above.
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment