Something awesome in InnoDB -- the insert buffer

The InnoDB insert buffer significantly reduces the disk IO required to support a change intensive workload when the database does not fit in the buffer pool. Eventually I must begin calling it the change buffer as it does more in MySQL 5.5.

The insert buffer reduces the disk IO required to maintain secondary indexes. For many servers that I care about it reduces secondary index maintenance IO by a factor of 2 to 5 -- something between 2:1 and 5:1. Someone reported a server today where the savings was 16:1. On the benchmark I describe in this note, the insert buffer makes InnoDB 36X faster and that rate is likely to approach 80X faster if I let the test run to completion. I think it can do even better with smarter management of the LRU.

Benchmarks provide a simple way to demonstrate the performance. I ran the Insert Benchmark on two servers with InnoDB. Both servers used a 16G buffer pool and loaded 1B rows using iibench.py and one connection. Each server can do ~1600 reads/second across 8 SAS disks and ~200 reads/second to 1 disk.

With the insert buffer enabled the test completed in ~65 hours. The row insert rate over the last 1M rows was ~2500/second. The row insert rate over the 1M rows from 595M to 596M was ~4500.

Rows inserted per second with the insert buffer enabled

Rows inserted per second with the insert buffer enabled and log scale for the y-axis

With the insert buffer disabled the test is still running after 450 hours. It has inserted ~596M rows and the row insert rate over the last 1M rows was ~125/second. I expect the rate to drop to ~30/second at which point the insert buffer makes InnoDB 80X faster.

Rows inserted per second with the insert buffer disbled

Rows inserted per second with the insert buffer disbled and the log scale for the y-axis

The poor performance from InnoDB with the insert buffer disabled isn't a bug in InnoDB. Any database engine that does secondary index maintenance via update-in-place at statement completion will have similar performance. Alas, while you can repeat my results with Percona Server you cannot with official MySQL until feature request 59214 is fixed. The benefits from the insert buffer stop as soon as it gets full and for change intensive workloads it tends to stay full once it gets full because the background IO thread does not merge changes from it fast enough.

Rows inserted per second for both

Rows inserted per second for both and log scale for the y-axis