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 storage engine 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 generates a lot of redo
information (writes to the log file). In this case, it is possible
that all available space in the log files is used up, even though
innodb_max_dirty_pages_pct is not reached. This causes a sharp
checkpoint, causing a temporary reduction in throughput.
Beginning with release 1.0.4, InnoDB storage engine 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 decides 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 storage engine
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
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .