Configuring InnoDB Buffer Pool

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 pool.

innodb 在后臺執行一些任務，包括從緩沖池刷臟頁（這些頁面已經被改變但是還沒有寫入數據庫文件）

InnoDB starts flushing buffer pool pages when the percentage of dirty pages in the buffer pool reaches the low water mark setting defined byinnodb_max_dirty_pages_pct_lwm. This option is intended to control the ratio of dirty pages in the buffer pool and ideally prevent the percentage of dirty pages from reaching innodb_max_dirty_pages_pct. If the percentage of dirty pages in the buffer pool exceedsinnodb_max_dirty_pages_pct, InnoDB begins to aggressively flush buffer pool pages.

當緩沖池中的臟頁百分比達到innodb_max_dirty_pages_pct_lwm設置的低水位線時，開始刷臟頁。這個選項旨在控制緩沖池中的臟頁比率，并且理想地組織臟頁百分比達到innodb_max_dirty_pages_pct設置的值。如果緩沖池中的臟頁比率超過了innodb——max_dirty_pages_pct, innodb開始積極的刷緩沖頁。

InnoDB uses an algorithm to estimate the required 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 buffer flush activity keeps up with the need to keep the buffer pool “clean”. Automatically adjusting the rate of flushing can help to avoid sudden dips in throughput, when excessive buffer pool flushing limits the I/O capacity available for ordinary read and write activity.

Innodb 使用算法估計需要的刷新速率，根據事務日志的生成速度和當前的刷新速率。目的是在保證緩沖池‘干凈的前提下’ 盡可能的平滑整體性能。自動調整沖洗速率有助于避免過多的緩沖池沖洗限制了可用于普通讀寫活動的I / O容量，從而避免吞吐量突然下降。

InnoDB uses its log files in a circular fashion. Before reusing a portion of a log file, InnoDB flushes to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file, a process known as a sharp checkpoint. If a workload is write-intensive, it generates a lot of redo information, all written to the log file. If all available space in the log files is used up, a sharp checkpoint occurs, causing a temporary reduction in throughput. This situation can happen even if innodb_max_dirty_pages_pct is not reached.

innodb 用循環的方式使用日志文件。再重用這部分日志文件之前，會使用 sharp checkpoint 線程 將包含那部分redo 條目的臟緩沖頁刷到磁盤上。如果程序寫密集型，會產生很多的redo信息。如果日志文件的可用空間被用光了，則會出現尖銳檢查點，造成吞吐量暫時降低。即使還未達到innodb_max_dirty_pages_pct,這種情況也會發生。

InnoDB uses a heuristic-based algorithm to avoid such a scenario, by measuring the number of dirty pages in the buffer pool and the rate at which redo is being generated. Based on these numbers, InnoDB decides how many dirty pages to flush from the buffer pool each second. This self-adapting algorithm is able to deal with sudden changes in workload.

InnoDB使用基于啟發式的算法來避免這種情況，通過測量緩沖池中的臟頁數和生成redo的速率。 根據這些數據，InnoDB決定每秒從緩沖池中刷新多少個臟頁面。 這種自適應算法能夠處理工作量的突然變化。

Internal benchmarking has shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.

內部基準測試顯示，該算法不僅隨著時間可以維持吞吐量，而且還能夠顯著提高總吞吐量。

Because adaptive flushing can significantly affect the I/O pattern of a workload, the innodb_adaptive_flushing configuration parameter lets you turn off this feature. The default value for innodb_adaptive_flushing is ON, enabling the adaptive flushing algorithm. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPERprivilege.

因為自適應刷新可能會顯著影響工作負載的I / O模式，所以innodb_adaptive_flushing配置參數可讓你關閉此功能。默認值是on，你可以再配置文件中設置該參數或者使用set global 命令動態設置，需要super權限

For information about fine-tuning InnoDB buffer pool flushing behavior, see Section 14.6.3.7, “Fine-tuning InnoDB Buffer Pool Flushing”.

For more information about InnoDB I/O performance, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.