mysqldump造成Buffer Pool污染的研究

前言：

最近Oracle MySQL在其官方Blog上貼出了 5.6中一些變量默認值的修改。其中innodb_old_blocks_time 的默認值從0替換成了1000（即1s）

關於該參數的作用摘錄如下：

how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. Increasing this value protects against the buffer pool being filled up by data that is referenced only for a brief period, such as during a full table scan.

其實作用就是：減小單次的大批量數據查詢（類似於mysqldump的行為）對於BufferPool（下稱BP）的污染。

說到這裡就不得不提一下BP的midpoint insert 機制。

下文就將對於這個機制做一定分析和討論。

一、 Buffer Pool 的insert 機制

BP可以被認為是一條長鏈表。被分成young 和 old兩個部分，其中old默認占37%的大小（由innodb_old_blocks_pct 配置）。靠近頂端的Page表示最近被放問。靠近尾端的Page表示長時間未被訪問。而這兩個部分的交匯處成為midpoint。每當有新的Page需要加載到BP時，該page都會被插入到midpoint的位置，並聲明為old-page。當old部分的page，被訪問到時，該page會被提升到鏈表的頂端，標識為young。

由於table scan的操作是先load page，然後立即觸發一次訪問。所以當innodb_old_blocks_time =0 時，會導致table scan所需要的page不讀的作為young page被添加到鏈表頂端。而一些使用較為不頻繁的page就會被擠出BP，使得之後的SQL會產生磁盤IO，從而導致響應速度變慢。這也就是標題中所提到的BP污染。

 

二、 修改innodb_old_blocks_time 的效果

percona之前也做過相關測試，其結論是time=0時，正常訪問的吞吐量下降為10%；當time=1000時，吞吐量和沒有備份時的性能一致。

是否真是如此呢，我們來親自測試一下。

下面是測試結果：

其中concurrency代表sysbench中 --num-threads的數值。

OPT代表該環境下，沒有mysqldump時的sysbench QPS。

余下兩列分別代表有mysqldump時的sysbench QPS。

Concurrency OPT old_time=0 old_time=1000 1 17394 1836 2141 2 29703 3670 3981 3 47347 5683 6540 4 64717 6805 8337 5 83551 8676 15885 6 99396 12978 19893 7 112330 16491 26022 8 126600 23840 33346 9 138468 30760 39194 10 150365 39034 48925 11 163053 43174 60352 12 174916 52066 70180 13 174160 63853 78076 14 173786 65164 80661 15 174268 70965 90633 16 175044 80871 102629 17 175583 90689 103423 18 175939 94805 112629 19 175114 93303 120625

由結果可以看出，time=1000並沒有給查詢性能帶來很大的提升。最佳情況下也只是比time=0時提高80%的性能。

為什麼呢？

其實不難理解，表中的concurrency很大程度上決定了測試page的冷熱程度。並發數越大，每面產生的並行請求就越多，從而每個page被訪問的頻率就越高，page在LRU鏈表中的位置也就越靠頂端。反之亦然。

那麼我們來想想下高頻率熱點數據訪問時的情況。這時雖然mysqldump訪問的page會不斷加載在LRU頂端，但是高頻度的熱點數據訪問會以更快的速度把page再次搶占到LRU頂端。從而導致mysqldump加載入的page會被迅速刷下，並立即被evict（淘汰）。因此，time=0或1000對這種壓力環境下的訪問不會造成很大影響，因為dump的數據根本搶占不過熱點數據。

同樣，超低頻率的數據訪問也是一樣的情況。由於數據訪問頻度很低，大量的page都處於LRU鏈表的尾端。所以無論dump的page被加載到head或是midpoint位置，都會在熱點數據的前面。也就是說無論怎樣，數據page都會被淘汰。所以，這種壓力環境下的性能同樣不會隨著time值的配置變化有很大浮動。

真正能夠享受到time帶來的福利的是那些 處於midpoint邊緣的不溫不火的數據。

從下圖也可以看出，性能提升最大的情況集中在中等訪問量的情況下，也即 37%的位置上

三、 Mid Point位置帶來的影響

從之前的分析也可以得出這樣的結論：innodb_old_blocks_time 的作用范圍對page的冷熱情況有直接聯系。而innodb_old_blocks_pct 又決定了BP的數據分布。

那麼 innodb_old_blocks_pct 的調節，能夠左右 innodb_old_blocks_time的影響范圍。

上圖的曲線也證明了這樣的觀點。當innodb_old_blocks_pct 調節到60%時，波峰也相應平移到了 60%的位置。

總結：
1. innodb_old_blocks_time =1000 一定程度上可以降低mysqldump類型的訪問對數據庫性能帶來的影響。
2. innodb_old_blocks_time =1000 的優化效果有限，對於處於midpoint附近的page能帶來最大的提升效果。