作為綜合性多業(yè)務(wù)的“互聯(lián)網(wǎng)+生活服務(wù)”平臺(tái)， 美團(tuán)點(diǎn)評(píng) 對(duì)數(shù)據(jù)庫(kù)的穩(wěn)定運(yùn)行有較高的要求，小概率的性能抖動(dòng)(包括慢SQL)都會(huì)造成一定的可用性損失。本文將從過(guò)去幾年遇到的一些性能問(wèn)題中，挑選了一個(gè)較為棘手的案例，探究端到端數(shù)據(jù)庫(kù)性能問(wèn)題的解決思路，為DBA同學(xué)在解決類似問(wèn)題時(shí)提供一種參考。

問(wèn)題描述

在一段時(shí)間內(nèi)不斷有開發(fā)同學(xué)反饋，線上應(yīng)用程序獲取數(shù)據(jù)超時(shí)，通過(guò)C AT 監(jiān)控系統(tǒng)發(fā)現(xiàn)這些應(yīng)用的SQL 99line都比較高，這在一定程度上影響了對(duì)應(yīng)業(yè)務(wù)的QoS，比如達(dá)不到99.99%的業(yè)務(wù)可用性(超時(shí)被定義為不可用)。這些問(wèn)題出現(xiàn)在很多業(yè)務(wù)場(chǎng)景中，是一個(gè)普遍性問(wèn)題。

通過(guò) C AT 監(jiān)控系統(tǒng)、SQL樣本、慢查詢系統(tǒng)等進(jìn)一步了解，發(fā)現(xiàn)這類SQL有如下特征:

• 基本上都是以主鍵或唯一鍵為條件的簡(jiǎn)單查詢，查詢后的結(jié)果集及掃描的行數(shù)都比較小;
• 查詢的表的數(shù)據(jù)總量也很小，最小的表甚至只有幾千行;
• 時(shí)間達(dá)到了幾百ms，甚至1s;
• 數(shù)據(jù)庫(kù)的slow log里沒(méi)有記錄這類SQL。

下圖為CAT相關(guān)監(jiān)控?cái)?shù)據(jù)的樣本，以xxx-service這個(gè)service為例：

99line的監(jiān)控?cái)?shù)據(jù)，有很多SQL的返回時(shí)間超過(guò)100ms以上。

SQL的絕對(duì)數(shù)量在2016年9月6日當(dāng)天為 ：3788。

具體到某個(gè)SQL，甚至達(dá)到了929ms。

FB_Coach的表結(jié)構(gòu)如下：

可看到最多641條記錄，還有聯(lián)合索引。

概要分析

要想定位到原因，必須通過(guò)排除法找到該SQL到底慢在哪個(gè)階段，這樣才能縮小范圍。接下來(lái)我們來(lái)分析慢SQL的花費(fèi)時(shí)間組成。從下圖可看出，時(shí)間主要由3部分組成：

• App Server：發(fā)出SQL請(qǐng)求的時(shí)間，接收返回結(jié)果的時(shí)間
• 網(wǎng)絡(luò)：SQL請(qǐng)求包及查詢結(jié)果在網(wǎng)絡(luò)上花費(fèi)的時(shí)間
• MySQL Server：發(fā)出SQL到查詢結(jié)果整個(gè)過(guò)程花費(fèi)的時(shí)間

我們可以通過(guò)抓包工具獲取每個(gè)階段花費(fèi)的時(shí)間，從而定位到底慢在哪個(gè)階段。

問(wèn)題解決思路迭代

思路1：確認(rèn)哪個(gè)過(guò)程花費(fèi)的時(shí)間最多

方法：分別在APP Server與MySQL Server部署TcpDump抓包工具，得到數(shù)據(jù)包在4個(gè)監(jiān)測(cè)點(diǎn)的“到達(dá)時(shí)間”。為了方便，把如下4個(gè)Wireshark分析結(jié)果(對(duì)TcpDump抓取日志分析)按4個(gè)方位標(biāo)注：

• APP Server 發(fā)出SQL(左上)
• MySQL Server 收到SQL(右上)
• MySQL Server 將查詢發(fā)出(右下)
• APP Server 收到查詢結(jié)果(左下)

從數(shù)據(jù)可以準(zhǔn)確的看出時(shí)間主要花費(fèi)在MySQL內(nèi)部，具體時(shí)間為22.569285000-21.962634000=0.6066509999999994(秒)，約為606ms。

