iiBench Contest Updates

We re-ran iiBench based on Mark Callaghan’s excellent work. We used standard InnoDB engine in MySQL 5.1 without the Google or Percona patches. Our hardware is similar to Mark’s except we used a 6-disk hardware RAID 0, whereas Mark employed a 10-disk software RAID 0. We ran on the same hardware as we did before, a Sunfire x4150 with 8 cores and 16GB of memory, and we made the following changes compared to our original run:

• 6 SATA disk HW RAID 0 (original run on 6 disk HW RAID 5)
• 1,000 rows inserted per SQL INSERT statement
• Used my.cnf settings based on what Mark did and what Percona suggested.
  
  innodb_file_per_table=1 innodb_log_buffer_size=4M innodb_thread_concurrency=8 innodb_log_files_in_group=3 innodb_log_file_size=1300M innodb_flush_log_at_trx_commit=2 innodb_doublewrite=0 innodb_buffer_pool_size=12G innodb_max_dirty_pages_pct=90

  1 2 3 4 5 6 7 8 9

  innodb_file_per_table=1 innodb_log_buffer_size=4M innodb_thread_concurrency=8 innodb_log_files_in_group=3 innodb_log_file_size=1300M innodb_flush_log_at_trx_commit=2 innodb_doublewrite=0 innodb_buffer_pool_size=12G innodb_max_dirty_pages_pct=90

• We didn’t set innodb_flush_method=O_DIRECT (it was set in the original run).

These changes provided significant improvements, especially at the start of the run. Initial rates were in the 30,000-40,000 rows per
second range vs. about 5,000-8,000 seen in our initial run. Elapsed time to insert 1 billion rows improved from 19.6 days to 8.6 days, a
2.3x improvement. The terminal rate for the final 10 million rows improved from about 400 rows per second to about 875 rows per second.

Mark reported an elapsed time of 4.4 days with a terminal rate of 1,800 rows/second, which is about 2 times better than our results.

How much of that improvement is due to having 6 disks rather than 10? To estimate the difference I’ll assume that the workload is I/O bound and apply some hand waving. I’ll assume linear scaling with the number of disks. Thus extrapolating down from Mark’s 1,800 rows per row, by a factor of 6/10 (we had 6 disks instead of 10) would yield 1,080 rows per second. The rest of the difference from our measured 875 rows per second may be due to the Percona and Google patches. If we get a chance, we’ll run it again with the patches.

We moved our deadline for contest submissions out a week to 01/09/09. Drop us an e-mail at [email protected] with a summary of your test HW, MySQL version and patches, my.cnf parameters, and any changes made to the iiBench schema if you would like to share your results, or post your results in a blog.