標籤：

14.6.3 InnoDB Buffer Pool 配置

05-25
14.6.3 InnoDB Buffer Pool 配置

來自專欄 mysql 官方文檔翻譯

dev.mysql.com/doc/refma

14.6.3.1 InnoDB Buffer Pool

InnoDB維護一個稱為緩衝池的存儲區域，用於在內存中緩存數據和索引。 了解InnoDB緩衝池如何工作，並利用它來將頻繁訪問的數據保存在內存中，這是MySQL調優的一個重要方面。

您可以配置InnoDB緩衝池的各個方面以提高性能。

  • 理想情況下，您可以將緩衝池的大小設置為儘可能大的值，從而為伺服器上的其他進程留出足夠的內存以在沒有過多分頁的情況下運行。 緩衝池越大，InnoDB就越像內存資料庫，從磁碟讀取數據一次，然後在後續讀取期間從內存中訪問數據。
  • 對於具有大內存大小的64位系統，您可以將緩衝池分成多個部分，以最大限度地減少並發操作之間的內存結構爭用。
  • 儘管備份或報告等操作突然出現活動高峰，您仍可以將經常訪問的數據保留在內存中。
  • 您可以控制InnoDB何時以及如何執行預讀請求，以便預先將頁面非同步預取到緩衝池中，因為預計這些頁面將很快需要使用。
  • 您可以控制何時發生臟頁面的後台刷新，以及InnoDB是否根據工作負載動態調整刷新率。
  • 您可以微調InnoDB緩衝池刷新行為的各個方面，以提高性能。
  • 您可以配置InnoDB如何保留當前的緩衝池狀態，以避免伺服器重啟後冗長的預熱期。 您也可以在伺服器運行時保存當前的緩衝池狀態。

InnoDB Buffer Pool LRU 演算法

InnoDB使用最近最少使用（LRU）演算法的變體，將緩衝池作為列表來管理。 當需要將空間添加到池中時，InnoDB會清除最近最少使用的頁面，並將新頁面添加到列表的中間。 這種「中點插入策略」將列表視為兩個子列表：

  • 頭部是最近訪問的「new」（或「young」）頁面的子列表。
  • 尾部是最近訪問過的「舊」頁面的子列表。

該演算法保留了新子列表中查詢大量使用的頁面。 舊的子列表包含較少使用的頁面; 這些網頁是驅逐的候選人。

LRU演算法默認運行如下：

  • 3/8的緩衝池專用於舊的子列表。
  • 列表的中點是新子列表的尾部與舊子列表的頭部相交的邊界。
  • 當InnoDB將一個頁面讀入緩衝池時，它最初將它插入到中點（舊子列表的頭部）。 可以讀入頁面，因為它是用戶指定的操作（如SQL查詢）所必需的，或者作為InnoDB自動執行的預讀操作的一部分。
  • 在舊的子列表中訪問一個頁面會使其變得「young」，將其移動到緩衝池的頭部（新子列表的頭部）。 如果頁面因為需要而被讀入，則第一次訪問立即發生並且頁面變得年輕。 如果由於預讀而導致頁面被讀入，則第一次訪問不會立即發生（並且在頁面被逐出之前可能根本不會發生）。
  • 隨著資料庫的運行，緩衝池中的頁面不會通過移動到列表的尾部而被「訪問」。 新的和舊的子列表中的頁面隨著其他頁面的變化而變舊。 在中點插入頁面時，舊子列表中的頁面也會變老。 最終，一段長時間未使用的頁面會到達舊的子列表的尾部並被驅逐。

默認情況下，查詢讀取的頁面會立即移動到新的子列表中，這意味著它們會留在緩衝池中的時間更長。 表掃描（例如為mysqldump操作執行，或者不帶WHERE子句的SELECT語句）可以將大量數據帶入緩衝池並驅逐等量的舊數據，即使新數據再也不會使用。 同樣，由預讀後台線程載入，然後只訪問一次的頁面移動到新列表的頭部。 這些情況可能會將經常使用的頁面推送到舊的子列表中，他們可能會受到驅逐。

InnoDB Standard Monitor輸出在緩衝池和內存部分包含與緩衝池LRU演算法的操作相關的幾個欄位。

InnoDB緩衝池配置選項

幾個影響InnoDB緩衝池的不同方面的配置選項。

  • innodb_buffer_pool_size

指定緩衝池的大小。 如果緩衝池很小並且有足夠的內存，那麼通過減少查詢訪問InnoDB表所需的磁碟I / O量可以提高緩衝池的性能，從而提高性能。 innodb_buffer_pool_size選項是動態的，它允許您在不重新啟動伺服器的情況下配置緩衝池大小。

  • innodb_buffer_pool_chunk_size