抓包結(jié)果： 慢在MySQL Server端。

思路2： 一條SQL進(jìn)入MySQL Server到查詢結(jié)果輸出分哪些階段?

方法： 將MySQL內(nèi)部對(duì)SQL查詢的流程進(jìn)行梳理，采用排除法定位問(wèn)題。要 把經(jīng)典圖拿出來(lái)說(shuō)事了，以下基礎(chǔ)知識(shí)主要來(lái)自于《高性能MySQL》，“拿來(lái)主義”一下。

首先可以看到，MySQL主要有三個(gè)組件：連接/線程處理、MySQL Server層、存儲(chǔ)引擎層。

• 最上層主要進(jìn)行連接處理、授權(quán)認(rèn)證、安全等;
• 第二層包括查詢解析、分析、優(yōu)化(這三個(gè)是解決問(wèn)題最關(guān)心的)、緩存管理、所有內(nèi)置函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器、視圖，似乎扯得有點(diǎn)遠(yuǎn);
• 第三層包含了主要的存儲(chǔ)引擎層，MySQL Server層(第二層)通過(guò)“存儲(chǔ)引擎API”向存儲(chǔ)引擎層存儲(chǔ)和提取數(shù)據(jù)，此層主要是數(shù)據(jù)存儲(chǔ)相關(guān)。

接下來(lái)通過(guò)一個(gè)客戶端請(qǐng)求查詢數(shù)據(jù)，看看MySQL主要做哪些工作吧。

每個(gè)客戶端(可能理解為App負(fù)責(zé)連接數(shù)據(jù)庫(kù)的組件，我們叫DAL)連接到MySQL服務(wù)器進(jìn)程后會(huì)擁有一個(gè)線程，這個(gè)連接的所有查詢都會(huì)在該線程中去執(zhí)行，同時(shí)服務(wù)器會(huì)緩存線程，以減少創(chuàng)建或銷毀線程的開銷和頻繁的上下文切換。

當(dāng)客戶連接到MySQL服務(wù)器時(shí)，服務(wù)器會(huì)分配一個(gè)線程，之后進(jìn)行權(quán)限認(rèn)證，認(rèn)證通過(guò)后，MySQL就開始解析該SQL查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)數(shù)據(jù)結(jié)構(gòu)(解析樹)，然后對(duì)其各種優(yōu)化，***調(diào)用存儲(chǔ)引擎API獲取或存儲(chǔ)需要的數(shù)據(jù)，***將查詢結(jié)果返回給客戶端。

通過(guò)以上“背書”，我們大概了解了一個(gè)SQL請(qǐng)求的執(zhí)行過(guò)程，那到底慢在哪個(gè)階段呢?

通過(guò)“慢SQL特點(diǎn)”的第4條知道，“數(shù)據(jù)庫(kù)的slow log里沒(méi)有記錄這類SQL”，那慢SQL發(fā)生的階段就可以排除了。

MySQL slow log是記錄SQL執(zhí)行過(guò)程花費(fèi)的時(shí)間，記錄的時(shí)間從“SQL解析”到“存儲(chǔ)引擎”返回?cái)?shù)據(jù)整個(gè)過(guò)程，所以可以排除該SQL是慢在第二層和第三層，那么只能是把時(shí)間花費(fèi)在***層了?和線程相關(guān)?

結(jié)果： 很可能慢在MySQL線程管理上。

思路3： 是創(chuàng)建線程慢?thread cache不夠用，需要頻繁的創(chuàng)建線程?

方法：查看當(dāng)時(shí)數(shù)據(jù)庫(kù)的狀態(tài)值

可以看到，當(dāng)時(shí)空閑的thread很多，監(jiān)控圖也沒(méi)有抖動(dòng)，所以并沒(méi)有頻繁地創(chuàng)建線程。 慢SQL產(chǎn)生的時(shí)間點(diǎn)，空閑的thread很多，并沒(méi)有進(jìn)行大量的線程創(chuàng)建。

那問(wèn)題到底出現(xiàn)在和線程相關(guān)的哪個(gè)環(huán)節(jié)呢? 先把所有和thread相關(guān)的參數(shù)列出來(lái)。

