數(shù)據(jù)庫性能調(diào)優(yōu)方案

1、 數(shù)據(jù)庫性能調(diào)優(yōu)方案DBAplus社群 微信號 dbaplus功能介紹 圍繞數(shù)據(jù)庫、大數(shù)據(jù)、PaaS云，頂級大咖、技術(shù)干貨，運營幾個月受眾過十萬！成為運維圈最專注圍繞“數(shù)據(jù)”的學習交流和專業(yè)社群！歡迎投稿，加入探討。作為綜合性多業(yè)務的“互聯(lián)網(wǎng)+生活服務”平臺，美團點評對數(shù)據(jù)庫的穩(wěn)定運行有較高的要求，小概率的性能抖動（包括慢SQL）都會造成一定的可用性損失。本文將從過去幾年遇到的一些性能問題中，挑選了一個較為棘手的案例，探究端到端數(shù)據(jù)庫性能問題的解決思路，為DBA同學在解決類似問題時提供一種參考。問題描述在一段時間內(nèi)不斷有開發(fā)同學反饋，線上應用程序獲取數(shù)據(jù)超時，通過CAT監(jiān)控系統(tǒng)發(fā)現(xiàn)這些應用的S

2、QL 99line都比較高，這在一定程度上影響了對應業(yè)務的QoS，比如達不到99.99%的業(yè)務可用性(超時被定義為不可用)。這些問題出現(xiàn)在很多業(yè)務場景中，是一個普遍性問題。通過CAT監(jiān)控系統(tǒng)、SQL樣本、慢查詢系統(tǒng)等進一步了解，發(fā)現(xiàn)這類SQL有如下特征:基本上都是以主鍵或唯一鍵為條件的簡單查詢，查詢后的結(jié)果集及掃描的行數(shù)都比較?。徊樵兊谋淼臄?shù)據(jù)總量也很小，最小的表甚至只有幾千行；時間達到了幾百ms，甚至1s；數(shù)據(jù)庫的slow log里沒有記錄這類SQL。下圖為CAT相關(guān)監(jiān)控數(shù)據(jù)的樣本，以xxx-service這個service為例：99line的監(jiān)控數(shù)據(jù)，有很多SQL的返回時間超過100ms以

3、上。SQL的絕對數(shù)量在2016年9月6日當天為 ：3788。具體到某個SQL，甚至達到了929ms。FB_Coach的表結(jié)構(gòu)如下：可看到最多641條記錄，還有聯(lián)合索引。概要分析要想定位到原因，必須通過排除法找到該SQL到底慢在哪個階段，這樣才能縮小范圍。接下來我們來分析慢SQL的花費時間組成。從下圖可看出，時間主要由3部分組成：App Server：發(fā)出SQL請求的時間，接收返回結(jié)果的時間網(wǎng)絡：SQL請求包及查詢結(jié)果在網(wǎng)絡上花費的時間MySQL Server：發(fā)出SQL到查詢結(jié)果整個過程花費的時間我們可以通過抓包工具獲取每個階段花費的時間，從而定位到底慢在哪個階段。問題解決思路迭代思路1：確認

4、哪個過程花費的時間最多方法：分別在APP Server與MySQL Server部署TcpDump抓包工具，得到數(shù)據(jù)包在4個監(jiān)測點的“到達時間”。為了方便，把如下4個Wireshark分析結(jié)果（對TcpDump抓取日志分析）按4個方位標注：APP Server 發(fā)出SQL（左上）MySQL Server 收到SQL（右上）MySQL Server 將查詢發(fā)出（右下）APP Server 收到查詢結(jié)果（左下）從數(shù)據(jù)可以準確的看出時間主要花費在MySQL內(nèi)部，具體時間為22.569285000-21.962634000=0.6066509999999994(秒)，約為606ms。抓包結(jié)果：慢在My

5、SQL Server端。思路2：一條SQL進入MySQL Server到查詢結(jié)果輸出分哪些階段？方法：將MySQL內(nèi)部對SQL查詢的流程進行梳理，采用排除法定位問題。要把經(jīng)典圖拿出來說事了，以下基礎知識主要來自于高性能MySQL，“拿來主義”一下。首先可以看到，MySQL主要有三個組件：連接/線程處理、MySQL Server層、存儲引擎層。最上層主要進行連接處理、授權(quán)認證、安全等；第二層包括查詢解析、分析、優(yōu)化（這三個是解決問題最關(guān)心的）、緩存管理、所有內(nèi)置函數(shù)、存儲過程、觸發(fā)器、視圖，似乎扯得有點遠；第三層包含了主要的存儲引擎層，MySQL Server層（第二層）通過“存儲引擎API”向

