Insert 1B rows into a table while maintaining indexes
At the OpenSQL camp in November 2008, we presented a challenge to insert 1B rows with indexes into a MySQL table. We also provided a C++ version of an open-source benchmark, iiBench, to measure performance. iiBench is designed to stress inserting a small number of rows at a time at a high rate, while keeping indexes up-to-date. Indexed insertions are valuable for applications with high incoming data rates and a need to query on new data without waiting for periodic batch loads. iiBench source code and the included iiBench Users Guide is freely available under GPLv3. We welcome contributions, questions and suggestions via e-mail to iiBench@tokutek.com.
The best results that we are aware of are shown in the table below.
iiBench Results – Insert 1B Rows
| Who | Engine | Total Time | Terminal Rate | Notes |
|---|---|---|---|---|
| Tokutek | TokuDB 1.1.2 | 15.7 hours | 15,332 | 16GB memory, 6 disk RAID0 |
| 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 |
* Contest rules limited physical memory to 16GB, but Devananda’s post has some interesting results.