• thread_cache_size
• thread_concurrency
• thread_handling
• thread_pool_high_prio_mode
• thread_pool_high_prio_tickets
• thread_pool_idle_timeout
• thread_pool_max_threads
• thread_pool_oversubscribe
• thread_pool_size
• thread_pool_stall_limit
• thread_stack
• thread_statistics

一眼看過(guò)去，大部分是和Thread-Pool相關(guān)。同時(shí)意識(shí)到這些問(wèn)題是隨著升級(jí)到MySQL 5.6產(chǎn)生的，5.6引入了 Thread-Pool 功能。

結(jié)果： 看來(lái)MySQL5.6的 Thread-Pool 有很大嫌疑了。

思路4： 關(guān)閉MySQL 5.6的 Thread-Pool ，確認(rèn)一下問(wèn)題

方法： 調(diào)整MySQL參數(shù) thread_handling = pool-of-threads---- → thread_handling = One-Connection-Per-Thread。

結(jié)論： 關(guān)閉 Thread-Pool 功能后，減少78%的慢SQL，側(cè)面證明是 Thread-Pool 的問(wèn)題。

以下是具體的證據(jù)，以xxx-service這個(gè)service為例： 打開 Thread-Pool 功能(2016年9月6日當(dāng)天數(shù)據(jù))。

99line占比：有好多超過(guò)100ms的SQL。

慢SQL數(shù)量：3788

關(guān)閉 Thread-Pool 功能后(2016年9月13日當(dāng)天數(shù)據(jù))。

99line占比：已經(jīng)看不到超過(guò)100ms的sql了，都在10ms以內(nèi)。

慢SQL數(shù)量：818

那么關(guān)閉 Thread-Pool ? 答案很顯然，不能! Thread-Pool 是MySQL5.6重要的功能，能夠保證MySQL數(shù)據(jù)庫(kù)高并發(fā)下的性能穩(wěn)定。

思路5： 調(diào)優(yōu) Thread-Pool 相關(guān)參數(shù)

方法：深入了解 Thread-Pool 的工作原理，查找可能產(chǎn)生慢SQL的參數(shù)。

結(jié)果： 找到了相關(guān)參數(shù)(thread_pool_stall_limit)，并且效果明顯，慢SQL數(shù)量從最初的3788減少到63，幾乎全部消滅掉。

以xxx-service這個(gè)service為例，調(diào)整后的效果，2016年9月20日當(dāng)天的數(shù)據(jù)：

99line占比：

慢SQL數(shù)量：63

ok，效果有了，總結(jié)一下。

問(wèn)題分析

1、基本原理

沒(méi)有引入Thread-Pool前，MySQL使用的是one thread per connection，一旦connection增加到一定程度，MySQL的性能將急劇下降甚至被壓跨。引入Thread-Pool后將會(huì)解決上述問(wèn)題,同時(shí)會(huì)減少M(fèi)ySQL內(nèi)部的線程數(shù)(節(jié)省內(nèi)存)及頻繁創(chuàng)建線程的開銷(節(jié)省CPU)。

2、Thread-Pool 是如何工作的?

在MySQL內(nèi)部有一個(gè)專用的thread用來(lái)監(jiān)聽(tīng)數(shù)據(jù)庫(kù)連接請(qǐng)求，當(dāng)一個(gè)新的請(qǐng)求過(guò)來(lái)，如果采用以前的模型(one-thread-per-connection)，main listener(這是主線程中的listener，為了避免與thread group 中的listener混淆，我們稱之為“Main listener”)將從thread cache中取出1個(gè)thread或創(chuàng)建1個(gè)新的thead立即處理該連接請(qǐng)求，由該thread完成該連接的整個(gè)生命周期;

而如果采用Thread-Pool模型，這個(gè)連接請(qǐng)求將會(huì)被隨機(jī)放到一個(gè)thread group(thread pool由多個(gè)thread group 組成)的隊(duì)列中，之后該thread group中worker thread從隊(duì)列中取出并建立連接，一旦連接建立，該連接對(duì)應(yīng)的socket句柄將與該thread group中的listener關(guān)聯(lián)起來(lái)，之后該連接將在該thread group中完成它的生命周期。

