Part 4: sysbench, a large server and small database

This post has more results for the test described in my previous post. Here I share the impact from disabling the binlog and binlog crash safety.

I will repeat the disclaimer from my previous report. This test is done with a small and cached database that has 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:

• MyRocks gets up to ~1.5X more throughput with binlog crash safety disabled
• MyRocks gets up to ~3X more throughput with the binlog disabled
• InnoDB gets up to ~1.5X more throughput with the binlog disabled

Another way to explain this is that MyRocks loses more throughput than InnoDB with the binlog and binlog crash safety enabled. We have work in progress to fix that.

Disabling the binlog and binlog crash safety

I ran tests to understand the overhead from binlog crash safety and the binlog on MyRocks and InnoDB. I added skip_log_bin to the my.cnf files to disable the binlog. For MyRocks I also ran tests with the binlog enabled but binlog crash safety disabled by adding rocksdb_enable_2pc=0 to the my.cnf. When binlog crash safety is disabled then it is possible for the binlog and RocksDB to not recover to the same point in time.

The binaries tested are:

• myrocks-5635 - MyRocks from FB MySQL based on MySQL 5.6.35
• innodb-5626 - InnoDB from upstream MySQL 5.6.26
• innodb-5710 - InnoDB from upstream MySQL 5.7.10

For the results below I use base to mean the configuration with the binlog and binlog crash safety enabled. But fsync is disabled for the binlog and storage engine. I use 2pc0 for the MyRocks configuration with the binlog enabled but binlog crash safety disabled. I use nobinlog for the configurations with the binlog disabled.

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
- myrocks-5635
2895    10363   20118   34017   38720   42138   44736   44646   44169   43633   43104   42690   42442   base
2966    9527    18574   35367   47037   52671   57182   58950   59594   61062   61866   61695   62179   2pc0
3168    7282    25153   47635   72764   85850   95187  100680  104422  108939  112490  116855  121797   nobinlog
- innodb-5626
4029    8373    15522   24563   32448   37061   40251   42522   44219   44631   45123   45762   45808   base
4659    9443    17115   27844   39099   56685   68578   69653   68424   65519   63270   62603   61285   nobinlog
- innodb-5710
4254    8792    17020   30524   48443   60623   67052   68773   69232   70556   72134   72696   74222   base
4817   10133    19273   31253   58881   83443  100658  107117  108247  105198  108413  111960  114375   nobinlog

At medium and high concurrency:

• MyRocks gets ~1.5X more throughput with binlog crash-safety disabled
• MyRocks gets almost 3X more throughput with the binlog disabled
• InnoDB gets ~1.5X more throughput with the binlog disabled

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
- myrocks-5635
2901   10696    20983   37078   43756   47549   50627   52753   52318   51690   50853   50003   49074   base
2973    9835    19527   38746   53403   61243   67248   70194   72343   73541   75190   75752   76222   2pc0
3192    7003    25995   50212   79235   99068  112833  116602  121630  128182  132259  137753  142908   nobinlog
- innodb-5626
6384   12625    23847   40501   51505   53024   53587   53523   53316   52483   51632   51276   51117   base
7527   14588    28320   49654   72081   80207   79992   78593   75252   70561   67417   65425   63701   nobinlog
- innodb-5710
5618   11060    21361   38810   58197   69717   74286   75519   75545   76520   77335   78117   79152   base
3293    6992    25292   41675   69919   95015  112157  122645  117212  130212  121092  124406  132257   nobinlog

At medium and high concurrency:

• MyRocks gets ~1.5X more throughput with binlog crash-safety disabled
• MyRocks gets ~2.5X more throughput with the binlog disabled
• InnoDB gets ~1.5X more throughput with the binlog disabled

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
- myrocks-5635
5254    16950   31561   41074   47720   52063   54066   53899   53900   53725   53343   53098   52278   base
5450    16021   30189   47647   63500   69840   75002   76964   78653   80942   82146   82982   83690   2pc0
6111    22188   43235   75194  106476  124108  134373  140854  143762  153497  155575  158598  164615   nobinlog
- innodb-5626
5753    11506   36083   57235   62591   62469   62577   61899   61131   60592   59080   56895   52913   base
6554    13293   28378   75689   89756   86628   83106   79377   76966   71510   66678   61638   52642   nobinlog
- innodb-5710
5291    15657   33358   55385   79656   90812   97735   99944  100714  101967  103374  104934  106194   base
6112    12531   26378   61395  113139  132083  133795  130147  125948  121881  119478  118145  114600   nobinlog

At medium and high concurrency:

• MyRocks gets ~1.5X more throughput with binlog crash-safety disabled
• MyRocks gets almost 3X more throughput with the binlog disabled
• InnoDB gets ~1.25X more throughput with the binlog disabled at medium concurrency. The benefit is ~1.1X at high concurrency.