6、存儲引擎層存儲和提取數(shù)據(jù)，此層主要是數(shù)據(jù)存儲相關(guān)。接下來通過一個客戶端請求查詢數(shù)據(jù)，看看MySQL主要做哪些工作吧。每個客戶端（可能理解為App負責連接數(shù)據(jù)庫的組件，我們叫DAL）連接到MySQL服務器進程后會擁有一個線程，這個連接的所有查詢都會在該線程中去執(zhí)行，同時服務器會緩存線程，以減少創(chuàng)建或銷毀線程的開銷和頻繁的上下文切換。當客戶連接到MySQL服務器時，服務器會分配一個線程，之后進行權(quán)限認證，認證通過后，MySQL就開始解析該SQL查詢，并創(chuàng)建內(nèi)部數(shù)據(jù)數(shù)據(jù)結(jié)構(gòu)（解析樹），然后對其各種優(yōu)化，最后調(diào)用存儲引擎API獲取或存儲需要的數(shù)據(jù)，最后將查詢結(jié)果返回給客戶端。通過以上“背書”，我們大概

7、了解了一個SQL請求的執(zhí)行過程，那到底慢在哪個階段呢？通過“慢SQL特點”的第4條知道，“數(shù)據(jù)庫的slow log里沒有記錄這類SQL”，那慢SQL發(fā)生的階段就可以排除了。MySQL slow log是記錄SQL執(zhí)行過程花費的時間，記錄的時間從“SQL解析”到“存儲引擎”返回數(shù)據(jù)整個過程，所以可以排除該SQL是慢在第二層和第三層，那么只能是把時間花費在第一層了？和線程相關(guān)？結(jié)果：很可能慢在MySQL線程管理上。思路3：是創(chuàng)建線程慢？thread cache不夠用，需要頻繁的創(chuàng)建線程？方法：查看當時數(shù)據(jù)庫的狀態(tài)值可以看到，當時空閑的thread很多，監(jiān)控圖也沒有抖動，所以并沒有頻繁地創(chuàng)建線程。慢

8、SQL產(chǎn)生的時間點，空閑的thread很多，并沒有進行大量的線程創(chuàng)建。那問題到底出現(xiàn)在和線程相關(guān)的哪個環(huán)節(jié)呢？ 先把所有和thread相關(guān)的參數(shù)列出來。thread_cache_sizethread_concurrencythread_handlingthread_pool_high_prio_modethread_pool_high_prio_ticketsthread_pool_idle_timeoutthread_pool_max_threadsthread_pool_oversubscribethread_pool_sizethread_pool_stall_limitthread_s

9、tackthread_statistics一眼看過去，大部分是和Thread-Pool相關(guān)。同時意識到這些問題是隨著升級到MySQL 5.6產(chǎn)生的，5.6引入了Thread-Pool功能。結(jié)果：看來MySQL5.6的Thread-Pool有很大嫌疑了。思路4：關(guān)閉MySQL 5.6的Thread-Pool，確認一下問題方法：調(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的問題。

10、以下是具體的證據(jù)，以xxx-service這個service為例：打開Thread-Pool功能（2016年9月6日當天數(shù)據(jù)）。99line占比：有好多超過100ms的SQL。慢SQL數(shù)量：3788關(guān)閉Thread-Pool功能后（2016年9月13日當天數(shù)據(jù)）。99line占比：已經(jīng)看不到超過100ms的sql了，都在10ms以內(nèi)。慢SQL數(shù)量：818那么關(guān)閉Thread-Pool ?答案很顯然，不能！Thread-Pool是MySQL5.6重要的功能，能夠保證MySQL數(shù)據(jù)庫高并發(fā)下的性能穩(wěn)定。思路5：調(diào)優(yōu)Thread-Pool相關(guān)參數(shù)方法：深入了解Thread-Pool的工作原理，查找可

11、能產(chǎn)生慢SQL的參數(shù)。結(jié)果：找到了相關(guān)參數(shù)（thread_pool_stall_limit），并且效果明顯，慢SQL數(shù)量從最初的3788減少到63，幾乎全部消滅掉。以xxx-service這個service為例，調(diào)整后的效果，2016年9月20日當天的數(shù)據(jù)：99line占比：慢SQL數(shù)量：63ok，效果有了，總結(jié)一下。問題分析1、基本原理沒有引入Thread-Pool前，MySQL使用的是one thread per connection，一旦connection增加到一定程度，MySQL的性能將急劇下降甚至被壓跨。引入Thread-Pool后將會解決上述問題,同時會減少MySQL內(nèi)部的線程數(shù)

12、（節(jié)省內(nèi)存）及頻繁創(chuàng)建線程的開銷（節(jié)省CPU)。2、Thread-Pool是如何工作的？在MySQL內(nèi)部有一個專用的thread用來監(jiān)聽數(shù)據(jù)庫連接請求，當一個新的請求過來，如果采用以前的模型（one-thread-per-connection），main listener（這是主線程中的listener，為了避免與thread group 中的listener混淆，我們稱之為“Main listener”）將從thread cache中取出1個thread或創(chuàng)建1個新的thead立即處理該連接請求，由該thread完成該連接的整個生命周期；而如果采用Thread-Pool模型，這個連接請求將會