接下來(lái)我們來(lái)說(shuō)說(shuō)Thread Group 。Thread Group是Thread-Pool的核心組件，所有的操作都是發(fā)生在thread group。Thread-Pool由多個(gè)(數(shù)量由參數(shù)thread_pool_size來(lái)決定，默認(rèn)等于cpu個(gè)數(shù))thrad group組成。一個(gè)連接請(qǐng)求被隨機(jī)地綁定到一個(gè)thread group，每個(gè)thread group獨(dú)立工作，并且占用一個(gè)核的CPU。所以thread group都會(huì)***限度地保持一個(gè)thread處于ACTIVE狀態(tài)，并且***只有一個(gè)，因?yàn)樘嗑陀锌赡軌嚎鐢?shù)據(jù)庫(kù)。

Thread Group中的thread一般有4個(gè)狀態(tài)：

• TP_STATE_LISTENER
• TP_STATE_IDLE
• TP_STATE_ACTIVE
• TP_STATE_WAITING

當(dāng)一個(gè)線程作為listener運(yùn)行時(shí)就處于“TP_STATE_LISTENER”，它通過(guò)epoll的方式監(jiān)聽(tīng)聯(lián)接到該Thread Group的所有連接，當(dāng)一個(gè)socket就緒后，listener將決定是否喚醒一個(gè)thread或自己處理該socket。此時(shí)如果Thread Group的隊(duì)列為空，它將自己處理該socket并將狀態(tài)更改為“ACTIVE”，之后該thread 在MySQL Server內(nèi)部處理“工作”，當(dāng)該線程遇到鎖或異步IO(比如將數(shù)據(jù)頁(yè)讀入到buffer pool)這些wait時(shí)，該thread將通過(guò)回調(diào)函數(shù)的方式告訴thread pool，讓其把自己標(biāo)記為“WAITING”狀態(tài)。

此時(shí)，假設(shè)隊(duì)列中有了新的socket準(zhǔn)備就緒，是立即創(chuàng)建新的線程還是等待剛才的線程執(zhí)行結(jié)束呢?

由于Thread-Pool最初設(shè)計(jì)的目標(biāo)是保持一定數(shù)量的線程處于“ACTIVE”狀態(tài)，具體的實(shí)現(xiàn)方式就是控制thread group的數(shù)量和thread group內(nèi)部處于"ACTIVE"狀態(tài)的thread的數(shù)量。控制thread group內(nèi)部的ACTIVE狀態(tài)的數(shù)量，方法就是***限度地保證處于ACTIVE狀態(tài)的線程個(gè)數(shù)是1。很顯然，當(dāng)前thread group中有一個(gè)處于WAITING狀態(tài)的thread了，如果再啟用一個(gè)新的線程并且處于ACTIVE狀態(tài)，剛才的線程由WAITING變?yōu)锳CTIVE狀態(tài)時(shí)，此時(shí)將會(huì)有2個(gè)“ACTIVE”狀態(tài)的線程，和最初的目標(biāo)似乎相背，但顯然也不能讓后續(xù)就緒的socket一直等待下去，那應(yīng)該怎么處理?

那么此時(shí)需要一個(gè)權(quán)衡了，提供了這樣的一個(gè)方法：對(duì)正在ACTIVE或WAITING狀態(tài)的線程啟用一個(gè)計(jì)數(shù)器，超過(guò)計(jì)數(shù)器后將該thread標(biāo)記為stalled，然后thread group創(chuàng)建新的thread或喚醒sleep的thread處理新的sokcet，這樣將是一個(gè)很好的權(quán)衡。超時(shí)時(shí)間該參數(shù)thread_pool_stall_limit來(lái)決定，默認(rèn)是500ms。

如果一個(gè)線程無(wú)事可做，它將保持空閑狀態(tài)(TP_STATE_WAITING)一定時(shí)間(thread_pool_idle_timeout參數(shù)決定，默認(rèn)是60秒)后“自殺”。

3、和我們遇到的具體問(wèn)題相關(guān)的點(diǎn)

假設(shè)上文提到的由“ACTIVE”轉(zhuǎn)化為“WAITING”狀態(tài)的線程(標(biāo)記為“線程A”)所執(zhí)行的“SQL"可能是一個(gè)標(biāo)準(zhǔn)的慢SQL(命名為SQLA，執(zhí)行時(shí)間較長(zhǎng))，那么后續(xù)有連接請(qǐng)求分配到了同一個(gè)thread group,那么新連接的SQL(命名SQLB)需要等待線程A結(jié)束;如果SQLA執(zhí)行時(shí)間超過(guò)500ms，該thread group創(chuàng)建新的worker線程來(lái)處理SQLB。

