《玩轉processlist，高效追溯MySQL活躍連接數飆升根因》要點：
本文介紹了玩轉processlist，高效追溯MySQL活躍連接數飆升根因，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

劉世勇,曾就職于華為、網易,2015年初加入鏈家網,負責鏈家網數據庫的運維、數據庫架構設計、DB自動化運維平臺的構建等工作.

一、引言

在運維MySQL時,經常遇到的一個問題就是活躍連接數飆升.一旦遇到這樣的問題,都根據后臺保存的processlist信息,或者連上MySQL環境,分析MySQL的連接情況.處理類似的故障多了,就萌生了一種想法,做個小工具,每次接到這種報警的時候,能夠快速地從各個維度去分析和統計當前MySQL中的連接狀態.比如當前連接的分布情況、活躍情況等等.

另外,真實故障處理時,光知道連接分布情況往往還不夠,我們需要知道當前MySQL的正在忙于做什么,也就是正在執行一些什么樣的SQL.而且,有時候即使我們知道了當前執行的SQL情況,也很難找到根因,因為如果活躍連接一旦飆升,這是的CPU基本上是處于被打滿的狀態,IO的負載也非常高,即使平時很快的SQL也變成了慢SQL,更不用說本身就很慢的SQL了.那我們怎么去甄別這些SQL里,哪些是導致問題的罪魁禍首,哪些僅僅是受害者呢?

帶著這些需求和問題,本文逐漸展開并一一做分析和解答,展示我們這個小工具的功能.

二、連接分析

想知道當前MySQL的連接信息,最直觀的方法是看MySQL的processlist,如果希望看到完整的SQL,可以執行show full processlist,或者直接查information_schema中的processlist這個表.當MySQL中連接數比較少的時候,還能夠人肉分析出來,可是如果連接數比較多,那就很難考肉眼看processlist去分析問題了.

最開始,我們的做法是寫個腳本,用MySQL客戶端在命令行登錄MySQL,并執行show full processlist,然后將輸出作為一個文本分析.本來這種實現方式在MySQL5.5和MariaDB上運行得很好,可是,當在MySQL5.6環境上運行時,出現了問題,在控制臺輸出中會多出一行Warning: Using a password on the commandline interface can be insecure,相信很多運行orzdba的同學也遇到過這種情況.這個是MySQL5.6本身的安全提示,輸入明文密碼時,沒有辦法避免,阿里的同學還分享過他們為此做過源碼改造,因為他們很多任務都依賴于命令行執行MySQL命令并捕獲結果.

還有另外一種方式規避這個問題就是用mysql_config_editor這個工具,但是這個需要做額外的一些配置,同時也有安全上的隱患.我們沒有能力改造源碼,但是也不想使用mysql_config_editor,所以我們使用了另外的方式,不從命令行登錄,而是用information_schema的表processlist作為數據源,在上面做查詢,得到processlist的信息.還有另外一張表performance_schema.threads,也包含了同樣的結果,甚至更豐富的后臺線程信息,而且相比information_schema.processlist,在查詢的時候不用申請mutex,對系統系能影響小,不過這要求打開perfomance schema,感興趣的同學可以自己嘗試.

確定了連接信息來源,下面就開始分析信息統計維度.查看processlist這個表,表結構如下(以MySQL5.6為例,MariaDB可能有額外的信息)：

• ID：線程ID,這個信息對統計來說沒有太大作用
• USER：連接使用的賬號,這個是一個統計維度,用于統計來自每個賬號的連接數
• HOST：連接客戶端的IP/hostname+網絡端口號,這也是一個統計維度,用于確定發起連接的客戶端
• DB：連接使用的default database,DB通常對應具體服務,可以用于判斷服務的連接分布,這算一個統計維度
• COMMAND：連接的動作,實際上是說連接處于哪個階段,常見的有Sleep、Query、Connect、Statistics等,這也是一個統計維度,主要用于判斷連接是否處于空閑狀態
• TIME：連接處于當前狀態的時間,單位是s,這個在后面進行分析,暫不算在連接狀態的統計維度中
• STATE：連接的狀態,表示當前MySQl連接正在做什么操作,這算一個統計維度,可能的值也比較多,詳細可以查閱官方文檔
• INFO：連接正在執行的SQL,這個在下一節分析,暫不算在連接狀態的統計維度中

通過上面的分析,總結出了5個連接的統計維度：user、host、db、command和state.有了這5個統計維度,我們就可以開始著手寫小工具了.

最基本的功能需求就是,查詢information_schema.processlist這個表,然后按剛才總結的5個統計維度,對MySQL中的連接進行分組統計,按照統計個數排序.processlist這個表的host字段需要做一些細節上的處理,因為它的值實際上是IP/hostname+網絡端口號的組合,我們需要把端口號裁剪掉,這樣才能按照客戶端進行統計,否則每個客戶端連接的端口號都是不一樣的,沒法進行分組統計.

最后的輸出如下：

有了最基本的功能,能滿足最基本的統計需求.可是在實際排查和處理線上問題時,可能并不關心所有的統計維度,只需要按照上述5個維度中的部分進行統計；另外,可能希望host出現在user的前面,優先按照客戶端的IP或者是hostname進行統計.所以,這就要求這個工具具有增加靈活地添加或者刪除統計維度的功能,而且能夠對統計維度的出現順序進行動態調整.

最后的示例輸出如下：

