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

2024-07-24 12:35:37

字體：大中小

供稿：網(wǎng)友

  玩轉(zhuǎn)processlist 高效追溯MySQL活躍連接數(shù)飆升原由：

　　一、引言

　　在運(yùn)維MySQL時(shí)，經(jīng)常遇到的一個(gè)問(wèn)題就是活躍連接數(shù)飆升。一旦遇到這樣的問(wèn)題，都根據(jù)后臺(tái)保存的processlist信息，或者連上MySQL環(huán)境，分析MySQL的連接情況。處理類(lèi)似的故障多了，就萌生了一種想法，做個(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基本上是處于被打滿(mǎn)的狀態(tài)，IO的負(fù)載也非常高，即使平時(shí)很快的SQL也變成了慢SQL，更不用說(shuō)本身就很慢的SQL了。那我們?cè)趺慈フ鐒e這些SQL里，哪些是導(dǎo)致問(wèn)題的罪魁禍?zhǔn)祝男﹥H僅是受害者呢？

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

　　二、連接分析

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

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

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

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

　　ID：線(xiàn)程ID，這個(gè)信息對(duì)統(tǒng)計(jì)來(lái)說(shuō)沒(méi)有太大作用
　　USER：連接使用的賬號(hào)，這個(gè)是一個(gè)統(tǒng)計(jì)維度，用于統(tǒng)計(jì)來(lái)自每個(gè)賬號(hào)的連接數(shù)
　　HOST：連接客戶(hù)端的IP/hostname+網(wǎng)絡(luò)端口號(hào)，這也是一個(gè)統(tǒng)計(jì)維度，用于確定發(fā)起連接的客戶(hù)端
　　DB：連接使用的default database，DB通常對(duì)應(yīng)具體服務(wù)，可以用于判斷服務(wù)的連接分布，這算一個(gè)統(tǒng)計(jì)維度
　　COMMAND：連接的動(dòng)作，實(shí)際上是說(shuō)連接處于哪個(gè)階段，常見(jiàn)的有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ì)維度中
　　通過(guò)上面的分析，總結(jié)出了5個(gè)連接的統(tǒng)計(jì)維度：user、host、db、command和state。有了這5個(gè)統(tǒng)計(jì)維度，我們就可以開(kāi)始著手寫(xiě)小工具了。

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

　　最基本的功能需求就是，查詢(xún)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)裁剪掉，這樣才能按照客戶(hù)端進(jìn)行統(tǒng)計(jì)，否則每個(gè)客戶(hù)端連接的端口號(hào)都是不一樣的，沒(méi)法進(jìn)行分組統(tǒng)計(jì)。

　　有了最基本的功能，能滿(mǎn)足最基本的統(tǒng)計(jì)需求。可是在實(shí)際排查和處理線(xiàn)上問(wèn)題時(shí)，可能并不關(guān)心所有的統(tǒng)計(jì)維度，只需要按照上述5個(gè)維度中的部分進(jìn)行統(tǒng)計(jì)；另外，可能希望host出現(xiàn)在user的前面，優(yōu)先按照客戶(hù)端的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)整。

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

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

　　三、SQL分析

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

　　其實(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值代表了一類(lèi)SQL，這類(lèi)SQL除了參數(shù)不一樣之外，其它的SQL結(jié)構(gòu)都是完全一致的。所以我們把這種思路引入到具體實(shí)現(xiàn)中，通過(guò)正則，將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)是完全一致的。通過(guò)正則匹配之后，將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ù)需要，添加一些附加信息，便于定位和分析問(wèn)題，例如user、Host等。

　　四、事務(wù)分析

　　有了SQL分析和統(tǒng)計(jì)，在某些場(chǎng)景下，基本能定位到問(wèn)題所在，比如高頻的執(zhí)行計(jì)劃良好的SQL。可是如果是由于慢SQL導(dǎo)致整個(gè)系統(tǒng)響應(yīng)變慢的場(chǎng)景，上面單純的SQL統(tǒng)計(jì)是否還能夠有效地快速定位出問(wèn)題呢？肯定不能，因?yàn)榇藭r(shí)，單純地從統(tǒng)計(jì)結(jié)果，無(wú)法分辨出哪些是導(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)求可能是慢查詢(xún)。那加入哪些輔助信息呢？有兩種選擇。

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

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

　　五、結(jié)語(yǔ)

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

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

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

（編輯：武林網(wǎng)）

上一篇：Mysql通信文件

下一篇：mysql中完整的select語(yǔ)句的用處