不管哪種情況，SQLB都會(huì)在線程等待上花費(fèi)很多時(shí)間，此時(shí)SQLB就是CAT監(jiān)控系統(tǒng)上看到的慢SQL。又因?yàn)镾QLA不一定都是慢SQL，所以SQLB也不是每次在線程等待上花費(fèi)較多的時(shí)間，這就吻合我們看到的現(xiàn)象“一定比例的慢SQL”。

解決方法

找到問(wèn)題了，那么解決辦法就簡(jiǎn)單了。調(diào)整thread_pool_stall_limit=10，這樣就強(qiáng)迫被SQLA更快被標(biāo)記為stalled，然后創(chuàng)建新的線程來(lái)處理SQLB。

帶來(lái)的價(jià)值

• 以xxx-service為例，減少了98.3%的慢SQL，效果很明顯;
• 該問(wèn)題的解決讓百個(gè)產(chǎn)品線從中受益，業(yè)務(wù)可用性超過(guò)了99.99%。

首先我們分析了慢SQL的特點(diǎn)及該SQL花費(fèi)的時(shí)間組成，通過(guò)“時(shí)間花費(fèi)在哪”這一通用方法，不斷把問(wèn)題范圍縮小，最終通過(guò)排除法將問(wèn)題鎖定在MySQL內(nèi)部線程。

對(duì)于MySQL內(nèi)部線程，我們通過(guò)對(duì)參數(shù)“全量掃描”，發(fā)現(xiàn)了與MySQL 5.6新開啟的參數(shù)有關(guān)，粗略確定了Thread-Pool是導(dǎo)致慢SQL問(wèn)題的。

之后通過(guò)關(guān)閉Thread-Pool進(jìn)一步確認(rèn)是開啟該功能引起的。之后我們不斷調(diào)整參數(shù)和閱讀大量相關(guān)的資料，最終將問(wèn)題解決。

通過(guò)以上問(wèn)題的解決，我們可以學(xué)到一些端到端的性能問(wèn)題解決思路：

定位問(wèn)題

劃分問(wèn)題的邊界

每個(gè)問(wèn)題總有它的邊界。當(dāng)我們無(wú)法一眼看出來(lái)問(wèn)題的邊界在哪里時(shí)，就需要不斷的通過(guò)排除法縮小邊界，在特定的邊界內(nèi)就用特定的專業(yè)知識(shí)來(lái)定位問(wèn)題。

搜集關(guān)鍵數(shù)據(jù)得出靠譜結(jié)論

比如生產(chǎn)環(huán)境中會(huì)有各種數(shù)據(jù)，包含監(jiān)控?cái)?shù)據(jù)、臨時(shí)部署工具獲取的數(shù)據(jù)，充分利用這些數(shù)據(jù)支撐我們的結(jié)論。

對(duì)問(wèn)題產(chǎn)生的時(shí)間或影響范圍進(jìn)行上下文聯(lián)想

很多問(wèn)題是隨著一些改變產(chǎn)生的，就像軟件的生命周期一樣，受到各種環(huán)境的變化影響。通過(guò)問(wèn)題產(chǎn)生的上下去尋找問(wèn)題的原因，可以發(fā)現(xiàn)大部分問(wèn)題的產(chǎn)生原因。

解決問(wèn)題

不斷嘗試

有很多人認(rèn)為，知道問(wèn)題的原因了，解決問(wèn)題是比較容易的。其實(shí)我認(rèn)為這個(gè)是反的。因?yàn)橹挥星宄绬?wèn)題解決了，才能證明問(wèn)題的原因是對(duì)的。在找到問(wèn)題的原因之前，其實(shí)我們已經(jīng)通過(guò)不斷的調(diào)整和測(cè)試把問(wèn)題解決了。所以解決問(wèn)題很關(guān)鍵，貌似是廢話。

合理的理論推斷問(wèn)題產(chǎn)生的原因

問(wèn)題解決了，原因也找到了，***一步還要“自圓其說(shuō)”，這就需要深究技術(shù)原理，找到切入點(diǎn)，復(fù)現(xiàn)問(wèn)題了。

解決問(wèn)題的方法有千萬(wàn)種，這里列舉了其中一種，希望能夠幫助到大家。