Friday, September 8, 2017

Linkbench - in-memory, low-concurrency

After a few weeks of sysbench it is now time for more complex workloads and the first one is Linkbench with a cached database and low-concurrency. I prefer to start with cached & low-concurrency configurations before trying IO-bound & high-concurrency.

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.


No comments:

Post a Comment