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 cache,
a task performed by the “master thread”. Currently,
the master thread aggressively flushes buffer pool pages if the
percentage of dirty pages in the buffer pool exceeds
This behavior can cause temporary reductions in throughput when excessive buffer pool flushing takes place, limiting the I/O capacity available for ordinary read and write activity. Beginning with release 1.0.4, InnoDB Plugin 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 of this change is to smooth overall performance, eliminating steep dips in throughput, by ensuring that buffer flush activity keeps up with the need to keep the buffer pool “clean”.
Remember that InnoDB uses its log files in a circular fashion.
To make a log file (or a portion of it) reusable, InnoDB must
flush to disk all dirty buffer pool pages whose redo entries are
contained in that portion of the log file. When required, InnoDB
performs a so-called “sharp checkpoint” by flushing
the appropriate dirty pages to make space available in the log
file. If a workload is write intensive, it will generate a lot of
redo information (writes to the log file). In this case, it is
possible that available space in the log files will be used up,
innodb_max_dirty_pages_pct is not reached. This will
cause a sharp checkpoint, causing a temporary reduction in
Beginning with release 1.0.4, InnoDB Plugin uses a new heuristic-based algorithm to avoid such a scenario. The heuristic is a function of the number of dirty pages in the buffer cache and the rate at which redo is being generated. Based on this heuristic, the master thread will decide how many dirty pages to flush from the buffer cache each second. This self adapting heuristic is able to deal with sudden changes in the workload.
The primary aim of this feature is to smooth out I/O activity, avoiding sudden dips in throughput when flushing activity becomes high. 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, the InnoDB Plugin
introduces a new configuration parameter that can be used to
disable this feature. The default value of the new boolean
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