玩轉(zhuǎn)processlist，高效追溯MySQL活躍連接數(shù)飆升根因

一、引言

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

另外，真實(shí)故障處理時(shí)，光知道連接分布情況往往還不夠，我們需要知道當(dāng)前MySQL的正在忙于做什么，也就是正在執(zhí)行一些什么樣的SQL。而且，有時(shí)候即使我們知道了當(dāng)前執(zhí)行的SQL情況，也很難找到根因，因?yàn)槿绻钴S連接一旦飆升，這是的CPU基本上是處于被打滿的狀態(tài)，IO的負(fù)載也非常高，即使平時(shí)很快的SQL也變成了慢SQL，更不用說本身就很慢的SQL了。那我們?cè)趺慈フ鐒e這些SQL里，哪些是導(dǎo)致問題的罪魁禍?zhǔn)祝男﹥H僅是受害者呢？

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

二、連接分析

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

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

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

確定了連接信息來源，下面就開始分析信息統(tǒng)計(jì)維度。查看processlist這個(gè)表，表結(jié)構(gòu)如下（以MySQL5.6為例，MariaDB可能有額外的信息）：

• ID：線程ID，這個(gè)信息對(duì)統(tǒng)計(jì)來說沒有太大作用

• USER：連接使用的賬號(hào)，這個(gè)是一個(gè)統(tǒng)計(jì)維度，用于統(tǒng)計(jì)來自每個(gè)賬號(hào)的連接數(shù)

• HOST：連接客戶端的IP/hostname+網(wǎng)絡(luò)端口號(hào)，這也是一個(gè)統(tǒng)計(jì)維度，用于確定發(fā)起連接的客戶端

• DB：連接使用的default database，DB通常對(duì)應(yīng)具體服務(wù)，可以用于判斷服務(wù)的連接分布，這算一個(gè)統(tǒng)計(jì)維度

• COMMAND：連接的動(dòng)作，實(shí)際上是說連接處于哪個(gè)階段，常見的有Sleep、Query、Connect、Statistics等，這也是一個(gè)統(tǒng)計(jì)維度，主要用于判斷連接是否處于空閑狀態(tài)

• TIME：連接處于當(dāng)前狀態(tài)的時(shí)間，單位是s，這個(gè)在后面進(jìn)行分析，暫不算在連接狀態(tài)的統(tǒng)計(jì)維度中

• STATE：連接的狀態(tài)，表示當(dāng)前MySQl連接正在做什么操作，這算一個(gè)統(tǒng)計(jì)維度，可能的值也比較多，詳細(xì)可以查閱官方文檔

• INFO：連接正在執(zhí)行的SQL，這個(gè)在下一節(jié)分析，暫不算在連接狀態(tài)的統(tǒng)計(jì)維度中

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

最基本的功能需求就是，查詢information_schema.processlist這個(gè)表，然后按剛才總結(jié)的5個(gè)統(tǒng)計(jì)維度，對(duì)MySQL中的連接進(jìn)行分組統(tǒng)計(jì)，按照統(tǒng)計(jì)個(gè)數(shù)排序。processlist這個(gè)表的host字段需要做一些細(xì)節(jié)上的處理，因?yàn)樗闹祵?shí)際上是IP/hostname+網(wǎng)絡(luò)端口號(hào)的組合，我們需要把端口號(hào)裁剪掉，這樣才能按照客戶端進(jìn)行統(tǒng)計(jì)，否則每個(gè)客戶端連接的端口號(hào)都是不一樣的，沒法進(jìn)行分組統(tǒng)計(jì)。

最后的輸出如下：

有了最基本的功能，能滿足最基本的統(tǒng)計(jì)需求?？墒窃趯?shí)際排查和處理線上問題時(shí)，可能并不關(guān)心所有的統(tǒng)計(jì)維度，只需要按照上述5個(gè)維度中的部分進(jìn)行統(tǒng)計(jì)；另外，可能希望host出現(xiàn)在user的前面，優(yōu)先按照客戶端的IP或者是hostname進(jìn)行統(tǒng)計(jì)。所以，這就要求這個(gè)工具具有增加靈活地添加或者刪除統(tǒng)計(jì)維度的功能，而且能夠?qū)y(tǒng)計(jì)維度的出現(xiàn)順序進(jìn)行動(dòng)態(tài)調(diào)整。

最后的示例輸出如下：

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

有了這個(gè)連接統(tǒng)計(jì)信息，我們就清楚當(dāng)前MySQL內(nèi)部的連接狀態(tài)，大致判斷出是哪個(gè)業(yè)務(wù)或者模塊有問題。