最開始說了,我們造這個工具的初衷是分析活躍連接,可是統計出來的結果中,包含了空閑連接,那么需要將空閑連接從統計結果中排除出去.當然,除了空閑連接,可能還有一些MySQL本身的一些連接,例如slave線程,binlog dump線程等,也希望從結果排除出去.這就要求有個按照任意統計維度進行排除的功能.既然有了排除功能,那同樣也可以增加包含功能,即按照任意統計維度進行過濾,包含固定條件的連接才能出現在統計結果之中.

有了這個連接統計信息,我們就清楚當前MySQL內部的連接狀態,大致判斷出是哪個業務或者模塊有問題.

三、SQL分析

分析到業務或者模塊的粒度還不夠,到底是哪個接口或者是哪個功能有問題呢?根據上面的連接狀態信息,還沒有辦法準確地回答這個問題.我們繼續深入,分析processlist中的SQL,回去看到上節中被我們暫時忽略的information_schema.processlist這個表的INFO字段,里面就保存了每個活躍連接上正在執行的SQL信息.通過分析和統計SQL,我們才真正清晰地掌握MySQL當前的內部活動,活躍連接都在干些什么事.通過這種方式,我們可以協助RD同學快速地定位問題,找到有問題的接口或者是功能模塊.

其實,要統計SQL并不容易,因為SQL千變萬化,每一條SQL都不是一樣的,即使是統一功能模塊的SQL,參數也可能不一樣.那這種情況下,如何統計SQL呢?這里借鑒了pt-toolkit中的設計思想.在pt-query-digest的分析結果中,有一個fingerprint的字段,它其實是一個hash值,這個hash值代表了一類SQL,這類SQL除了參數不一樣之外,其它的SQL結構都是完全一致的.所以我們把這種思路引入到具體實現中,通過正則,將SQL中的具體條件都去掉,然后將正則之后的SQL結構相同的SQL都算作同一條SQL,然后就可以進行分組統計了.舉個例子,比如現在應用里有2條SQL,分別如下：

這2條SQL除了最后where條件中ucid字段的值不一樣之外,其他的SQL結構是完全一致的.通過正則匹配之后,將ucid的值和limit的行數去掉,在最終的統計結果中,這2條SQL都變成了下面的SQL：

這樣,就實現了SQL的分組統計.

示例輸出如下：

當然,還可以根據需要,添加一些附加信息,便于定位和分析問題,例如user、Host等.

四、事務分析

有了SQL分析和統計,在某些場景下,基本能定位到問題所在,比如高頻的執行計劃良好的SQL.可是如果是由于慢SQL導致整個系統響應變慢的場景,上面單純的SQL統計是否還能夠有效地快速定位出問題呢?肯定不能,因為此時,單純地從統計結果,無法分辨出哪些是導致系統響應變慢的慢SQL,哪些是被影響的SQL.當然,統計結果中,次數多的SQL可能會是慢SQL,但是也可能本身就是一些高頻的接口調用,因為系統響應變慢,導致請求堆積.所以,最好的辦法就是能夠加入一些其它的輔助信息,幫助判斷哪些請求可能是慢查詢.那加入哪些輔助信息呢?有兩種選擇.

首先,我們回去看第一節被我們忽略的information_schema.processlist這個表的Time字段,可以用于大概判斷連接的上SQL的執行,和實際時長的差異取決于SQL執行時每個階段所消耗的時間.其次,因為線上表都是InnoDB表,所以可以和InnoDB的事務統計信息進行關聯.InnoDB的事務分為只讀事務和讀寫事務,信息都保存在information_schema.INNODB_TRX這張表里.對于某些大事務的場景下,一個事務包含多個操作,這種方式得出的結果會有偏差.如果是非InnoDB的引擎,這種方式不適用.

此處分析時,以只讀事務,也就是select語句為例.在實現上,我們將問題簡化,通過processlist中time字段的值或者事務的執行時間,去預估一條SQL的執行時間,進而判斷在processlist中,積壓的大量連接中,哪些請求本身就是慢查詢,哪些是受影響變慢的查詢.利用事務判斷時,將processlist中ID字段和information_schema.INNODB_TRX中trx_MySQl_thread_id字段做關聯,具體的SQL為select p.*, now() – t.trx_started as runtime frominformation_schema.processlist p, information_schema.INNODB_TRX t where p.id =t.trx_MySQl_thread_id.最后,統計正則之后每一類SQL總的執行時間,以及平均執行時間.執行時間越長的,我們更傾向于認為是導致問題的罪魁禍首.

示例輸出如下：

• RT：這一類SQL截止當前,總的執行時間,單位是S(秒)
• AVGRT：這一類SQL截止當前,每個事務平均執行時間,單位是S(秒)

加入user、Host等附加信息之后,輸出如下：

五、結語

通過上面的3個維度,把MySQL的processlist中的可用信息基本上都挖掘得差不多了.我們在實際問題排查和處理時,也經常使用這個工具,經過實踐檢驗,問題定位效率還是比較高效的.

但是,也還存在很多改進的地方.比如SQL語句分析中,limit值不同的,嚴格來說其實應該算不同的SQL,因為執行時間可能相差非常大.另外,SQL執行時間分析中,對于單條select語句的只讀事務分析結果非常準確,但是對于讀寫事務,怎么減少結果的誤差,因為讀寫事務相比只讀事務會更復雜,因為可能涉及鎖等待等一些額外的情況.所有的這些已經在我們的改進計劃中,如果大家有好的思路或者是想法,歡迎交流.

我們自己做這些事情,其實日常運維經驗的積累和沉淀,如果剛好某位同學的思路和實現有雷同,實屬必然.

文章來自微信公眾號：DBAplus社群

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/2199.html