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. 

## Limiting Buffer Flushing During Idle Periods 

As of MySQL 8.0.18, you can use the innodb_idle_flush_pct  variable to limit the rate of buffer pool flushing during idle periods, which are periods of time that database pages are not modified. The innodb_idle_flush_pct  value is a percentage of the innodb_io_capacity  setting, which defines the number of I/O operations per second available to InnoDB . The default innodb_idle_flush_pct value is 100, which is 100 percent of the innodb_io_capacity  setting. To limit flushing during idle periods, define an innodb_idle_flush_pct  value 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. 

## 15.8.3.6 Saving and Restoring the Buffer Pool State 

