InnoDB performs certain tasks in the
background, including flushing
of 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
InnoDB aggressively flushes buffer pool pages
if the percentage of dirty pages in the buffer pool exceeds
InnoDB uses an 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
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
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
Internal benchmarking has shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.
Because adaptive flushing can significantly affect the I/O
pattern of a workload, the
configuration parameter lets you turn off this feature. The
default value for
ON, enabling the adaptive flushing algorithm.
You can set the value of this parameter in the MySQL option file
change it dynamically with the
GLOBAL statement, which requires privileges sufficient
to set global system variables. See
Section 220.127.116.11, “System Variable Privileges”.
For more information about
performance, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.