定義InnoDB緩衝池調整大小操作的塊大小

  • innodb_buffer_pool_instances

    將緩衝池劃分為用戶指定數量的單獨區域，每個區域都有自己的LRU列表和相關數據結構，以減少並發內存讀取和寫入操作期間的爭用。 只有將innodb_buffer_pool_size設置為1GB或更大的值時，此選項才會生效。 您指定的總大小被分配到所有緩衝池中。 為了獲得最佳效率，請指定innodb_buffer_pool_instances和innodb_buffer_pool_size的組合，以便每個緩衝池實例至少為1 GB。 有關更多信息，請參見第14.6.3.3節「配置多個緩衝池實例」。

  • innodb_old_blocks_pct

    指定InnoDB用於舊塊子列表的緩衝池的近似百分比。 值的範圍是5到95.默認值是37（即池的3/8）。

  • innodb_old_blocks_time

指定插入到舊子列表中的頁面在第一次訪問後必須在毫秒（ms）內保留多長時間，然後才能移動到新子列表中。 如果值為0，則無論插入後何時發生訪問，插入舊子列表的頁面在第一次訪問時都會立即移至新子列表。 如果該值大於0，則頁面將保留在舊的子列表中，直到在第一次訪問後至少幾毫秒內發生訪問。 例如，值為1000（默認值）會導致頁面在第一次訪問後停留在舊子表中1秒鐘，然後才有資格移動到新子列表。

將innodb_old_blocks_time設置為大於0可防止一次性表掃描使用僅用於掃描的頁面填充新的子列表。 讀取的掃描頁面中的行會連續快速訪問多次，但該頁面在此之後未被使用。 如果innodb_old_blocks_time設置為大於處理頁面的時間的值，則該頁面保留在「舊」子列表中，並且老化到列表的尾部以被快速驅逐。 這樣，僅用於一次掃描的頁面不會影響新子列表中大量使用的頁面。

innodb_old_blocks_time可以在運行時設置，因此您可以在執行諸如表掃描和轉儲等操作時臨時更改它：

SET GLOBAL innodb_old_blocks_time = 1000; ... perform queries that scan tables ...

SET GLOBAL innodb_old_blocks_time = 0;

如果您的意圖是通過填充表格的內容來「緩衝」緩衝池，則此策略不適用。 例如，基準測試通常在伺服器啟動時執行表或索引掃描，因為在正常使用一段時間後，數據通常會在緩衝池中。 在這種情況下，請將innodb_old_blocks_time設置為0，至少在暖機階段完成之前。

  • innodb_read_ahead_threshold

控制InnoDB用於預取頁面到緩衝池中的線性預讀的靈敏度。

  • innodb_random_read_ahead

使用隨機預讀技術將頁面預取到緩衝池中。 隨機預讀是一項技術，可根據緩衝池中已有的頁面快速預測何時需要頁面，而不管這些頁面的讀取順序如何。 innodb_random_read_ahead默認是禁用的。

  • innodb_adaptive_flushing

指定是否根據工作負載動態調整緩衝池中刷新臟頁的速率。 動態調整沖刷速率旨在避免I / O活動的爆發。 該設置默認啟用。

  • innodb_flush_neighbors

指定是否從緩衝池中清除頁面也會在相同範圍內刷新其他臟頁面。

默認是1 啟用。

  • innodb_flushing_avg_loops

InnoDB保存之前計算的刷新狀態快照的迭代次數，控制自適應刷新對更改工作負載的響應速度。

默認是 30

  • innodb_lru_scan_depth

影響緩衝池刷新操作的演算法和啟發式的參數。 性能主要關注調整I / O密集型工作負載。 它為每個緩衝池實例指定了緩衝池LRU下多遠，page_cleaner線程掃描尋找要刷新的臟頁。

默認是1024

  • innodb_max_dirty_pages_pct

InnoDB會嘗試從緩衝池中刷新數據，以便臟頁面的百分比不超過此值。 指定範圍從0到99的整數。默認值為75。

  • innodb_max_dirty_pages_pct_lwm

低水位的百分比表示啟用來控制臟頁預flush的比率。 默認值為0將完全禁用預沖洗行為。

默認是0

  • innodb_buffer_pool_filename

指定保存由innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now生成的表空間ID和頁面ID列表的文件的名稱。

  • innodb_buffer_pool_dump_at_shutdown

指定在MySQL伺服器關閉時是否記錄緩衝池中緩存的頁面，以縮短下次重新啟動時的預熱過程。

默認是啟用

  • innodb_buffer_pool_load_at_startup

