Insert benchmark on disks, part 2

I ran more insert benchmark tests for InnoDB on pure disk servers. The previous results with a lot more detail are here. My goal in this case was to use better configuration options for InnoDB on disk and to understand the impact of innodb_flush_neighbors. With the better settings InnoDB sustains a much higher insert rate.

The first problem in the previous tests was that I used a few settings that are better for flash than disk with InnoDB so I increased innodb_write_io_threads from 4 to 32, reduced innodb_io_capacity from 16000 to 3000 and decreased innodb_lru_scan_depth from 2000 to 500. I also changed innodb_flush_log_at_trx_commit from 1 to 2 which makes it a bit harder to compare the previous results to these. I am not going to repeat the tests and will accept some uncertainty.

The previous tests used innodb_flush_neighbors=0 and see the link above for details on it. For these tests I compared that with innodb_flush_neighbors set to 1 and 2. With the value of 1 or 2 InnoDB might write more pages back to disk but will do so using few disk seeks. This begins to look more like a proper in-memory DBMS that uses a redo log plus snapshots for persistence. While the writes for InnoDB are far from sequential the results here show the benefit of the options.

Conclusions:

• MongoDB writes much more to storage for the same workload than alternatives
• the insert rate for MongoDB is much worse than the alternatives. While bulk load isn't an everyday workload reload or load is frequently done. The partial workaround for MongoDB is to create secondary indexes after load but that is limited to 1 index create per database (there is no concurrency). This needs to be made much better.
• the insert rate for TokuMX doesn't degrade much and it writes much less to storage than the alternatives. This is very important for flash endurance.
• InnoDB is faster than TokuMX until the database is somewhere between 1X and 2X the size of RAM.
• Setting innodb_flush_neighbors to 1 or 2 on pure-disk servers might be useful
• Using more write_io_threads for a pure-disk server might help InnoDB

Configuration

The test servers had 8+ 10K RPM SAS disks, 72G of RAM and 16 CPU cores with HT enabled. The client was the Java insert benchmark client for TokuMX and MongoDB and the Python client for InnoDB. The test measured performance while loading up to 2B documents/rows at 100M document/row intervals. The configurations tested are listed below:

• inno-fn0 - MySQL 5.6.12, InnoDB, flush_neighbors=0, doublewrite=0, flush_write_at_trx_commit=2, write_io_threads=32
• inno-fn1 - MySQL 5.6.12, InnoDB, flush_neighbors=1, doublewrite=0, flush_write_at_trx_commit=2, write_io_threads=32
• inno-fn2 - MySQL 5.6.12, InnoDB, flush_neighbors=2, doublewrite=0, flush_write_at_trx_commit=2, write_io_threads=32
• inno-sync - MySQL 5.6.12, InnoDB, flush_neighbors=0, doublewrite=0, flush_write_at_trx_commit=1, write_io_threads=4
• toku-sync - TokuMX 1.4.0, logFlushPeriod=0
• mongo26-sync - MongoDB 2.6.0rc0, journalCommitInterval=2, powerOf2Sizes=1
• mongo24-sync - MongoDB 2.4.9, journalCommitInterval=2, powerOf2Sizes=0

Legend

• DB-size - database size at the end of the measurement interval
• Bytes-per-doc - DB-size divided by number of documents (or rows)
• Write-rate - rate for writing to storage measured by iostat
• Bytes-written - total bytes written to storage measured by iostat
• Test-secs - duration for test
• Inserts/sec - rate of documents/rows inserted per second

Results @100M rows

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

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
16.0 GB    171          20.3 MB/s     76 GB         3987      25080       inno-fn0
 X   GB      X          20.7 MB/s     76 GB         3946      25339       inno-fn1
 X   GB      X          22.7 MB/s     76 GB         3578      27947       inno-fn2
16.0 GB    171          28.9 MB/s    124 GB         4290      23308       inno-sync
 9.2 GB     98          11.1 MB/s     79 GB         7127      14030       toku-sync
42.0 GB    450          47.9 MB/s   1446 GB        30199       3311       mongo26-sync
43.0 GB    461          46.4 MB/s   1539 GB        33230       3009       mongo24-sync

Results @200M rows

The table has results after inserting 100M documents/rows to end with 200M documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 31 GB     166           28.1 MB/s    111 GB        4238      23598       inno-fn0
  X GB       X           28.8 MB/s    111 GB        4141      24151       inno-fn1
  X GB       X           31.5 MB/s    112 GB        3779      26462       inno-fn2
 31 GB     166           24.3 MB/s    238 GB        9781      10224       inno-sync
 17 GB      91           12.3 MB/s     90 GB        7328      13646       toku-sync
 76 GB     408           39.3 MB/s   1611 GB       40992       2439       mongo26-sync
 72 GB     386           37.4 MB/s   1768 GB       47329       2113       mongo24-sync

