14.13.1.6 Tuning InnoDB Buffer Pool Flushing

The configuration options
innodb_flush_neighbors and
innodb_lru_scan_depth let you
fine-tune certain aspects of the
flushing process for the
InnoDBbuffer pool. These
options primarily help write-intensive
workloads. With heavy
DML activity, flushing can fall
behind if it is not aggressive enough, resulting in excessive
memory use in the buffer pool; or, disk writes due to flushing
can saturate your I/O capacity if that mechanism is too
aggressive. The ideal settings depend on your workload, data
access patterns, and storage configuration (for example, whether
data is stored on HDD or
SSD devices).

If flushing activity falls far behind,
InnoDB can flush more aggressively than
specified by
innodb_io_capacity.
innodb_io_capacity_max
represents an upper limit on the I/O capacity used in such
emergency situations, so that the spike in I/O does not
consume all the capacity of the server.

Most of the options referenced above are most applicable to
servers that run write-heavy workloads for long periods of time
and have little reduced load time to catch up with changes
waiting to be written to disk.

innodb_flushing_avg_loops
defines the number of iterations for which
InnoDB keeps the previously calculated
snapshot of the flushing state, which controls how quickly
adaptive flushing responds to foreground load changes. Setting a
high value for
innodb_flushing_avg_loops means
that InnoDB keeps the previously calculated
snapshot longer, so adaptive flushing responds more slowly. A
high value also reduces positive feedback between foreground and
background work, but when setting a high value it is important
to ensure that InnoDB redo log utilization
does not reach 75% (the hardcoded limit at which async flushing
starts) and that the
innodb_max_dirty_pages_pct
setting keeps the number of dirty pages to a level that is
appropriate for the workload.

Systems with consistent workloads, a large
innodb_log_file_size, and small
spikes that do not reach 75% redo log space utilization should
use a high
innodb_flushing_avg_loops value
to keep flushing as smooth as possible. For systems with extreme
load spikes or log files that do not provide a lot of space,
consider a smaller
innodb_flushing_avg_loops
value. The smaller value will allow flushing to closely track
the load and help avoid reaching 75% redo log space utilization.