指定在MySQL伺服器啟動時，通過載入它在先前保存的相同頁面，緩衝池自動預熱。 通常與innodb_buffer_pool_dump_at_shutdown結合使用。

默認是on

  • innodb_buffer_pool_dump_now

立即記錄在緩衝池中緩存的頁面。

默認是OFF

  • innodb_buffer_pool_load_now

立即通過載入一組數據頁面來加熱緩衝池，而無需等待伺服器重新啟動。 在基準測試期間將高速緩存恢復到已知狀態，或者在運行查詢報告或維護後，使MySQL伺服器恢復正常工作負載，可能會很有用。 通常與innodb_buffer_pool_dump_now一起使用。

  • innodb_buffer_pool_dump_pct

指定每個緩衝池讀出和轉儲的最近使用頁面的百分比。 範圍是1到100。

默認是25

  • innodb_buffer_pool_load_abort

中斷由innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now觸發的緩衝池內容恢復過程。

默認是OFF

14.6.3.2 配置InnoDB buffer pool 大小

您可以在伺服器運行時將InnoDB緩衝池大小配置為離線（啟動時）或聯機。 本節中描述的行為適用於這兩種方法。

當增加或減少innodb_buffer_pool_size時，操作以塊為單位執行。 塊大小由innodb_buffer_pool_chunk_size配置選項定義，默認值為128M。

緩衝池大小必須始終等於innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍數。 如果將innodb_buffer_pool_size配置為不等於或等於innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍數的值，則緩衝池大小會自動調整為等於或等於innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍數的值，該值不小於指定的緩衝池大小。

在以下示例中，innodb_buffer_pool_size設置為8G，innodb_buffer_pool_instances設置為16. innodb_buffer_pool_chunk_size為默認值128M。

8G是一個有效的innodb_buffer_pool_size值，因為8G是innodb_buffer_pool_instances = 16 * innodb_buffer_pool_chunk_size = 128M的倍數，即2G。

在本例中，innodb_buffer_pool_size設置為9G，innodb_buffer_pool_instances設置為16. innodb_buffer_pool_chunk_size為128M，這是默認值。 在這種情況下，9G不是innodb_buffer_pool_instances = 16 * innodb_buffer_pool_chunk_size = 128M的倍數，所以innodb_buffer_pool_size被調整為10G，這是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的下一個不小於指定緩衝池大小的倍數。

配置 InnoBD buffer 池 Chunk 大小

innodb_buffer_pool_chunk_size可以以1MB（1048576位元組）為單位增加或減少，但只能在啟動時，命令行字元串或MySQL配置文件中修改。

命令行：

shell>

mysqld --innodb_buffer_pool_chunk_size=134217728

配置文件：

[mysqld]

innodb_buffer_pool_chunk_size=134217728

更改innodb_buffer_pool_chunk_size時，以下條件適用：

  • 如果在初始化緩衝池時，新的innodb_buffer_pool_chunk_size值* innodb_buffer_pool_instances大於當前的緩衝池大小，則innodb_buffer_pool_chunk_size將被截斷為innodb_buffer_pool_size / innodb_buffer_pool_instances。

例如，如果初始化緩衝池的大小為2GB（2147483648位元組），4個緩衝池實例和1GB的chunk大小（1073741824位元組），則會將chunk大小截斷為等於innodb_buffer_pool_size / innodb_buffer_pool_instances的值，如圖所示 下面：

shell> mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4 --innodb_buffer_pool_chunk_size=1073741824;

mysql> SELECT @@innodb_buffer_pool_size;

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

| 2147483648 |

+---------------------------+

mysql> SELECT @@innodb_buffer_pool_instances;

+--------------------------------+

| @@innodb_buffer_pool_instances |

+--------------------------------+

| 4 |

+--------------------------------+

# Chunk size was set to 1GB (1073741824 bytes) on startup but was # truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;

+---------------------------------+

| @@innodb_buffer_pool_chunk_size |

+---------------------------------+

| 536870912 |

