InnoDB on IO bound workloads

I ran the iibench test using a server with 2 CPU cores, 2 disks in SW RAID 0 and 1 MB stripe, 2G RAM and XFS. If you just want a summary, it is that software changes can make InnoDB run much faster on the same hardware. There is a lot of opportunity -- but certainly not enough to catch TokuDB.

The binaries tested are:

• XtraDB 1.0.3-5
  
• InnoDB with MySQL 5.0.77
• v3 Google patch dated April 12

There were two tests:

1. Time to insert 50M rows into an empty table
2. Time to insert several million rows into a table with 50M rows

I disabled the innodb doublewrite buffer for all tests as I want to compare the results to a server that doesn't use that level of safety.

The my.cnf parameters for 5.0.77 are:

innodb_buffer_pool_size=1G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=20
innodb_doublewrite=0

 The my.cnf parameters for the v3 Google patch are:

innodb_buffer_pool_size=1G
innodb_log_file_size=1900M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=20
innodb_ibuf_max_pct_of_buffer=10
innodb_ibuf_reads_sync=1
innodb_doublewrite=0

The my.cnf parameters for XtraDB are:

innodb_log_file_size=1900M
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=250
innodb_use_sys_malloc=0
innodb_read_io_threads=2
innodb_write_io_threads=2
innodb_max_dirty_pages_pct=20
innodb_ibuf_max_size=100M
innodb_ibuf_active_contract=1
innodb_ibuf_accel_rate=200
innodb_doublewrite=0

All performance results are anonymous for binaries X, Y and Z. Maybe I can monetize my performance testing effort by doing this. I probably need a new disk soon.

The first result is the time to insert 50m rows into an empty table measured in seconds. The difference is not that signficant. However, the results can be misleading. 5.0.77 has much more pending work at test end (dirty pages and insert buffer entries). That also made 5.0.77 much slower near the end of the test, but I will save the graphs for the next set of results. The test is run using the run_ib found in the link for iibench at the start of this page. The command line is:

bash run.sh 1 $path no root pw test no innodb 50000000 innodb yes yes $binary

And the results are:

• 24478 seconds -- binary X
  
• 21016 seconds -- binary Y
• 37146 seconds -- binary Z
  

The second result is the time to insert 3,380,000 rows into a table that starts with 50m rows on a cold server (no entries in the insert buffer, no dirty pages, server restarted). Queries are continuously run by 4 threads concurrent with the inserts. The test is run using run_ib from the iibench link at the top of this page. The command line is:

bash run.sh 1 $path no root pw test no innodb 10000000 innodb no no $binary

And the results are:

• 38673 seconds -- binary X
• 11143 seconds -- binary Y
• 21018 seconds -- binary Z
  

Finally, the graph for row insert rate over time. Note that the graphs for binaries Y and Z don't extend to the right because they inserted the 3.3M rows much faster.