Tuesday, September 19, 2017

Linkbench: IO-bound, large server

This has results for IO-bound Linkbench on a large server. I previously shared results for in-memory & low-concurrency Linkbench.

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.
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.
  • 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

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
* 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

Charts

Charts for load and transaction throughput using the ips and tps columns from the tables above.

2 comments:

  1. Hello Sir,

    How 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

    ReplyDelete
    Replies
    1. 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:
      https://github.com/mdcallag/mytools/blob/master/bench/run_linkbench/run.sh

      Delete