+---------------------------------+

  • 緩衝池大小必須始終等於innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍數。 如果您更改了innodb_buffer_pool_chunk_size，則innodb_buffer_pool_size將自動調整為等於或等於innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值不小於當前緩衝池大小的值。 調整發生在緩衝池初始化時。

    更改innodb_buffer_pool_chunk_size時應該小心，因為更改此值可以增加緩衝池的大小，如上例所示。 在更改innodb_buffer_pool_chunk_size之前，請計算對innodb_buffer_pool_size的影響，以確保生成的緩衝池大小可以接受。

    注意：

    為避免潛在的性能問題，chunk數量（innodb_buffer_pool_size / innodb_buffer_pool_chunk_size）不應超過1000。

    --也就是說默認128M的chunk大小，buffer poo最大不要超過125G。

    在線 配置 InnoDB buffer pool 大小

    innodb_buffer_pool_size配置選項可以使用SET語句動態設置，允許您在不重新啟動伺服器的情況下調整緩衝池的大小。 例如：

    mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

    在調整緩衝池的大小之前，應該先完成通過InnoDB API執行的活動事務和操作。 啟動調整大小操作時，直到所有活動事務完成後才會啟動操作。 一旦調整大小操作正在進行中，需要訪問緩衝池的新事務和操作必須等到調整大小操作完成。 規則的例外是，當緩衝池被碎片整理時，允許對緩衝池的並發訪問，並且當緩衝池大小減小時撤回頁面。 允許並發訪問的一個缺點是它可能導致暫時缺少可用頁面而頁面正在被撤銷。

    注意：

    如果在緩衝池調整大小操作開始後啟動嵌套事務，則可能會失敗。

    監控在線緩衝池調整進度

    Innodb_buffer_pool_resize_status報告緩衝池調整進度。 例如：

    mysql> SHOW STATUS WHERE Variable_name=InnoDB_buffer_pool_resize_status;

    +----------------------------------+----------------------------------+

    | Variable_name | Value |

    +----------------------------------+----------------------------------+

    | Innodb_buffer_pool_resize_status | Resizing also other hash tables.

    | +----------------------------------+----------------------------------+

    緩衝池調整大小進度也會記錄在伺服器錯誤日誌中。

    在線緩衝池調整內部組件

    調整大小操作由後台線程執行。 當增加緩衝池的大小時，調整大小操作：

  • 以chunks的形式添加頁面（塊大小由innodb_buffer_pool_chunk_size定義）
  • Coverts哈希表，列表和指針在內存中使用新地址
  • 將新頁面添加到空閑列表中

當這些操作正在進行時，其他線程將被阻止訪問緩衝池。

減小緩衝池的大小時，調整大小操作：

  • 對緩衝池進行碎片整理並撤銷（釋放）頁面
  • 以chunks形式移除頁面（塊大小由innodb_buffer_pool_chunk_size定義）
  • 轉換哈希表，列表和指針以在內存中使用新地址

在這些操作中，只對緩衝池進行碎片整理和撤銷頁面，可以讓其他線程同時訪問緩衝池。

14.6.3.3 配置多個buffer pool 實例

對於緩衝池在數GB範圍內的系統，通過在不同線程讀取和寫入緩存頁面時減少爭用，將緩衝池劃分為不同的實例可以提高並發性。 此功能通常用於緩衝池大小在數GB範圍內的系統。 使用innodb_buffer_pool_instances配置選項配置多個緩衝池實例，並且您還可以調整innodb_buffer_pool_size值。

當InnoDB緩衝池很大時，通過從內存中檢索可以滿足許多數據請求。 您可能會遇到來自嘗試一次訪問緩衝池的多個線程的瓶頸。 您可以啟用多個緩衝池以最大限度地減少此爭用。 存儲在緩衝池中或從中讀取的每個頁面都使用散列函數隨機分配給其中一個緩衝池。 每個緩衝池管理自己的空閑列表，刷新列表，LRU以及連接到緩衝池的所有其他數據結構，並受其自己的緩衝池互斥鎖保護。

要啟用多個緩衝池實例，請將innodb_buffer_pool_instances配置選項設置為大於1（默認值）最大為64（最大值）的值。 只有當您將innodb_buffer_pool_size設置為1GB或更大的大小時，此選項才會生效。 您指定的總大小被分配到所有緩衝池中。 為了獲得最佳效率，請指定innodb_buffer_pool_instances和innodb_buffer_pool_size的組合，以便每個緩衝池實例至少為1GB。

14.6.3.4 使緩衝池 耐掃描

InnoDB並沒有採用嚴格的LRU演算法，而是採用了一種技術來最小化帶入緩衝池的數據量，並且再也無法訪問。 我們的目標是確保經常訪問（「hot」）的頁面保留在緩衝池中，即使預讀和全表掃描帶入可能或可能未被訪問的新塊。

新讀取的塊被插入到LRU列表的中間。 所有新讀取的頁面都插入到默認位置，距離LRU列表尾部3/8。 當頁面首次在緩衝池中訪問時，這些頁面將移動到列表的最前面（最近使用的一端）。 因此，永遠不會被訪問的頁面永遠不會將它放到LRU列表的前面，並且比使用嚴格的LRU方法更快地「老化」。 這種安排將LRU列表分成兩段，其中插入點下游的頁面被認為是「舊」的，並且是LRU理想驅逐者。

