MySQL 8.4 Release Notes
InnoDB performs certain tasks in the
background, including flushing of dirty pages from the buffer
pool. Dirty pages are those that have been modified but are not
yet written to the data files on disk.
Buffer pool flushing is performed by page cleaner threads. The
number of page cleaner threads is controlled by the
innodb_page_cleaners variable,
which has a default value set to the same value as
innodb_buffer_pool_instances.
Buffer pool flushing is initiated when the percentage of dirty
pages reaches the low water mark value defined by the
innodb_max_dirty_pages_pct_lwm
variable. The default low water mark is 10% of buffer pool
pages. A
innodb_max_dirty_pages_pct_lwm
value of 0 disables this early flushing behaviour.
The purpose of the
innodb_max_dirty_pages_pct_lwm
threshold is to control the percentage dirty pages in the buffer
pool and to prevent the amount of dirty pages from reaching the
threshold defined by the
innodb_max_dirty_pages_pct
variable, which has a default value of 90.
InnoDB aggressively flushes buffer pool pages
if the percentage of dirty pages in the buffer pool reaches the
innodb_max_dirty_pages_pct
threshold.
When configuring
innodb_max_dirty_pages_pct_lwm,
the value should always be lower than the
innodb_max_dirty_pages_pct
value.
Additional variables permit fine-tuning of buffer pool flushing behavior:
The
innodb_flush_neighborsvariable defines whether flushing a page from the buffer pool also flushes other dirty pages in the same extent.The default setting of 0 disables
innodb_flush_neighbors. Dirty pages in the same extent are not flushed. This setting is recommended for non-rotational storage (SSD) devices where seek time is not a significant factor.A setting of 1 flushes contiguous dirty pages in the same extent.
A setting of 2 flushes dirty pages in the same extent.
When table data is stored on a traditional HDD storage device, flushing neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can disable this setting to spread out write operations.
The
innodb_lru_scan_depthvariable specifies, per buffer pool instance, how far down the buffer pool LRU list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed by a page cleaner thread once per second.A setting smaller than the default is generally suitable for most workloads. A value that is significantly higher than necessary may impact performance. Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
When tuning
innodb_lru_scan_depth, start with a low value and configure the setting upward with the goal of rarely seeing zero free pages. Also, consider adjustinginnodb_lru_scan_depthwhen changing the number of buffer pool instances, sinceinnodb_lru_scan_depth*innodb_buffer_pool_instancesdefines the amount of work performed by the page cleaner thread each second.
The innodb_flush_neighbors and
innodb_lru_scan_depth variables
are primarily intended for write-intensive workloads. With heavy
DML activity, flushing can fall behind if it is not aggressive
enough, or disk writes can saturate I/O capacity if flushing 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).
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
innodb_max_dirty_pages_pct
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.
The
innodb_adaptive_flushing_lwm
variable defines a low water mark for redo log capacity. When
that threshold is crossed, adaptive flushing is enabled, even
if the
innodb_adaptive_flushing
variable is disabled.
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. It gives the most benefit when
the redo log is in danger of filling up. If adaptive flushing
is not appropriate to the characteristics of your workload,
you can disable it. Adaptive flushing controlled by the
innodb_adaptive_flushing
variable, which is enabled by default.
innodb_flushing_avg_loops
defines the number of iterations that
InnoDB keeps the previously calculated
snapshot of the flushing state, controlling how quickly
adaptive flushing responds to foreground workload changes. A
high
innodb_flushing_avg_loops
value means that InnoDB keeps the
previously calculated snapshot longer, so adaptive flushing
responds more slowly. When setting a high value it is
important to ensure that redo log utilization does not reach
75% (the hardcoded limit at which asynchronous flushing
starts), and that the
innodb_max_dirty_pages_pct
threshold keeps the number of dirty pages to a level that is
appropriate for the workload.
Systems with consistent workloads, a large log file size
(innodb_log_file_size), and
small spikes that do not reach 75% 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, a smaller value allows flushing to closely track
workload changes, and helps to avoid reaching 75% log space
utilization.
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
innodb_io_capacity setting.
The innodb_io_capacity_max
value defines an upper limit on I/O capacity in such
situations, so that a spike in I/O activity does not consume
the entire I/O capacity of the server.
The innodb_io_capacity
setting is applicable to all buffer pool instances. When dirty
pages are flushed, I/O capacity is divided equally among
buffer pool instances.
The innodb_idle_flush_pct
variable limits the rate of buffer pool flushing during idle
periods, which are periods of time that database pages are not
modified. Its value is interpreted as a percentage of
innodb_io_capacity (which
defines the number of I/O operations per second available to
InnoDB). The default value is 100, or 100
percent of the value of
innodb_io_capacity. To limit
flushing during idle periods, set
innodb_idle_flush_pct to less than 100.
Limiting page flushing during idle periods can help extend the life of solid state storage devices. Side effects of limiting page flushing during idle periods may include a longer shutdown time following a lengthy idle period, and a longer recovery period should a server failure occur.