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

作者介紹

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

一、引言

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

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

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

二、連接分析

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

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

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

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

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

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

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

最后的輸出如下：

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

最后的示例輸出如下：

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

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

三、SQL分析

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

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

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

這樣,就實現(xiàn)了SQL的分組統(tǒng)計.

示例輸出如下：

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

四、事務分析

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

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

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

示例輸出如下：

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

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

五、結語

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

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

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

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

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