有關InnoDB緩衝池內部工作原理和LRU演算法細節的說明，請參見第14.6.3.1節「InnoDB緩衝池」。

您可以控制LRU列表中的插入點，並選擇InnoDB是否對通過表或索引掃描進入緩衝池的塊應用相同的優化。 配置參數innodb_old_blocks_pct控制LRU列表中「舊」塊的百分比。 innodb_old_blocks_pct的默認值是37，對應於3/8的原始固定比率。 值的範圍是5（緩衝池中的新頁面非常快速地老化）到95（只有5％的緩衝池用於熱頁面，使得演算法接近熟悉的LRU策略）。

保持緩衝池不被預讀激活的優化可以避免由表或索引掃描造成的類似問題。 在這些掃描中，數據頁面通常會連續快速訪問幾次，並且不會再次觸摸。 配置參數innodb_old_blocks_time指定第一次訪問頁面之後的時間窗口（以毫秒為單位），在此期間，可以在不移動到LRU列表的前端（最近使用的末尾）的情況下訪問它。 innodb_old_blocks_time的預設值是1000.增加此值會使越來越多的塊從緩衝池中快速老化。

innodb_old_blocks_pct和innodb_old_blocks_time都是動態的，全局的，可以在MySQL選項文件（my.cnf或my.ini）中指定，或者在運行時使用SET GLOBAL命令更改。 更改設置需要SUPER許可權。

為了幫助您評估設置這些參數的效果，SHOW ENGINE INNODB STATUS命令報告緩衝池統計信息。

由於這些參數的影響因硬體配置，數據以及工作負載的細節而異，所以它們的測試總是基準，以驗證在任何性能關鍵或生產環境中更改這些設置之前的有效性。

在混合工作負載中，大多數活動是OLTP類型，並且定期批量報告查詢會導致大掃描，因此在批處理運行期間設置innodb_old_blocks_time的值可以幫助保持緩衝池中正常工作負載的工作集。

掃描不能完全放入緩衝池的大型表時，將innodb_old_blocks_pct設置為較小的值會使僅讀取一次的數據佔用大量緩衝池。 例如，設置innodb_old_blocks_pct = 5會限制只讀取一次的數據到緩衝池的5％。

掃描適合內存的小表時，在緩衝池內移動頁面所需的開銷較小，因此您可以將innodb_old_blocks_pct保留為默認值，或者更高，例如innodb_old_blocks_pct = 50。

innodb_old_blocks_time參數的影響比innodb_old_blocks_pct參數更難預測，其相對較小，並且隨工作負載而變化更大。 為了達到最佳值，如果調整innodb_old_blocks_pct的性能提升不足，則應執行自己的基準測試。

14.6.3.5配置InnoDB緩衝池預取（預讀）

預讀請求是一個I/O請求，用於預先非同步預取緩衝池中的多個頁面，預計這些頁面很快就會被需要。 請求在一個範圍內引入所有頁面。 InnoDB使用兩種預讀演算法來提高I/O性能：

線性預讀是一項技術，可根據順序訪問的緩衝池中的頁面快速預測可能需要的頁面。 您可以使用配置參數innodb_read_ahead_threshold通過調整觸發非同步讀取請求所需的順序頁面訪問次數來控制InnoDB何時執行預讀操作。 在添加此參數之前，InnoDB只會計算在讀取當前範圍的最後一頁時，是否對下一個範圍發出非同步預取請求。

配置參數innodb_read_ahead_threshold控制InnoDB在檢測連續頁面訪問模式方面的敏感程度。 如果從某個範圍順序讀取的頁數大於或等於innodb_read_ahead_threshold，則InnoDB會啟動整個後續範圍的非同步預讀操作。 innodb_read_ahead_threshold可以設置為0-64之間的任何值。 預設值是56。值越高，訪問模式檢查越嚴格。 例如，如果將值設置為48，InnoDB只有在當前範圍中的48頁已被順序訪問時才會觸發線性預讀請求。 如果值為8，則即使連續訪問範圍內的最少8頁，InnoDB也會觸發非同步預讀。 您可以在MySQL配置文件中設置此參數的值，或者使用需要SUPER許可權的SET GLOBAL命令動態更改它。

隨機預讀是一項技術，可根據緩衝池中已有的頁面快速預測何時需要頁面，而不管這些頁面的讀取順序如何。 如果在緩衝池中找到來自相同範圍的連續13個頁面，則InnoDB會非同步發出一個請求以預取剩餘頁面的範圍。 要啟用此功能，請將配置變數innodb_random_read_ahead設置為ON。

