12 | 為什麼我的MySQL會“抖”一下？

一條 SQL 語句，正常執行的時候特別快，但是有時也不知道怎麼回事，它就會變得特別慢，並且這樣的場景很難復現，它不只隨機，而且持續時間還很短。

你的 SQL 語句為什麼變“慢”了

記憶體里的數據寫入磁碟的過程，叫做 flush。在這個 flush 操作執行之前，記憶體里的數據和磁碟里的是不一致的。

當記憶體數據頁跟磁碟數據頁內容不一致的時候，我們稱這個記憶體頁為“臟頁”。記憶體數據寫入到磁碟後，記憶體和磁碟上的數據頁的內容就一致了，稱為“乾凈頁”。

平時執行很快的更新操作，其實就是在寫記憶體和日誌，而 MySQL 偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）

什麼情況會引發資料庫的 flush 過程

第一種場景是InnoDB 的redo log 寫滿了。這時候系統會停止所有更新操作，把 checkpoint 往前推進，redo log 留出空間可以繼續寫。

把 checkpoint 位置從 CP 推進到 CP’，就需要將兩個點之間的日誌（淺綠色部分），對應的所有臟頁都 flush 到磁碟上。之後，圖中從 write pos 到 CP’之間就是可以再寫入的 redo log 的區域。

第二種場景是系統記憶體不足。當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些數據頁，空出記憶體給別的數據頁使用。如果淘汰的是“臟頁”，就要先將臟頁寫到磁碟。

Q：如果直接淘汰記憶體，下次需要請求該頁時，直接從磁碟讀入數據頁，然後拿 redo log 出來應用效率不好嘛？

A：

如果刷臟頁一定會寫盤，就保證了每個數據頁有兩種狀態：

• 一種是記憶體里存在，記憶體里就肯定是正確的結果，直接返回；

• 另一種是記憶體里沒有數據，就可以肯定數據文件上是正確的結果，讀入記憶體後返回。

這樣的效率最高。

第三種場景是 MySQL 認為系統“空閑”的時候

第四種場景是 MySQL 正常關閉的情況。這時候，MySQL 會把記憶體的臟頁都 flush 到磁碟上，這樣下次 MySQL 啟動的時候，就可以直接從磁碟上讀數據，啟動速度會很快。

上面四種場景對性能的影響

後兩種情景不會太關註“性能”問題，主要來分析前兩種場景下的性能問題。

第一種是“redo log 寫滿了，要 flush 臟頁”，這種情況是 InnoDB 要儘量避免的。因為出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都必須堵住。如果你從監控上看，這時候更新數會跌為 0。

第二種是“記憶體不夠用了，要先將臟頁寫到磁碟”，這種情況其實是常態。

InnoDB 用緩衝池（buffer pool）管理記憶體，緩衝池中的記憶體頁有三種狀態：

• 第一種是，還沒有使用的；
• 第二種是，使用了並且是乾凈頁；
• 第三種是，使用了並且是臟頁。

InnoDB 的策略是儘量使用記憶體，因此對於一個長時間運行的庫來說，未被使用的頁面很少。

而當要讀入的數據頁沒有在記憶體的時候，就必須到緩衝池中申請一個數據頁。這時候只能把最久不使用的數據頁從記憶體中淘汰掉：

如果要淘汰的是一個乾凈頁，就直接釋放出來複用；

但如果是臟頁，就必須將臟頁先刷到磁碟，變成乾凈頁後才能復用。

所以，刷臟頁雖然是常態，但是出現一個查詢要淘汰的臟頁個數太多，會導致查詢的響應時間明顯變長，明顯影響性能

所以，InnoDB 需要有控制臟頁比例的機制，來儘量避免上面的這兩種情況。

InnoDB 刷臟頁的控制策略

首先，要正確地告訴 InnoDB 所在主機的 IO 能力，這樣 InnoDB 才能知道需要全力刷臟頁的時候，可以刷多快。通過設置 innodb_io_capacity 這個參數為磁碟的 IOPS 來避免浪費磁碟性能。

畢竟磁碟能力不能只用來刷臟頁，還需要服務用戶請求。所以接下來，我們就一起看看 InnoDB 怎麼控制引擎按照“全力”的百分比來刷臟頁。

Q：如果你來設計策略控制刷臟頁的速度，會參考哪些因素呢？

A：

如果刷太慢，會出現什麼情況？

首先是記憶體臟頁太多，其次是 redo log 寫滿。

所以，InnoDB 的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是 redo log 寫盤速度。

參數 innodb_max_dirty_pages_pct 是臟頁比例上限，預設值是 75%。

F1：InnoDB 會根據當前的臟頁比例（假設為 M），算出一個範圍在 0 到 100 之間的F1

F2：InnoDB 每次寫入的日誌都有一個序號，當前寫入的序號跟 checkpoint 對應的序號之間的差值為 N。InnoDB 會根據這個 N 算出一個範圍在 0 到 100 之間的數字F2

根據上述算得的 F1 和 F2兩個值，取其中較大的值記為 R，之後引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度。

總結：

合理地設置 innodb_io_capacity 的值，並且平時要多關註臟頁比例，不要讓它經常接近 75%。

一個有趣的策略

一旦一個查詢請求需要在執行過程中先 flush 掉一個臟頁時，這個查詢就可能要比平時慢了。而 MySQL 中的一個機制，可能讓你的查詢會更慢：

在準備刷一個臟頁的時候，如果這個數據頁旁邊的數據頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續蔓延，也就是對於每個鄰居數據頁，如果跟它相鄰的數據頁也還是臟頁的話，也會被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 參數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。

在 MySQL 8.0 中，innodb_flush_neighbors 參數的預設值是 0

Q：一個記憶體配置為 128GB、innodb_io_capacity 設置為 20000 的大規格實例，正常會建議你將 redo log 設置成 4 個 1GB 的文件。

但如果你在配置的時候不慎將 redo log 設置成了 1 個 100M 的文件，會發生什麼情況呢？又為什麼會出現這樣的情況呢？

A：

每次事務提交都要寫 redo log，如果設置太小，很快就會被寫滿，也就是下麵這個圖的狀態，這個“環”將很快被寫滿，write pos 一直追著 CP。

這時候系統不得不停止所有更新，去推進 checkpoint。

這時，看到的現象是磁碟壓力很小，但是資料庫出現間歇性的性能下跌。