Tuesday, February 28, 2017

Part 3: sysbench, a large server and small database

This is part 3 of my performance report for sysbench and MyRocks. For this test I use a large server (24 cores, 48 threads, fast NVMe SSD). A small server was used for part 1 and part 2.

This test is done with a small and cached database - 8M rows. InnoDB is a great choice for such a workload. I am not surprised that MyRocks and TokuDB are not great at this workload. The value for MyRocks and TokuDB is better compression and less write-amplification and that usually requires a larger database and the benefit grows when the database doesn't fit in RAM. But early evaluators might use sysbench so I want to understand how they perform in this setup.

tl;dr
  • The common pattern was InnoDB was faster than MyRocks and MyRocks was faster than TokuDB. 
  • InnoDB in MySQL 5.7.10 is much faster than in MySQL 5.6.26 on tests that are write-heavy or have long range scans. But InnoDB in 5.7 isn't strictly faster than in 5.6 even at high concurrency.
  • MyRocks suffers on tests with longer range scans but did pretty good for point queries.
  • TokuDB was the slowest for all of the workloads except read-write with --oltp-range-size=10000 and read-only with --oltp-range-size in 100 and 10000.
  • Percona fixed a bug in TokuDB that I reported. Thank you.
It can be interesting to compare this result with the result from the smaller servers I use at home. The smaller server has a slower CPU (core i3) and slower SSD. Unfortunately I need to repeat tests on my work servers using the latest version of sysbench.

Details

I used version of sysbench that is a few months old with an older version of my scripts. For my home servers I upgraded to modern sysbench and I will soon do that at work. I will also upgrade from MySQL 5.7.10 to a more recent version and that should make a famous MySQL support guru happy.

The test server has 24 cores, 48 threads, 2 sockets and 256gb of RAM. The storage is from multiple NVMe SSDs. I tested 4 engines -- myrocks-5635 is MyRocks from FB MySQL merged to upstream MySQL 5.6.35, innodb-5626 is InnoDB from upstream MySQL 5.6.26, innodb-5710 is InnoDB from upstream MySQL 5.7.10 and tokudb-5717 is TokuDB from Percona Server 5.7.17-11. The my.cnf files are here for MyRocks, InnoDB-5.7 and InnoDB-5.6. In all cases the binlog is enabled and fsync is disabled for both the storage engine redo log and the binlog.

Tests are run for 1 to 128 concurrent clients. When describing the results I divide that into
low concurrency (<= 8 clients), medium concurrency (16 to 40 clients) and high concurrency (>= 48 clients).  The test was run at each concurrency level for 180 seconds for read-heavy tests and 300 seconds for write-heavy tests. The sysbench process is run on the same host as mysqld.

Tests are run in this order:
  • prepare - this isn't a test, it creates and loads the tables. There were 8 tables with 1M rows per table. The sysbench table has one secondary index.
  • update-only and secondary index maintenance is required
  • update-only and secondary index maintenance is not required
  • read-write with --oltp-range-size=100 and then optionally with --oltp-range-size=10000
  • read-only with --oltp-range-size in 10, 100, 1000 and 10000. I don't report results for --oltp-range-size=1000
  • point queries
  • insert-only
Results

For the performance summaries below I use "X >> Y >> Z" to mean X is faster than Y and Y is faster than Z. I did not include graphs for one or both of 1) that is a lot more work and 2) I want some readers to take the time and think about the numbers. Unfortunately the formatting is not great for a wide table.

Update-only with secondary index maintenance

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
2895    10363   20118   34017   38720   42138   44736   44646   44169   43633   43104   42690   42442   myrocks-5635
4029    8373    15522   24563   32448   37061   40251   42522   44219   44631   45123   45762   45808   innodb-5626
4254    8792    17020   30524   48443   60623   67052   68773   69232   70556   72134   72696   74222   innodb-5710
2145    4026     7344   13575   21222   27807   30039   29541   29106   27964   26493   25435   23210   tokudb-5717

Throughput:
  • 1 to 8 clients - MyRocks >> InnoDB-5.7 >> InnoDB-5.6 >> TokuDB. I don't know why MyRocks was lousy for 1 client but OK for 2+.
  • 16 to 40 clients - InnoDB-5.7 >> MyRocks >> InnoDB-5.6 >> TokuDB
  • 48+ clients - InnoDB-5.7 >> MyRocks, InnoDB-5.6 >> TokuDB

Update-only without secondary index maintenance

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
2901   10696    20983   37078   43756   47549   50627   52753   52318   51690   50853   50003   49074   myrocks-5635
6384   12625    23847   40501   51505   53024   53587   53523   53316   52483   51632   51276   51117   innodb-5626
5618   11060    21361   38810   58197   69717   74286   75519   75545   76520   77335   78117   79152   innodb-5710
2260    4470     8206   15322   26552   33024   37739   41461   44358   44415   41899   39626   35341   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB.  I don't know why MyRocks was lousy for 1 client but OK for 2+.
  • 16 to 40 clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB.
  • 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB

Read-write with --oltp-range-size=100

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
4798    9955    20426   46361   98537  132010  153726  165477  167280  166549  168513  166807  166722   myrocks-5635
6391   12591    27534   61157  112516  146970  172971  193107  196981  191683  190707  191579  190615   innodb-5626
6143   13051    27216   57447  108786  145127  169957  192548  204655  205408  205278  205629  206721   innodb-5710
5157    9009    17931   42903   76633   95322  108896  114310  114650  113619  112220  108055  103181   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.7, InnoDB-5.6 >> MyRocks >> TokuDB
  • 16 to 40 clients - InnoDB-5.7, InnoDB-5.6 >> MyRocks >> TokuDB
  • 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB

Read-write with --oltp-range-size=10000

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
 210     412      774    2091    4077    5288    5568    6409    6885    6235    6675    6543    5889   myrocks-5635
 306     612     1216    2403    4713    6439    7398    8098    8416    8397    8393    8364    8247   innodb-5626
 448     888     1758    3462    6699    8770    9724   10302   10482   10448   10330   10382   10360   innodb-5710
 360     722     1409    2713    5218    6932    7529    7851    7973    7954    7856    7822    7675   toku5717.none.100g

Throughput:
  • 1 to 8 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
  • 16 to 40 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
  • 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> TokuDB >> MyRocks

Read-only with --oltp-range-size=10

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
5659   12266    25778   61366  128290  175337  204504  230534  246007  262875  269596  272178  274313   myrocks-5635
6491   13838    30028   72355  144523  194622  230314  265632  298481  300703  302924  303685  305162   innodb-5626
6052   12700    27143   63307  129096  177797  209601  243368  273429  272255  272563  275147  276691   innodb-5710
5391   11261    23935   55313  113932  164953  195290  223643  249579  247840  246926  247897  247045   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
  • 16 to 40 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
  • 48+ clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB

Read-only with --oltp-range-size=100

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
4031    8429    17417   38579   82542  116461  134208  150897  165211  166491  164694  166504  166625   myrocks-5635
5375   11273    23474   54099  110142  151311  177268  202868  225283  228292  229257  229828  231068   innodb-5626
5383   11292    23629   54050  110015  152462  176974  201112  223618  223737  224161  226272  226484   innodb-5710
4804   10062    20795   47474   98569  138827  159757  180406  198294  198680  198103  198374  196718   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks
  • 16 to 40 clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks
  • 48+ clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks

Read-only with --oltp-range-size=10000

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
 161     325      634    1238    2438    3385    3807    4093    4300    4261    4239    4214    4135   myrocks-5635
 242     484      961    1900    3729    5099    5857    6439    6748    6663    6562    6589    6500   innodb-5626
 357     708     1407    2788    5354    6984    7689    8095    8246    8222    8145    8138    8071   innodb-5710
 301     606     1203    2365    4583    6057    6582    6885    6992    6908    6862    6802    6772   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
  • 16 to 40 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
  • 48+ clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks

Point-query

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
6784   14328    31277   80458  166882  222750  265885  306227  341095  354327  349912  345317  349403   myrocks-5635
7236   15236    33569   87991  177791  236463  278423  319764  362604  371093  374380  371092  375698   innodb-5626
6802   14177    30860   78950  163909  222998  265156  307198  345832  354835  362774  366825  367057   innodb-5710
6249   12951    27608   67805  142064  194320  230720  264171  294814  306298  308177  309542  310391   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.6 >> MyRocks >> InnoDB-5.7 >> TokuDB
  • 16 to 40 clients - InnoDB-5.6 >> MyRocks, InnoDB-5.7 >> TokuDB
  • 48+ clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB

Insert-only

The numbers in the table are the QPS for 1 to 128 threads.

   1       2        4       8      16      24      32      40      48      64      80      96     128   threads
5254    16950   31561   41074   47720   52063   54066   53899   53900   53725   53343   53098   52278   myrocks-5635
5753    11506   36083   57235   62591   62469   62577   61899   61131   60592   59080   56895   52913   innodb-5626
5291    15657   33358   55385   79656   90812   97735   99944  100714  101967  103374  104934  106194   innodb-5710
2975     5754    9033   17695   29039   37341   38410   35859   33792   30053   27362   25028   21275   tokudb-5717

Throughput:
  • 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
  • 16 to 40 clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB
  • 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB

3 comments:

  1. MyRocks is worst than innoDB in almost benchmarks or I'm dreaming?

    ReplyDelete
  2. I edited this because I used InnoDB from MySQL 5.6.26, not from 5.6.35.

    ReplyDelete