SHOW ENGINE INNODB STATUS命令顯示統計信息，以幫助您評估預讀演算法的有效性。 統計數據包括以下全局狀態變數的計數器信息：

  • Innodb_buffer_pool_read_ahead
  • Innodb_buffer_pool_read_ahead_evicted
  • Innodb_buffer_pool_read_ahead_rnd

這些信息在微調innodb_random_read_ahead設置時非常有用。

14.6.3.6 配置InnoDB 緩存池刷新

InnoDB在後台執行某些任務，包括從緩衝池中清除臟頁（已更改但尚未寫入資料庫文件的那些頁）。

當緩衝池中臟頁的百分比達到innodb_max_dirty_pages_pct_lwm定義的低水位設置時，InnoDB開始刷新緩衝池頁。 此選項旨在控制緩衝池中的臟頁的比率，並理想地防止臟頁的百分比達到innodb_max_dirty_pages_pct。 如果緩衝池中臟頁的百分比超過innodb_max_dirty_pages_pct，InnoDB開始積極地刷新緩衝池頁面。

InnoDB使用一種演算法來根據重做日誌生成的速度和當前刷新率來估計所需的刷新率。 其目的是通過確保緩衝區刷新活動與保持緩衝池「清潔」的需要相一致來平滑整體性能。 自動調整刷新速率有助於避免吞吐量突然下降，因為過多的緩衝池刷新限制了普通讀寫活動可用的I / O容量。

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

內部基準測試表明，該演算法不僅可以保持一段時間的吞吐量，還可以顯著提高整體吞吐量。

由於自適應刷新可以顯著影響工作負載的I/O模式，因此可以使用innodb_adaptive_flushing配置參數關閉此功能。 innodb_adaptive_flushing的默認值為ON，啟用自適應刷新演算法。 您可以在MySQL選項文件（my.cnf或my.ini）中設置此參數的值，或者使用需要SUPER特權的SET GLOBAL命令動態更改它。

14.6.3.7 微調InnoDB緩衝池刷新

配置選項innodb_flush_neighbors和innodb_lru_scan_depth可讓您調整InnoDB緩衝池的刷新過程的各個方面。

  • innodb_flush_neighbors

指定是否從緩衝池中清除頁面也會在相同範圍內刷新其他臟頁面。 當表格數據存儲在傳統的HDD存儲設備上時，與在不同時間沖刷單獨頁面相比，在一個操作中沖刷相鄰頁面減少了I / O開銷（主要用於磁碟搜索操作）。 對於存儲在SSD上的表數據，尋道時間不是一個重要因素，您可以禁用此設置以分散寫入操作。

  • innodb_lru_scan_depth

為每個緩衝池實例指定緩衝池LRU列表多少，頁面清理器線程掃描尋找要刷新的臟頁面。 這是每秒執行一次的後台操作。

這些選項主要有助於編寫密集型工作負載。 如果DML活動繁重，如果不夠積極，刷新可能會落後，導致緩衝池中的內存使用過多; 或者如果這種機制過於激進，由於刷新導致的磁碟寫入可能會使您的I / O容量飽和。 理想的設置取決於您的工作負載，數據訪問模式和存儲配置（例如，數據是存儲在HDD還是SSD設備上）。

對於工作負載繁重或工作負載波動很大的系統，可以使用多個配置選項來微調InnoDB表的刷新行為：

  • innodb_adaptive_flushing_lwm
  • innodb_max_dirty_pages_pct_lwm
  • innodb_io_capacity_max
  • innodb_flushing_avg_loops

這些選項提供給innodb_adaptive_flushing選項使用的公式。

innodb_adaptive_flushing，innodb_io_capacity和innodb_max_dirty_pages_pct選項受以下選項的限制或擴展：

  • innodb_adaptive_flushing_lwm
  • innodb_io_capacity_max
  • innodb_max_dirty_pages_pct_lwm

InnoDB自適應沖洗機制並不適用於所有情況。 當重做日誌存在填滿危險時，它提供了最大的好處。 innodb_adaptive_flushing_lwm選項指定重做日誌容量的「低水位」百分比; 當超過該閾值時，即使未由innodb_adaptive_flushing選項指定，InnoDB也會啟用自適應刷新。

如果刷新活動遠遠落後，InnoDB可以比innodb_io_capacity指定的刷新更積極。 innodb_io_capacity_max表示在此類緊急情況下使用的I/O容量的上限，以便I/O中的峰值不會消耗伺服器的性能。

InnoDB會嘗試從緩衝池中清除數據，以使臟頁面的百分比不超過innodb_max_dirty_pages_pct的值。 innodb_max_dirty_pages_pct的默認值是75。

注意：

