Insert benchmark for flash, part 2

I repeated a few of the long running tests for the insert benchmark using flash storage. My goals were to test at least one new configuration, repeat a few tests to confirm the configuration was what I claimed it was and to confirm the impact of doing fsync-on-commit during this test. In this test the write operation adds 1000 small documents and the redo log write is not small. From casual observation I did not see a big impact from doing fsync-on-commit (or a big benefit from not doing it) but that is the point of this post. From the results here the impact from fsync-on-commit still appears to be minor. But remember that this is specific to one workload for which many KB of data are written to the redo log or journal per commit and for which the eventual bottleneck is random writes (for MongoDB and InnoDB).

Conclusions:

• the insert rate for MongoDB is about 10X worse than for TokuMX & InnoDB once the database becomes larger than memory and this is on a server with fast flash storage. This makes database reload a challenge especially for big data (can't believe I used those words). A partial workaround is to create the secondary indexes after the load but foreground index creation locks the database for long periods of time so you can't concurrently create indexes for a collection or for different collections in the same database and background index creation is really slow as the IO pattern from it is similar to incremental index maintenance (lots of random IO).
• at test end MongoDB is writing about 20X more bytes to storage versus TokuMX and 4X more versus InnoDB. This is not good for flash endurance.
• InnoDB was faster than TokuMX until the database reached 3B documents/rows.
• fsync-on-commit was at most 10% faster than lazy sync for InnoDB. I wonder whether I have the results labeled wrong and lazy sync was 10% faster. Regardless that isn't a big difference for me on this test and perhaps there were other factors (flaky HW, etc).
• fsync-on-commit makes little difference for TokuMX
• there is much more variance in the MongoDB results. Running with the journal disabled had a much higher insert rate until the database became large. That server eventually reported memory errors so I am not sure if the eventual decline in performance is at all due to flaky HW. But all MongoDB results declined over time, so I don't think the results are out of line for MongoDB. The insert rate for fsync-on-commit wasn't that different from lazy sync.

Configuration

Given that I recently wrote a long post about this I can skip to the results here. I tested several configurations listed below while inserting up to 3B documents into an empty database. Measurements were taken every 100M inserts. I report them after 100M, 500M, 1B, 1.5B, 2B, 2.5B and 3B inserts. Some tests were stopped before reaching 3B documents because they were too slow.

• inno-8k-dw0-sync - MySQL 5.6.12 with InnoDB, 8k pages, doublewrite buffer disabled and fsync-on-commit via innodb_flush_log_at_trx_commit=1
• inno-8k-dw0-lazy - MySQL 5.6.12 with InnoDB, 8k pages, doublewrite buffer disabled and fsync about once per second via innodb_flush_log_at_trx_commit=2
• toku-sync - TokuMX 1.4.0 and fsync-on-commit with logFlushPeriod=0 and writes done with w:1,j:1
• toku-lazy - TokuMX 1.4.0 and fsync ~3 times per second with logFlushPeriod=300 and writes done with w:1,j:0
• mongo-sync - MongoDB 2.6.0rc2 and fsync-on-commit with journalCommitInterval=2 and writes done with w:1,j:1. Also used powerOf2Sizes=1.
• mongo-lazy - MongoDB 2.4.9 and fsync ~3 times per second with journalCommitInterval=300 and writes done with w:1,j:0. Also used powerOf2Sizes=0.
• mongo-noj - MongoDB 2.6.0rc2 with nojournal=true and writes done with w:1,j:0. Also used powerOf2Sizes=1.
• mongo-p2n-sync - MongoDB 2.6.0rc?, journalCommitInterval=2, writes done with w:1,j:1, powerOf2Sizes=0
• mongo-p2y-sync - MongoDB 2.6.0rc?, journalCommitInterval=2, writes done with w:1,j:1, powerOf2Sizes=1

Results @100M rows

The table has results after inserting 100M documents/rows.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Writes/sec Server
16.0 GB    172          37.8 MB/s     107 GB         2991      34435     inno-8k-dw0-sync

16.0 GB    172          34.0 MB/s     118 GB         3460      28905     inno-8k-dw0-lazy
 9.4 GB    101          15.2 MB/s      98 GB         6489      15409     toku-sync
 9.3 GB    100          18.5 MB/s      95 GB         5140      19454     toku-lazy
40.0 GB    429         178.6 MB/s    1788 GB        10021       9979     mongo-sync
42.0 GB    451         170.0 MB/s    1823 GB        10730       9319     mongo-lazy
41.0 GB    440         241.5 MB/s     836 GB         3463      28872     mongo-noj
 X   GB      X         151.4 MB/s    1886 GB        12469       8019     mongo-p2n-sync
40   GB    429         128.9 MB/s    1946 GB        15116       6615     mongo-p2y-sync

Results @500M rows

The table has results after inserting 100M documents to reach 500M documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
 76 GB     163          97.0 MB/s     277 GB        3007      33253     inno-8k-dw0-sync

 76 GB     163          90.2 MB/s     300 GB        3333      30005     inno-8k-dw0-lazy
 40 GB      86          14.5 MB/s      99 GB        6863      14570     toku-sync
 40 GB      86          14.7 MB/s      98 GB        6686      14956     toku-lazy
170 GB     365         196.6 MB/s    2493 GB       12696       7876     mongo-sync
154 GB     331         181.9 MB/s    2511 GB       13820       7235     mongo-lazy
170 GB     365         367.6 MB/s    1967 GB        5352      18672     mongo-noj
  X GB       X         158.9 MB/s    2523 GB       15893       6292     mongo-p2n-sync
170 GB     365         133.3 MB/s    2517 GB       18912       5288     mongo-p2y-sync

Results @1B rows

The table has results after inserting 100M documents to reach 1B documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
150 GB     161         130.4 MB/s     399 GB        3220      31057     inno-8k-dw0-sync
150 GB     161         122.7 MB/s     429 GB        3504      28540     inno-8k-dw0-lazy
 76 GB      82          14.5 MB/s     107 GB        7398      13516     toku-sync
 77 GB      83          14.5 MB/s     105 GB        7247      13798     toku-lazy
326 GB     350          73.3 MB/s    2540 GB       34666       2885     mongo-sync
298 GB     319         101.4 MB/s    2542 GB       25097       3984     mongo-lazy
326 GB     350          73.3 MB/s    2116 GB       28915       3458     mongo-noj
  X          X          90.2 MB/s    2545 GB       28251       3540     mongo-p2n-sync
326 GB     350          79.3 MB/s    2536 GB       32027       3122     mongo-p2y-sync

Results @1.5B rows

The table has results after inserting 100M documents to reach 1.5B documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
225 GB     161         150.7 MB/s     494 GB        3448      29006     inno-8k-dw0-sync
224 GB     160         145.2 MB/s     527 GB        3627      27570     inno-8k-dw0-lazy
108 GB      77          14.4 MB/s     107 GB        7423      13470     toku-sync
108 GB      77          14.5 MB/s     108 GB        7529      13281     toku-lazy
482 GB     345          29.0 MB/s    2525 GB       87235       1146     mongo-sync
438 GB     314          62.3 MB/s    2529 GB       40617       2462     mongo-lazy
482 GB     345          29.4 MB/s    2110 GB       71879       1391     mongo-noj
  X GB       X          56.9 MB/s    2529 GB       44531       2246     mongo-p2n-sync
482 GB     345          51.1 MB/s    2519 GB       49331       2027     mongo-p2y-sync

Results @2B rows

The table has results after inserting 100M documents to reach 2B documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
298 GB     159         145.8 MB/s     606 GB        4369      22889     inno-8k-dw0-sync
298 GB     159         139.9 MB/s     643 GB        4601      21737     inno-8k-dw0-lazy
146 GB      78          15.5 MB/s     105 GB        7698      14709     toku-sync
146 GB      78          15.8 MB/s     105 GB        6696      14933     toku-lazy
640 GB     344          20.6 MB/s    2524 GB      122719        815     mongo-sync
640 GB     344          21.2 MB/s    2125 GB      100248        998     mongo-noj
  X GB       X          47.4 MB/s    2528 GB       53241       1872     mongo-p2n-sync
640 GB     344          43.9 MB/s    2520 GB       57488       1739     mongo-p2y-sync

Results @2.5B rows

The table has results after inserting 100M documents to reach 2.5B documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
372 GB     159         125.8 MB/s     679 GB        5684      17594     inno-8k-dw0-sync
372 GB     159         116.7 MB/s     728 GB        6240      16025     inno-8k-dw0-lazy
181 GB      78          16.8 MB/s     114 GB        6812      14678     toku-sync
181 GB      78          17.1 MB/s     114 GB        6682      14965     toku-lazy
  X GB       X          43.7 MB/s    2575 GB       59069       1693     mongo-p2n-sync
803 GB     345          45.5 MB/s    2567 GB       56481       1771     mongo-p2y-sync

Results @3B rows

The table has results after inserting 100M documents to reach 3B documents.

DB-size  Bytes-per-doc Write-rate  Bytes-written Test-secs  Writes/sec Server
448 GB     160         112.4 MB/s     736 GB        6895      14504     inno-8k-dw0-sync
447 GB     160         103.0 MB/s     783 GB        7605      13149     inno-8k-dw0-lazy
207 GB      74          16.5 MB/s     114 GB        6931      14428     toku-sync
207 GB      74          16.4 MB/s     111 GB        6785      14737     toku-lazy
850 GB     304          40.7 MB/s    2475 GB       60961       1640     mongo-p2n-sync
942 GB     337          43.8 MB/s    2467 GB       56403       1773     mongo-p2y-sync