iiBench Contest Results

January 20, 2009

Author

kuszmaul

MySQL

Share this Post:

At OpenSQL Camp in November we presented a challenge to insert one billion rows, maintaining indexes, into a MySQL table. The best results we have seen are:

Who	Engine	Total Time	Terminal Rate	Notes
Mark Callaghan	InnoDB	106.2 hours	~1,800	Google Patches Tuned my.cnf parameters 10 disk SW RAID 0
Fake Amelia	MyISAM	31.5 hours	avg 8,800	400 Partitions
Devananda vdv	InnoDB	22 hours	10,000	128GB Memory*
Devananda vdv	InnoDB	10 hours	~16,000	128GB Memory* 4 parallel iiBench.exe

We intended iiBench to stress I/O bound indexed insertion workloads, and to produce useful indexes.

Mark Callaghan matched our intent the best, and his hardware configuration is close enough to the challenge that I’ll declare it
within bounds. Mark achieved the highest InnoDB performance.
Fake Amelia got the best performance on MyISAM, and although I am concerned that spreading indexes across 400 partitions may provide poor query performance, query performance wasn’t explicitly part of the challenge. Fake Amelia achieved the highest MyISAM performance.
Although Devananda employed of 128GB (well above the contest limit of 16GB), his results provide an interesting measure of what
happens when disk I/O is not the bottleneck.

We have received some excellent suggestions for how to improve iiBench. My two favorite suggestions are

to include deletions in the workload, and
to include periodic queries in the workload.

As of today, the iiBench tarball has been downloaded over 70 times. We hope you find this benchmark tool useful and we welcome suggestions for improvements.