innodb_max_dirty_pages_pct設置為沖洗活動建立目標。 它不會影響沖洗的速度。

innodb_max_dirty_pages_pct_lwm選項指定一個「低水位」值，該值表示啟用預先刷新以控制臟頁比率並理想地防止臟頁百分比達到innodb_max_dirty_pages_pct的臟頁的百分比。 innodb_max_dirty_pages_pct_lwm = 0的值會禁用「預沖洗」行為。

上面提到的大多數選項最適用於長時間運行寫入繁重工作負載的伺服器，並且幾乎沒有減少載入時間以趕上等待寫入磁碟的更改。

innodb_flushing_avg_loops定義了InnoDB保留之前計算的刷新狀態快照的迭代次數，它控制自適應刷新響應前景負載變化的速度。為innodb_flushing_avg_loops設置一個較高的值意味著InnoDB保持之前計算的快照時間更長，所以自適應刷新響應速度更慢。較高的值也會減少前台和後台工作之間的正反饋，但設置較高值時，確保InnoDB重做日誌利用率未達到75％（非同步刷新開始的硬編碼限制）並且innodb_max_dirty_pages_pct設置保持不變將臟頁面數量設置為適合工作負載的級別。

具有一致工作負載，大型innodb_log_file_size和未達到75％重做日誌空間利用率的小型峰值的系統應該使用高innodb_flushing_avg_loops值來儘可能保持平滑。對於極端負載峰值或日誌文件不足以提供大量空間的系統，請考慮使用較小的innodb_flushing_avg_loops值。較小的值允許沖洗以緊密跟蹤負載，並有助於避免達到75％的重做日誌空間利用率。

14.6.3.8 保存和恢復緩衝池狀態

為了減少重新啟動伺服器後的預熱時間，InnoDB會在伺服器關閉時為每個緩衝池保存一定比例的最近使用的頁面，並在伺服器啟動時恢復這些頁面。 最近使用的頁面的百分比由innodb_buffer_pool_dump_pct配置選項定義。

在重新啟動繁忙的伺服器之後，通常會有一個預熱期，吞吐量穩步增加，因為緩衝池中的磁碟頁被帶回到內存中（因為查詢，更新了相同的數據）。 在啟動時恢復緩衝池的功能通過在重新啟動之前重新載入緩衝池中的磁碟頁而縮短了預熱期，而不是等待DML操作訪問相應的行。 此外，I/O請求可以大批量執行，從而使整體I / O更快。 頁面載入發生在後台，並且不會延遲資料庫啟動。

除了在關閉時保存緩衝池狀態並在啟動時恢復緩衝池狀態外，您還可以在伺服器運行時隨時保存和恢復緩衝池狀態。 例如，您可以在穩定的工作負載下達到穩定的吞吐量後保存緩衝池的狀態。 運行將數據頁面帶入緩衝池的報表或維護作業（僅針對這些操作請求）或運行其他非典型工作負載後，還可以恢復以前的緩衝池狀態。

儘管一個緩衝池的大小可能是數GB，但InnoDB保存到磁碟的緩衝池數據相比之下卻很小。 只有定位適當頁面所需的表空間ID和頁面ID才會保存到磁碟。 此信息來自INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA表。 默認情況下，表空間ID和頁面ID數據保存在名為ib_buffer_pool的文件中，該文件保存在InnoDB數據目錄中。 文件名和位置可以使用innodb_buffer_pool_filename配置參數進行修改。

由於數據與常規資料庫操作一樣，緩存在緩衝池中並且超出緩衝池，因此，如果最近更新了磁碟頁面，或者如果DML操作涉及尚未載入的數據，則沒有問題。 載入機制跳過不存在的請求頁面。

底層機制涉及調度執行轉儲和載入操作的後台線程。

壓縮表中的磁碟頁面以壓縮格式載入到緩衝池中。 在DML操作期間訪問頁面內容時，頁面像往常一樣解壓縮。 由於解壓縮頁面是CPU密集型進程，因此在連接線程中執行並行操作比執行緩衝池恢復操作的單個線程的效率更高。

以下主題描述了與保存和恢復緩衝池狀態相關的操作：

為buffer pool 也沒配置dumo 百分比

在從緩衝池中轉儲頁面之前，可以通過設置innodb_buffer_pool_dump_pct選項來配置要轉儲的最近使用的緩衝池頁面的百分比。 如果您計劃在伺服器運行時轉儲緩衝池頁面，則可以動態配置該選項：

SET GLOBAL innodb_buffer_pool_dump_pct=40;

如果您計劃在伺服器關閉時轉儲緩衝池頁面，請在您的配置文件中設置innodb_buffer_pool_dump_pct。

[mysqld]