13、被隨機放到一個thread group（thread pool由多個thread group 組成）的隊列中，之后該thread group中worker thread從隊列中取出并建立連接，一旦連接建立，該連接對應的socket句柄將與該thread group中的listener關(guān)聯(lián)起來，之后該連接將在該thread group中完成它的生命周期。接下來我們來說說Thread Group 。Thread Group是Thread-Pool的核心組件，所有的操作都是發(fā)生在thread group。Thread-Pool由多個(數(shù)量由參數(shù)thread_pool_size來決定，默認等于cpu個

14、數(shù))thrad group組成。一個連接請求被隨機地綁定到一個thread group，每個thread group獨立工作，并且占用一個核的CPU。所以thread group都會最大限度地保持一個thread處于ACTIVE狀態(tài)，并且最好只有一個，因為太多就有可能壓跨數(shù)據(jù)庫。Thread Group中的thread一般有4個狀態(tài)：TP_STATE_LISTENERTP_STATE_IDLETP_STATE_ACTIVETP_STATE_WAITING當一個線程作為listener運行時就處于“TP_STATE_LISTENER”，它通過epoll的方式監(jiān)聽聯(lián)接到該Thread Group的

15、所有連接，當一個socket就緒后，listener將決定是否喚醒一個thread或自己處理該socket。此時如果Thread Group的隊列為空，它將自己處理該socket并將狀態(tài)更改為“ACTIVE”，之后該thread 在MySQL Server內(nèi)部處理“工作”，當該線程遇到鎖或異步IO(比如將數(shù)據(jù)頁讀入到buffer pool)這些wait時，該thread將通過回調(diào)函數(shù)的方式告訴thread pool，讓其把自己標記為“WAITING”狀態(tài)。此時，假設隊列中有了新的socket準備就緒，是立即創(chuàng)建新的線程還是等待剛才的線程執(zhí)行結(jié)束呢？由于Thread-Pool最初設計的目標是保持

16、一定數(shù)量的線程處于“ACTIVE”狀態(tài)，具體的實現(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)的線程個數(shù)是1。很顯然，當前thread group中有一個處于WAITING狀態(tài)的thread了，如果再啟用一個新的線程并且處于ACTIVE狀態(tài)，剛才的線程由WAITING變?yōu)锳CTIVE狀態(tài)時，此時將會有2個“ACTIVE”狀態(tài)的線程，和最初的目標似乎相背，但顯然也不能讓后續(xù)就緒的socket一直等待下去，那應該怎么處理？那么此時

17、需要一個權(quán)衡了，提供了這樣的一個方法：對正在ACTIVE或WAITING狀態(tài)的線程啟用一個計數(shù)器，超過計數(shù)器后將該thread標記為stalled，然后thread group創(chuàng)建新的thread或喚醒sleep的thread處理新的sokcet，這樣將是一個很好的權(quán)衡。超時時間該參數(shù)thread_pool_stall_limit來決定，默認是500ms。如果一個線程無事可做，它將保持空閑狀態(tài)（TP_STATE_WAITING）一定時間（thread_pool_idle_timeout參數(shù)決定，默認是60秒）后“自殺”。3、和我們遇到的具體問題相關(guān)的點假設上文提到的由“ACTIVE”轉(zhuǎn)化為“W

18、AITING”狀態(tài)的線程（標記為“線程A”）所執(zhí)行的“SQL可能是一個標準的慢SQL（命名為SQLA，執(zhí)行時間較長），那么后續(xù)有連接請求分配到了同一個thread group,那么新連接的SQL（命名SQLB）需要等待線程A結(jié)束；如果SQLA執(zhí)行時間超過500ms，該thread group創(chuàng)建新的worker線程來處理SQLB。不管哪種情況，SQLB都會在線程等待上花費很多時間，此時SQLB就是CAT監(jiān)控系統(tǒng)上看到的慢SQL。又因為SQLA不一定都是慢SQL，所以SQLB也不是每次在線程等待上花費較多的時間，這就吻合我們看到的現(xiàn)象“一定比例的慢SQL”。解決方法找到問題了，那么解決辦法就簡單了。調(diào)整thread_pool_stall_limit=10，這樣就強迫被SQLA更快被標記為stalled，然后創(chuàng)建新的線程來處理SQLB。帶來的價值以xxx-service為例，減少了98.3%的慢SQL，效果很明顯；該問題的解決讓百個產(chǎn)品線從中受益，業(yè)務可用性超過了99.99%。狀態(tài)慢SQL數(shù)量備注優(yōu)化前3788優(yōu)化操作：關(guān)閉thread pool818優(yōu)化操作：調(diào)整參數(shù)thread_pool_stall_limit=1063總結(jié)首先我們分析了慢SQL的特點及該SQL花費的時間組成，通過“時間花費在