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.
OK. If we go to the MySQL Conference, our prize should be an explanation of cache oblivious algorithmics.