innodb_buffer_pool_dump_pct=40

在MYSQL5.7中當innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup被默認啟用時，innodb_buffer_pool_dump_pct默認值從100（轉儲所有頁面）變為25（轉儲最近使用頁面的25％）。

在關機時保存緩衝池狀態並在啟動時恢復

要在關閉伺服器時保存緩衝池的狀態，請在關閉伺服器之前發出以下語句：

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

innodb_buffer_pool_dump_at_shutdown默認啟用。

要在伺服器啟動時恢復緩衝池狀態，請在啟動伺服器時指定--innodb_buffer_pool_load_at_startup選項：

mysqld --innodb_buffer_pool_load_at_startup=ON;

innodb_buffer_pool_load_at_startup默認啟用。

在線保存和重建buffer pool狀態

要在MySQL伺服器運行時保存緩衝池的狀態，請發出以下語句：

SET GLOBAL innodb_buffer_pool_dump_now=ON;

要在MySQL運行時恢復緩衝池狀態，請發出以下語句：

SET GLOBAL innodb_buffer_pool_load_now=ON;

顯示 buffer pool dump 過程

要將緩衝池狀態保存到磁碟時顯示進度，請發出以下語句：

SHOW STATUS LIKE Innodb_buffer_pool_dump_status;

如果操作尚未開始，則返回「 not started」。 如果操作完成，則列印完成時間（例如， Finished at 110505 12:18:02）。 如果操作正在進行中，則提供狀態信息（例如， Dumping buffer pool 5/7, page 237/2873）。

顯示 buffer pool 載入過程

要在載入緩衝池時顯示進度，請發出以下語句：

SHOW STATUS LIKE Innodb_buffer_pool_load_status;

如果操作尚未開始，則返回「 not started」。 如果操作完成，則列印完成時間（例如， Finished at 110505 12:23:24）。 如果操作正在進行中，則提供狀態信息（例如， Loaded 123/22301 pages）。

中斷 buffer pool 載入操作

要中止緩衝池載入操作，請發出以下語句：

SET GLOBAL innodb_buffer_pool_load_abort=ON;

使用性能方案監視緩衝池負載進度

您可以使用性能方案監視緩衝池負載進度。

以下示例演示如何啟用stage/innodb/buffer pool載入階段事件工具和相關的使用者表以監視緩衝池載入進度。

有關此示例中使用的緩衝池轉儲和載入過程的信息，請參見第14.6.3.8節「保存和恢復緩衝池狀態」。 有關Performance Schema階段事件工具和相關使用者的信息，請參見第25.11.5節「性能模式階段事件表」。

  • 啟用stage/innodb/buffer pool載入工具：

    mysql> UPDATE performance_schema.setup_instruments SET ENABLED = YES WHERE NAME LIKE stage/innodb/buffer%;

  • 啟用舞台事件使用者表格，其中包括events_stages_current，events_stages_history和events_stages_history_long。

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = YES WHERE NAME LIKE %stages%;

  • 通過啟用innodb_buffer_pool_dump_now來轉儲當前的緩衝池狀態。

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

  • 檢查緩衝池轉儲狀態以確保操作已完成。

mysql> SHOW STATUS LIKE Innodb_buffer_pool_dump_statusG *************************** 1. row ***************************

Variable_name: Innodb_buffer_pool_dump_status

Value: Buffer pool(s) dump completed at 150202 16:38:58

  • 通過啟用innodb_buffer_pool_load_now載入緩衝池：

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

通過查詢性能模式events_stages_current表來檢查緩衝池載入操作的當前狀態。 WORK_COMPLETED列顯示載入的緩衝池頁面的數量。 WORK_ESTIMATED列以頁面形式提供剩餘工作的估計值。

mysql>SELECTEVENT_NAME,WORK_COMPLETED,WORK_ESTIMATEDFROMperformance_schema.events_stages_current;

如果緩衝池載入操作已完成，則events_stages_current表將返回一個空集。 在這種情況下，您可以檢查events_stages_history表以查看已完成事件的數據。 例如：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;

注意：

在啟動時使用innodb_buffer_pool_load_at_startup載入緩衝池時，您還可以使用性能模式監視緩衝池載入進度。 在這種情況下，啟動時必須啟用stage/innodb/buffer pool load instrument和相關的用戶。


推薦閱讀：

※深入淺出hbase和bigtable
※資料庫系統概念筆記（1）引言（上）
※關係型資料庫 RDBMS 的舊與新 -- 談談 NewSQL
※TiDB 在 Mobikok 廣告系統中的應用和實踐
※MySQL恢復delete的數據

TAG:MySQL | MySQL入門 | 資料庫 |