InnoDB performs certain tasks in the background, including
dirty pages (those pages
that have been changed but are not yet written to the database
files) from the buffer
pool, a task performed by the
Currently, InnoDB aggressively flushes buffer pool pages if the
percentage of dirty pages in the buffer pool exceeds
InnoDB uses a new algorithm to estimate the required rate of flushing, based on the speed of redo log generation and the current rate of flushing. The intent is to smooth overall performance by ensuring that buffer flush activity keeps up with the need to keep the buffer pool “clean”. Automatically adjusting the rate of flushing can help to avoid sudden dips in throughput, when excessive buffer pool flushing limits the I/O capacity available for ordinary read and write activity.
InnoDB uses its log files in a circular fashion. Before reusing a
portion of a log file, InnoDB flushes to disk all dirty buffer
pool pages whose redo entries are contained in that portion of the
log file, a process known as a
sharp checkpoint. If
a workload is write-intensive, it generates a lot of redo
information, all written to the log file. If all available space
in the log files is used up, a sharp checkpoint occurs, causing a
temporary reduction in throughput. This situation can happen even
is not reached.
InnoDB uses a heuristic-based algorithm to avoid such a scenario, by measuring the number of dirty pages in the buffer pool and the rate at which redo is being generated. Based on these numbers, InnoDB decides how many dirty pages to flush from the buffer pool each second. This self-adapting algorithm is able to deal with sudden changes in the workload.
Internal benchmarking has also shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.
Because adaptive flushing is a new feature that can significantly
affect the I/O pattern of a workload, a new configuration
parameter lets you turn off this feature. The default value of the
TRUE, enabling the new algorithm. You can set
the value of this parameter in the MySQL option file
my.ini) or change
it dynamically with the
SET GLOBAL command,
which requires the
For more information about InnoDB I/O performance, see
InnoDB Disk I/O.