三、SQL分析

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

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

SELECT * FROM `xxxxxxxxxxxxxxxxxxxx` `t` WHERE `t`.`ucid`='1000000020018048' LIMIT 1

SELECT * FROM `xxxxxxxxxxxxxxxxxxxx` `t` WHERE `t`.`ucid`='1000000020281039' LIMIT 1

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

SELECT * FROM `xxxxxxxxxxxxxxxxxxxx` `t` WHERE `t`.`ucid`=? LIMIT ?

這樣，就實(shí)現(xiàn)了SQL的分組統(tǒng)計(jì)。

示例輸出如下：

當(dāng)然，還可以根據(jù)需要，添加一些附加信息，便于定位和分析問題，例如user、Host等。

四、事務(wù)分析

有了SQL分析和統(tǒng)計(jì)，在某些場(chǎng)景下，基本能定位到問題所在，比如高頻的執(zhí)行計(jì)劃良好的SQL。可是如果是由于慢SQL導(dǎo)致整個(gè)系統(tǒng)響應(yīng)變慢的場(chǎng)景，上面單純的SQL統(tǒng)計(jì)是否還能夠有效地快速定位出問題呢？肯定不能，因?yàn)榇藭r(shí)，單純地從統(tǒng)計(jì)結(jié)果，無法分辨出哪些是導(dǎo)致系統(tǒng)響應(yīng)變慢的慢SQL，哪些是被影響的SQL。當(dāng)然，統(tǒng)計(jì)結(jié)果中，次數(shù)多的SQL可能會(huì)是慢SQL，但是也可能本身就是一些高頻的接口調(diào)用，因?yàn)橄到y(tǒng)響應(yīng)變慢，導(dǎo)致請(qǐng)求堆積。所以，最好的辦法就是能夠加入一些其它的輔助信息，幫助判斷哪些請(qǐng)求可能是慢查詢。那加入哪些輔助信息呢？有兩種選擇。

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

此處分析時(shí)，以只讀事務(wù)，也就是select語句為例。在實(shí)現(xiàn)上，我們將問題簡(jiǎn)化，通過processlist中time字段的值或者事務(wù)的執(zhí)行時(shí)間，去預(yù)估一條SQL的執(zhí)行時(shí)間，進(jìn)而判斷在processlist中，積壓的大量連接中，哪些請(qǐng)求本身就是慢查詢，哪些是受影響變慢的查詢。利用事務(wù)判斷時(shí)，將processlist中ID字段和information_schema.INNODB_TRX中trx_MySQl_thread_id字段做關(guān)聯(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)計(jì)正則之后每一類SQL總的執(zhí)行時(shí)間，以及平均執(zhí)行時(shí)間。執(zhí)行時(shí)間越長(zhǎng)的，我們更傾向于認(rèn)為是導(dǎo)致問題的罪魁禍?zhǔn)住?/p>

示例輸出如下：

• RT：這一類SQL截止當(dāng)前，總的執(zhí)行時(shí)間，單位是S(秒)

• AVGRT：這一類SQL截止當(dāng)前，每個(gè)事務(wù)平均執(zhí)行時(shí)間，單位是S(秒)

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

五、結(jié)語

通過上面的3個(gè)維度，把MySQL的processlist中的可用信息基本上都挖掘得差不多了。我們?cè)趯?shí)際問題排查和處理時(shí)，也經(jīng)常使用這個(gè)工具，經(jīng)過實(shí)踐檢驗(yàn)，問題定位效率還是比較高效的。

但是，也還存在很多改進(jìn)的地方。比如SQL語句分析中，limit值不同的，嚴(yán)格來說其實(shí)應(yīng)該算不同的SQL，因?yàn)閳?zhí)行時(shí)間可能相差非常大。另外，SQL執(zhí)行時(shí)間分析中，對(duì)于單條select語句的只讀事務(wù)分析結(jié)果非常準(zhǔn)確，但是對(duì)于讀寫事務(wù)，怎么減少結(jié)果的誤差，因?yàn)樽x寫事務(wù)相比只讀事務(wù)會(huì)更復(fù)雜，因?yàn)榭赡苌婕版i等待等一些額外的情況。所有的這些已經(jīng)在我們的改進(jìn)計(jì)劃中，如果大家有好的思路或者是想法，歡迎交流。

我們自己做這些事情，其實(shí)日常運(yùn)維經(jīng)驗(yàn)的積累和沉淀，如果剛好某位同學(xué)的思路和實(shí)現(xiàn)有雷同，實(shí)屬必然。