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

作者介紹

劉世勇,曾就職于華為、網(wǎng)易,2015年初加入鏈家網(wǎng),負(fù)責(zé)鏈家網(wǎng)數(shù)據(jù)庫的運(yùn)維、數(shù)據(jù)庫架構(gòu)設(shè)計(jì)、DB自動化運(yùn)維平臺的構(gòu)建等工作.

一、引言

在運(yùn)維MySQL時(shí),經(jīng)常遇到的一個(gè)問題就是活躍連接數(shù)飆升.一旦遇到這樣的問題,都根據(jù)后臺保存的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了.那我們怎么去甄別這些SQL里,哪些是導(dǎo)致問題的罪魁禍?zhǔn)?哪些僅僅是受害者呢?

帶著這些需求和問題,本文逐漸展開并一一做分析和解答,展示我們這個(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)了問題,在控制臺輸出中會多出一行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é)果,甚至更豐富的后臺線程信息,而且相比information_schema.processlist,在查詢的時(shí)候不用申請mutex,對系統(tǒng)系能影響小,不過這要求打開perfomance schema,感興趣的同學(xué)可以自己嘗試.

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

• ID：線程ID,這個(gè)信息對統(tǒng)計(jì)來說沒有太大作用
• USER：連接使用的賬號,這個(gè)是一個(gè)統(tǒng)計(jì)維度,用于統(tǒng)計(jì)來自每個(gè)賬號的連接數(shù)
• HOST：連接客戶端的IP/hostname+網(wǎng)絡(luò)端口號,這也是一個(gè)統(tǒng)計(jì)維度,用于確定發(fā)起連接的客戶端
• DB：連接使用的default database,DB通常對應(yīng)具體服務(wù),可以用于判斷服務(wù)的連接分布,這算一個(gè)統(tǒng)計(jì)維度
• COMMAND：連接的動作,實(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ì)維度,對MySQL中的連接進(jìn)行分組統(tǒng)計(jì),按照統(tǒng)計(jì)個(gè)數(shù)排序.processlist這個(gè)表的host字段需要做一些細(xì)節(jié)上的處理,因?yàn)樗闹祵?shí)際上是IP/hostname+網(wǎng)絡(luò)端口號的組合,我們需要把端口號裁剪掉,這樣才能按照客戶端進(jìn)行統(tǒng)計(jì),否則每個(gè)客戶端連接的端口號都是不一樣的,沒法進(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)行動態(tài)調(diào)整.

最后的示例輸出如下：

最開始說了,我們造這個(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)部活動,活躍連接都在干些什么事.通過這種方式,我們可以協(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,分別如下：

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

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

示例輸出如下：

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

四、事務(wù)分析

有了SQL分析和統(tǒng)計(jì),在某些場景下,基本能定位到問題所在,比如高頻的執(zhí)行計(jì)劃良好的SQL.可是如果是由于慢SQL導(dǎo)致整個(gè)系統(tǒng)響應(yī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可能會是慢SQL,但是也可能本身就是一些高頻的接口調(diào)用,因?yàn)橄到y(tǒng)響應(yīng)變慢,導(dǎo)致請求堆積.所以,最好的辦法就是能夠加入一些其它的輔助信息,幫助判斷哪些請求可能是慢查詢.那加入哪些輔助信息呢?有兩種選擇.

首先,我們回去看第一節(jié)被我們忽略的information_schema.processlist這個(gè)表的Time字段,可以用于大概判斷連接的上SQL的執(zhí)行,和實(shí)際時(shí)長的差異取決于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這張表里.對于某些大事務(wù)的場景下,一個(gè)事務(wù)包含多個(gè)操作,這種方式得出的結(jié)果會有偏差.如果是非InnoDB的引擎,這種方式不適用.

此處分析時(shí),以只讀事務(wù),也就是select語句為例.在實(shí)現(xiàn)上,我們將問題簡化,通過processlist中time字段的值或者事務(wù)的執(zhí)行時(shí)間,去預(yù)估一條SQL的執(zhí)行時(shí)間,進(jìn)而判斷在processlist中,積壓的大量連接中,哪些請求本身就是慢查詢,哪些是受影響變慢的查詢.利用事務(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í)間越長的,我們更傾向于認(rèn)為是導(dǎo)致問題的罪魁禍?zhǔn)?

示例輸出如下：

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

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

五、結(jié)語

通過上面的3個(gè)維度,把MySQL的processlist中的可用信息基本上都挖掘得差不多了.我們在實(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í)間分析中,對于單條select語句的只讀事務(wù)分析結(jié)果非常準(zhǔn)確,但是對于讀寫事務(wù),怎么減少結(jié)果的誤差,因?yàn)樽x寫事務(wù)相比只讀事務(wù)會更復(fù)雜,因?yàn)榭赡苌婕版i等待等一些額外的情況.所有的這些已經(jīng)在我們的改進(jìn)計(jì)劃中,如果大家有好的思路或者是想法,歡迎交流.

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

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

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/2199.html