Results @300M rows

The table has results after inserting 100M documents/rows to end with 300M documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server

 46 GB     165           29.1 MB/s    141 GB        5183      19296       inno-fn0
  X GB       X           36.8 MB/s    132 GB        3835      26074       inno-fn1
  X GB       X           39.7 MB/s    131 GB        3517      28430       inno-fn2
 45 GB     161           21.7 MB/s    350 GB       16136       6198       inno-sync
 25 GB      89           12.0 MB/s     84 GB        7071      14142       toku-sync
108 GB     386           24.9 MB/s   1933 GB       77677       1287       mongo26-sync
 98 GB     350           30.7 MB/s   2008 GB       65514       1526       mongo24-sync

Results @400M rows

The table has results after inserting 100M documents/rows to end with 400M documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server

 61 GB     164           25.3 MB/s    167 GB        7102      14082       inno-fn0
  X GB       X           43.2 MB/s    154 GB        3808      26259       inno-fn1
  X GB       X           47.1 MB/s    154 GB        3426      29187       inno-fn2
 61 GB     164           21.1 MB/s    376 GB       17825       5610       inno-sync
 31 GB      83           12.1 MB/s     86 GB        7172      13941       toku-sync
130 GB     348           14.7 MB/s   2313 GB      157395        635       mongo24-sync

Results @500M rows

The table has results after inserting 100M documents/rows to end with 500M documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 75 GB     161           22.4 MB/s    181 GB        8626      11593       inno-fn0
  X GB       X           44.1 MB/s    173 GB        4205      23780       inno-fn1
  X GB       X           48.6 MB/s    166 GB        3616      27653       inno-fn2
 75 GB     161           19.8 MB/s    462 GB       23337       4285       inno-sync
 39 GB      83           11.1 MB/s     84 GB        7584      13186       toku-sync
160 GB     344            4.7 MB/s   2105 GB      441534        202       mongo24-sync

Results @600M rows

The table has results after inserting 100M documents/rows to end with 600M documents/rows in the db. The mongo24-sync binary was stopped at ~540M documents because it was too slow.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 89 GB     159           23.8 MB/s    173 GB        7782      12850       inno-fn0
  X GB       X           49.2 MB/s    180 GB        3906      25599       inno-fn1
  X GB       X           54.0 MB/s    180 GB        3553      28143       inno-fn2
 89 GB     159           20.1 MB/s    392 GB       19465       5137       inno-sync
 46 GB      82           11.6 MB/s     90 GB        7741      12917       toku-sync
168 GB     340            2.9 MB/s   1235 GB           X        123       mongo24-sync

Results @1B rows

The table has results after inserting 100M documents/rows to end with 1B documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 149 GB    160           15.3 MB/s    614 GB       42969       2327       inno-fn0
   X GB      X           29.1 MB/s    387 GB       14214       7035       inno-fn1
   X GB      X           33.8 MB/s    385 GB       12135       8241       inno-fn2
 148 GB    158           15.4 MB/s   1515 GB       98413       1016       inno-sync
  74 GB     79           10.9 MB/s     92 GB        8436      11853       toku-sync

Results @1.5B rows

The table has results after inserting 100M documents/rows to end with 1.5B documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 223 GB    160           13.3 MB/s    634 GB       51020       1960       inno-fn0
   X GB      X           15.9 MB/s    570 GB       38565       2593       inno-fn1
   X GB      X           20.8 MB/s    576 GB       29444       3396       inno-fn2
 221 GB    158           12.5 MB/s   1745 GB      140274        713       inno-sync
 104 GB     74           11.0 MB/s     96 GB        8722      11464       toku-sync   

Results @1.6B rows

The table has results after inserting 100M documents/rows to end with 1.6B documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 283 GB    160           13.0 MB/s    644 GB       53109       1883       inno-fn0
   X GB      X           15.0 MB/s    583 GB       41575       2405       inno-fn1
   X GB      X           20.5 MB/s    588 GB       30571       3271       inno-fn2

Results @2B rows

The table has results after inserting 100M documents/rows to end with 2B documents/rows in the db.

DB-size  Bytes-per-doc Write-rate  Bytes-written  Test-secs  Inserts/sec Server
 296 GB    159           13.4 MB/s    623 GB       49683       2013       inno-fn1
 296 GB    159           18.4 MB/s    625 GB       36144       2767       inno-fn2
 142 GB     76           12.6 MB/s     99 GB        7868      12709       toku-sync