MySQL訪問行更新慢、使用者執行緒大量堆積竟是因為它

1 問題現象

---

最近，騰訊雲某內部系統不定期出現資料庫訪問行更新慢，資料庫使用者執行緒大量堆積的現象。從slow log中觀察，大量update執行時間超過10秒，甚至個別update執行時間超過百秒，這已經嚴重影響該系統的正常執行。 運維同學不得不採取殺死執行session的方式解決該問題，由於訪問資料庫的任務是離線後臺批處理任務，因此會選擇業務壓力小的時候執行該任務，比如半夜12點，因此，運維同學必須半夜採取緊急措施，這給線上執行造成極大的負擔。

2 問題分析

---

2.1 山重水複

根據運維同學反饋，資料庫是不定期出現慢查詢的現象，懷疑資料庫可能存在死鎖問題。 由於問題復現的不確定性，因此線上上例項寫指令碼抓現場： 當問題出現時，記錄下innodb引擎狀態（show innodb status）、使用者執行緒狀態，innodb引擎狀態資訊中存在死鎖資訊。

遺憾的是，透過對innodb status分析，發現LATEST DETECTED DEADLOCK中不存在死鎖問題，初步排除偶發死鎖導致問題的可能，只能從業務模型角度尋找思路。

2.2 峰會路轉

透過與業務系統開發、運維人員的溝通，發現有三個業務子系統會訪問該資料庫，這三個子系統訪問模式類似，整理該業務模型如下圖所示：

每一個離線請求都會觸發上述流程，對同一個id的行短時間內有多次更新，如果等鎖超時會重試，會重試十次。在極端場景，疊加重試請求，會有2000+執行緒同時更新資料庫，造成大量連線等待現象。 對熱點行更新會加行鎖，行鎖在事務提交時釋放，釋放後喚醒其他執行緒繼續更新，正常情況下熱點行更新會降低資料庫吞吐但不會產生數十秒的事務等待，因此懷疑加鎖、釋放鎖、喚醒其他執行緒的某些環節有問題，導致大併發的極端情況下資料庫效能嚴重下降。

2.3 柳暗花明

由於抽象出了出問題時的業務模型，按照該業務模型基本可以復現問題，因此搭建線下環境，用pt-pmp、perf等工具分析資料庫問題。

經過對pt-pmp仔細分析，發現部分執行緒等待異常，如下圖所示：

上圖中1442個執行緒等待喚醒，這個是正常的，但有圖上標出的2組異常執行緒：

• 233個執行緒等待lock_wait_suspend_thread中的lock_wait_table_release_slot的入口mutex上
• 181個執行緒等待lock_wait_suspend_thread本身的mutex上。

經過程式碼分析，這兩個mutex為lock_wait_mutex_enter()，這把鎖出現在兩個地方中：

• 使用者執行緒呼叫的lock_wait_suspend_thread
• 後臺執行緒：lock_wait_timeout_thread

lock_wait_suspend_thread函式讓所有呼叫執行緒進入suspend狀態，掛起。 當熱點行更新時，只有一個執行緒更新其他所有執行緒都掛起等待行鎖，因此在熱點行更新時，這個函式是熱點。圖中的1442個執行緒就是在等待行鎖喚醒。

lock_wait_timeout_thread是鎖超時監測執行緒，監測是否有執行緒等鎖超時，該執行緒會掃描每一個由lock_wait_suspend_thread進入等待的執行緒，判斷其是否超時，如果有超時，喚醒。觸發機制有2種：

• 每秒定時觸發
• lock_wait_suspend_thread通知觸發，這個就是熱點行更新慢的關鍵！

lock_wait_suspend_thread與lock_wait_timeout_thread關係如下圖所示：

每增加n個進入等待程式，其中的每一個程式都會觸發一次lock_wait_timeout_thread，而每一次lock_wait_timeout_thread呼叫都會對m個已經掛起執行緒持鎖掃描，這樣多出n * m次持鎖掃描。 由於持鎖掃描會進一步加劇使用者執行緒等待在lock_wait_suspend_thread入口鎖lock_wait_mutex_enter()的等待，造成更多觸發lock_wait_timeout_thread執行緒的機會，單次運lock_wait_timeout_thread時間增加，那個進入惡性迴圈，所有執行緒都在爭奪lock_wait_mutex_enter()鎖。

3 問題解決

---

阻斷lock_wait_suspend_thread 對lock_wait_timeout_thread觸發，如下圖所示：

lock_wait_suspend_thread 對lock_wait_timeout_thread的呼叫，是用來加速監測鎖超時等待的，去掉該呼叫，減少大壓力併發的熱點行更新對lock_wait_timeout_thread的呼叫，進而減小lock_wait_suspend_thread 鎖等待，進而消除熱點。

4 結果

---

業務模擬工具按照線上業務模型，模擬線上執行2000個業務請求同時進行，每秒每個請求更新一次，分析每個訪問的執行時間（對binlog掃面得到執行時間（exec_time）得出執行時間）

用模擬業務的測試工具對改前、改後版本進行測試，結果如下：

執行時間

5.6最佳化前

5.6最佳化後

所有	6968	32460
1-9	1504（佔比21.6%）	802（2.47%）
>10	12（佔比0.17%）	0

從效果上看，最佳化後的熱點基本消失，5.6延遲（1-9s）的佔比，最佳化後是最佳化前的十分之一，基本解決熱點問題。 檢視ptpmp堆疊如下所示：

由圖可見，大部分執行緒被掛起，等在lock_wait_suspend_thread上的slot->event上面，這是一個正常的行為。 之前的熱點消失，只有5個執行緒等在入口鎖上面。

該修復隨著最新的txsql 5.6釋出線上，經過近一個月的線上執行，騰訊雲的線上業務沒有再出現更新慢的問題，基本確認問題已經解決。