InnoDB Website

The InnoDB Plugin has a new feature that can improve performance of OLTP when a table scan is taking place in the background, as might occur when exporting a table or creating an index while running normal operations. The new feature helps make the buffer cache somewhat resistant to such scans, by minimizing the number of buffer pages used to hold scanned-in pages. Because these pages are not likely to be used again, on-going transaction processing activities can make the best use of the remaining buffer cache pages.

The chart below shows the impact of this new capability:

In these read-only and read-write sysbench tests, a concurrent scan (in this case a SELECT without a WHERE clause) reduces throughput by about 24% and 42% respectively. When the buffer cache parameters are set appropriately, the loss in throughput is reduced to 13% and 34% in these cases. In other words, by adjusting the behavior of the buffer cache, we obtained about a 14% performance improvement over the default behavior.

To make the buffer cache less sensitive to the scan, you can set the percentage of the buffer cache to be used for “old blocks” using the parameter innodb_old_blocks_pct. You can also specify the time window during which a page can be accessed without being moved to the most-recently used end of the LRU list with the parameter innodb_old_blocks_time. The most appropriate settings of these parameters will vary widely based on your hardware and software configuration, your data, and the details of your workload. In this case, for the read-only test, the “old blocks percent” was set to 90% and the “old blocks time” was set to 2 seconds. For the read-write test, the parameters were set to 5% and 0.001 respectively.

Because the effect of these parameters is very sensitive to your particular workload and configuration, always benchmark before changing the settings of these parameters in any performance-critical or production environment. For more details amd guidelines on how to use this new feature and monitor its effect, please see the documentation.