14.13.1.2 Configuring the Rate of InnoDB Buffer Pool Flushing

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.
InnoDB flushes buffer pool pages if the percentage of dirty
pages in the buffer pool exceeds
innodb_max_dirty_pages_pct. As
of MySQL 5.7.5, InnoDB flushes buffer pool pages if the
percentage of dirty pages in the buffer pool is greater than or
equal to
innodb_max_dirty_pages_pct
(Bug#13029450).

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
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 though
innodb_max_dirty_pages_pct 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 can significantly affect the I/O
pattern of a workload, the
innodb_adaptive_flushing
configuration parameter lets you turn off this feature. The
default value for
innodb_adaptive_flushing
is TRUE, enabling the adaptive flushing
algorithm. You can set the value of this parameter in the MySQL
option file (my.cnf or
my.ini) or change it dynamically with the
SET GLOBAL command, which requires the
SUPER privilege.