技術分享|delete 語句引發大量 sql 被kill 問題分析

2019 年 11 月 21 日
筆記

一現象

某個資料庫經常在某個時間點比如凌晨2點或者白天某些時間段發出如下報警

[Critical][prod][mysql] - 超200 kill SQL/分鐘  [P0][PROBLEM][all(#2) db_data.Com_kill db=XXXX[m]:3306 10.53333>=3.3]  [O1 2019-11-01 03:40:00]

報警的意思是每分鐘超過200個sql被kill，是一個嚴重告警級別，會打電話給DBA。大半夜報警的確令人不爽，那麼如何解決這個問題呢？

通過檢查日誌，我們發現被kill的sql都是delete語句。業務方其實會定時的跑刪除任務，這個任務涉及到N 多個表，刪除任務持續時間比較長，所以白天和晚上都有一定概率會觸發 sql-killer ,然後報警。

在有贊的資料庫運維體系中，每個實例都會配置一個 sql-killer 的實時工具，用於kill query 超過指定閾值的sql請求(類似pt-killer)。

二初步分析

在之前的案例分析過程中，碰到過因為長事務導致特定表上面的查詢耗時增加的問題。經分析發現，這次被kill的SQL 是分布在各個表上面，而且查詢發現並不存在長事務。

分析問題發生時候的資料庫快照資訊，QPS 都很低，除了差不多10 TPS 的DELETE和幾十的SELECT，沒有發現有問題的SQL。

分析當時的show engine innodb status 的資訊，發現每次出問題的時候都會出現一些latch的等待，如下圖所示。

找到對應的程式碼行數

看程式碼鎖位置像是在等待各種Buffer Pool的各種latch。為啥會等待在這裡呢，又沒有DDL相關的SQL，於是百思不得其解。問題診斷一時間陷入困境。

三抽絲剝繭

由於等待和Buffer Pool的各種latch相關，而且delete操作本身會產生大量臟數據，那會不會跟刷臟頁操作相關呢？

我們看下SQL被kill的量和刷臟頁的量之間的關係

發現每秒刷臟頁的量和SQL被kill的量的曲線有點相近，看著刷臟頁的量挺大的，但是每秒DELETE的TPS又不是很高，為啥這麼低的TPS會讓刷臟頁頻率抖動以及SQL執行變慢呢？

曾經換過不同批次的機器，發現問題依舊，並沒有改善，說明並不是機器本身的問題。

繼續瀏覽buffer pool相關的監控指標，像是發現新大陸一樣的發現了一個異常指標

臟頁比例達到了快90%！！！太嚇人了！！！

為啥臟頁比例會達到90%呢，無非就是刷臟頁的速度跟不上產生的速度，要麼就是IO能力不行，要麼就是產生臟頁的速度過快，要麼就是記憶體池太小，導致Buffer Pool被臟頁佔滿。

那麼這個臟頁比例達到快90% 會有什麼問題呢？

MySQL有兩個關於臟頁的參數

# yzsql    3306 param  dirty  Variable_name    Value  innodb_max_dirty_pages_pct         75.000000  innodb_max_dirty_pages_pct_lwm     50.000000

我們查看下官方定義

innodb_max_dirty_pages_pct :

InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. The default value is 75.

innodb_max_dirty_pages_pct 是為了避免臟頁比例大於75%，改變該參數不會影響刷臟頁的速度。

innodb_max_dirty_pages_pct_lwm：

Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely.

innodb_max_dirty_pages_pct_lwm 表示的是當臟頁比例達到該參數表示的低水位時候，刷臟執行緒就開始預刷臟來控制臟頁比例，避免達到innodb_max_dirty_pages_pct 。刷臟頁的最大IO能力是受innodb_io_capacity和 innodb_io_capacity_max 控制。

生產上我們將innodb_max_dirty_pages_pct_lwm設置成了50

當臟頁比例大於 innodb_max_dirty_pages_pct 時候，InnoDB 會進行非常激烈的刷臟頁操作，但是由於DELETE操作還是在進行，臟頁產生的速度還是非常快，刷臟頁的速度還是跟不上臟頁產生的速度。為了避免臟頁比例進一步擴大，更新將會被堵塞，從而導致DELETE 執行變慢，直至被KILL。

發現問題之後，根據我們之前的假設，有三種解決方案：

調大 io_capacity ，但是由於主機是多實例部署，IO佔用已經比較高，PASS。
降低臟頁產生速度，也就是調低DELETE速度，因為一天的速度產生很快，為了避免刪除跟不上插入的速度，也被PASS
調大Buffer Pool，可以容納更多的臟頁。

說干就干，得益於MySQL 5.7的在線調整Buffer Pool，立馬將Buffer Pool Size擴了一倍，效果非常顯著

臟頁比例立馬下降，被kill的SQL也下降了。平均SQL rt下降很多。

四總結

得益於MySQL的開源，很多錯誤都可以直接確認到對應的程式碼，大致定位到問題發生的地方，給問題排查帶來了很多方便。同時對MySQL buffer pool 的命中率以及臟頁比例也要多多關注，對SQL的性能都有很大的影響。