tl;dr
- Compression doesn't hurt MyRocks performance
- MyRocks matches or beats InnoDB performance with much better efficiency
Configuration
I used my Linkbench repo and helper scripts to run sysbench with maxid1=1B, loaders=4 and requestors=16 so there will be 4+1 concurrent connections doing the load (4 for link/count tables, 1 for node table) and 16 connections 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 50gb of RAM available to the OS page cache and database.
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.
- MyRocks was compiled on August 15 with git hash 0d76ae. The block cache was set to 10gb. Tests were done without compression and then with no compression for L0/L1/L2, LZ4 for L3 to the next to last level and then Zstandard for the max level.
- InnoDB was from upstream 5.6.35, 5.7.17 and 8.0.2. The buffer pool was set to 35gb. The performance schema was enabled. Compression was not used.
- TokuDB was from Percona Server 5.7.17. The db cache was set to 10gb. Tests were done without compression and then zlib.
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.
- MyRocks has the best insert rate and compression doesn't hurt the rate
- There is a regression for InnoDB from 5.7 to 8.0.2
- Write efficiency (wKB/i) is best for TokuDB and similar between MyRocks and InnoDB
- CPU efficiency (Mcpu/i) is similar for MyRocks and InnoDB
- Space efficiency (size) is best for MyRocks and TokuDB. Uncompressed MyRocks uses much less space than uncompressed InnoDB
ips wKB/i Mcpu/i size rss rMB/s wMB/s cpu engine
142812 2.03 79 948 7.5 0.3 290.3 11.3 myrocks.none
144232 2.55 87 439 5.0 1.9 367.7 12.6 myrocks.zstd
132297 1.04 75 15xx 42.7 0 136.6 9.9 inno5635
138218 1.04 81 15xx 39.4 0.1 144.0 11.2 inno5717
122495 1.05 96 15xx 39.5 0.1 128.8 11.8 inno802
36737 1.68 246 12xx 11.1 0.1 61.6 9.0 toku5717.none
37032 1.18 257 442 11.6 0.1 43.8 9.5 toku5717.zlib
legend:
* ips - inserts/second
* wKB/i - iostat KB written per insert
* Mcpu/i - normalized CPU time per insert
* wMB/s, rMB/s - iostat write MB/s & read MB/s
* size - database size in GB at test end
* cpu - average value of vmstat us + sy columns
* rss - mysqld RSS in GB
* rss - mysqld RSS in GB
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.
- MyRocks and InnoDB have the best transaction rates
- Write efficiency (wKB/t) is much better for MyRocks than for InnoDB
- CPU efficiency (Mcpu/t) is similar for MyRocks and InnoDB
- Space efficiency (size) is best for MyRocks and TokuDB (see here)
- MyRocks has the the best response times and compression doesn't hurt it
tps r/t rKB/t wKB/t Mcpu/t ul gl rMB/s wMB/s cpu engine
34701 1.16 22.41 1.27 659 1 0.9 777.7 44.2 22.9 myrocks.none
34622 1.08 13.12 1.27 758 1 0.9 454.4 44.1 26.2 myrocks.zstd
25775 1.16 18.50 12.05 672 6 4 476.8 310.5 17.3 inno5635
34539 1.15 18.39 11.91 609 3 2 635.0 411.4 21.0 inno5717
33578 1.14 18.23 11.97 641 3 2 612.1 401.9 21.5 inno802
8982 3.07 188.97 5.64 2685 13 12 1697.3 50.7 24.1 toku5717.none
12448 1.20 17.42 2.84 2644 6 3 216.9 35.4 32.9 toku5717.zlib
legend:
* tps - transactions/second
* r/t - iostat reads per transaction
* r/t - iostat reads per transaction
* rKB/t, wKB/t - iostat KB read & 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
* rMB/s, wMB/s - iostat read MB/s & write MB/s
* cpu - average CPU utilization from vmstat us & sy
* cpu - average CPU utilization from vmstat us & sy
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)
Hello Sir,
ReplyDeleteHow did you get this result in tabular column? I used following command and got different results. Please advice how to achieve this.
I do not know how to use "helper scripts ".
./bin/linkbench -c config/MyConfig.properties -csvstats final-stats.csv -csvstreams streaming-stats.csv -r
final-stats.csv
------------
op,count,p25_low,p25_high,p50_low,p50_high,p75_low,p75_high,p95_low,p95_high,p99_low,p99_high,max,mean
ADD_NODE,3,0,0.1,3,4,5,6,5,6,5,6,6.01,5.11
UPDATE_NODE,4,2,3,3,4,5,6,5,6,5,6,9.54,5.25
GET_NODE,38,0.4,0.5,0.7,0.8,2,3,4,5,4,5,9.06,1.51
ADD_LINK,4,4,5,6,7,9,10,9,10,9,10,11.22,7.95
DELETE_LINK,2,0,0.1,1,2,1,2,1,2,1,2,27.7,14.8
COUNT_LINK,1,0,0.1,0,0.1,0,0.1,0,0.1,0,0.1,0.48,0.48
MULTIGET_LINK,8,0.5,0.6,0.6,0.7,1,2,1,2,1,2,1.47,0.88
GET_LINKS_LIST,20,0.6,0.7,1,2,3,4,7,8,7,8,10.79,2.48
streaming-stats.csv
-----------------------
threadID,timestamp,op,totalops,totalerrors,ops,sampleDuration_us,sampleOps,mean_us,min_us,p25_us,p50_us,p75_us,p90_us,p95_us,p99_us,max_us
1,1510135838,MULTIGET_LINK,4,0,4,37,4,700.25,305,633,637,637,1226,1226,1226,1226
1,1510135838,GET_LINKS_LIST,5,0,5,37,5,1350.6,359,462,678,2122,3132,3132,3132,3132
1,1510135838,COUNT_LINK,0,0,0,37,0,,,,,,,,,
1,1510135838,UPDATE_LINK,0,0,0,37,0,,,,,,,,,
1,1510135838,ADD_LINK,1,0,1,37,1,4713.0,4713,4713,4713,4713,4713,4713,4713,4713
1,1510135838,RANGE_SIZE,5,0,5,37,5,2.4,0,2,2,2,6,6,6,6
1,1510135838,ADD_NODE,0,0,0,37,0,,,,,,,,,
1,1510135838,UPDATE_NODE,1,0,1,37,1,9544.0,9544,9544,9544,9544,9544,9544,9544,9544
1,1510135838,DELETE_NODE,0,0,0,37,0,,,,,,,,,
1,1510135838,GET_NODE,9,0,9,37,9,1377.888888888889,233,404,791,2645,3369,3369,3369,3369
Thanks,
Siva
I use grep & awk with a little copy/paste. I don't start with csv output, and didn't know (or forgot) there was support for that. So I start with output that is slightly easier to parse. See this for one of my helper scripts:
Deletehttps://github.com/mdcallag/mytools/blob/master/bench/run_linkbench/run.sh