InnoDB performs certain tasks in the
background, including flushing of dirty pages from the buffer
pool, a task performed by the master thread. Dirty pages are
those that have been modified but are not yet written to the
data files on disk.
flushes buffer pool pages if the percentage of dirty pages in
the buffer pool reaches the
InnoDB uses an adaptive flushing algorithm
to dynamically adjust the 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
flushing activity keeps pace with the current workload.
Automatically adjusting the flushing rate helps avoid sudden
dips in throughput that can occur when bursts of I/O activity
due to buffer pool flushing affects the I/O capacity available
for ordinary read and write activity.
Sharp checkpoints, which are typically associated with
write-intensive workloads that generate a lot of redo entries,
can cause a sudden change in throughput, for example. A sharp
checkpoint occurs when
InnoDB wants to
reuse a portion of a log file. Before doing so, all dirty
pages with redo entries in that portion of the log file must
be flushed. If log files become full, a sharp checkpoint
occurs, causing a temporary reduction in throughput. This
scenario can occur even if
threshold is not reached.
The adaptive flushing algorithm helps avoid such scenarios by tracking the number of dirty pages in the buffer pool and the rate at which redo log records are being generated. Based on this information, it decides how many dirty pages to flush from the buffer pool each second, which permits it to manage sudden changes in workload.
Internal benchmarking has shown that the algorithm not only
maintains throughput over time, but can also improve overall
throughput significantly. However, adaptive flushing can
affect the I/O pattern of a workload significantly and may not
be appropriate in all cases. If adaptive flushing is not
appropriate to the characteristics of your workload, you can
disable it. Adaptive flushing controlled by the
variable, which is enabled by default.
Be aware that if flushing falls behind, the rate of buffer
pool flushing can exceed the I/O capacity available to
InnoDB, as defined by
setting is applicable to all buffer pool instances. When dirty
pages are flushed, I/O capacity is divided equally among
buffer pool instances.