最近出現(xiàn)多次由于上層組件異常導(dǎo)致DB雪崩的情況岂津，將部分監(jiān)控DB啟用了線程池功能。在使用線程池的過程中不斷的深入學(xué)習(xí)悦即，期間也遇到了不少問題吮成。本文就來詳細(xì)講述一下MySQL線程池相關(guān)的知識(shí)，以幫助廣大DBA快速了解MySQL的線程池機(jī)制辜梳，快速配置MySQL的線程池以及了解里面存在的一些坑粱甫。 其實(shí)，我想說的是作瞄，了解和使用MySQL線程池茶宵，看這篇文章就夠了。
一宗挥、為什么要使用MySQL線程池
在介紹為什么要使用線程池之前乌庶，我們都知道，隨著DB訪問量越來越大契耿，DB的響應(yīng)時(shí)間也會(huì)隨之越來越大瞒大，如下圖：
而DB的訪問大到一定程度的時(shí)候，DB的吞吐量也會(huì)出現(xiàn)下降搪桂，并且會(huì)越來越差透敌，如下圖所示：
那么是否有什么方式，能讓對(duì)著DB的訪問量越來越大踢械，DB始終能表現(xiàn)出最佳的性能酗电？類似下圖的表現(xiàn)：
答案就是今天要重點(diǎn)介紹的線程池功能，總結(jié)一下内列，使用線程池的理由有如下兩個(gè)：
1撵术、減少線程重復(fù)創(chuàng)建與銷毀部分的開銷，提高性能
? ? 線程池技術(shù)通過預(yù)先創(chuàng)建一定數(shù)量的線程德绿，在監(jiān)聽到有新的請(qǐng)求的時(shí)候荷荤，線程池直接從現(xiàn)有的線程中分配一個(gè)線程來提供服務(wù)，服務(wù)結(jié)束后這個(gè)線程不會(huì)直接銷毀移稳，而是又去處理其他的請(qǐng)求蕴纳。這樣就避免了線程和內(nèi)存對(duì)象頻繁創(chuàng)建和銷毀，減少了上下文切換个粱，提高了資源利用率古毛，從而在一定程度上提高了系統(tǒng)的性能和穩(wěn)定性。
2都许、對(duì)系統(tǒng)起到保護(hù)作用
? ? 線程池技術(shù)限制了并發(fā)線程數(shù)稻薇，相當(dāng)于限制了MySQL的runing線程數(shù)，無(wú)論系統(tǒng)目前有多少連接或者請(qǐng)求胶征，超過最大設(shè)置的線程數(shù)的都需要排隊(duì)塞椎，讓系統(tǒng)保持高性能水平。從而防止DB出現(xiàn)雪崩睛低，對(duì)底層DB起到保護(hù)作用案狠。
? ? 可能有人會(huì)問，使用連接池是否也能達(dá)到類似的效果钱雷？
? ? 可能有的DBA會(huì)把線程池和連接池混淆骂铁，其實(shí)兩者是有很大區(qū)別的，連接池一般在客戶端設(shè)置罩抗，而線程池是在DB服務(wù)器上配置拉庵；另外連接池可以取到避免了連接頻繁創(chuàng)建和銷毀，但是無(wú)法取到控制MySQL活動(dòng)線程數(shù)的目標(biāo)套蒂，在高并發(fā)場(chǎng)景下钞支，無(wú)法取到保護(hù)DB的作用。比較好的方式是將連接池和線程池結(jié)合起來使用操刀。
二伸辟、MySQL線程池介紹
（一）、MySQL線程池簡(jiǎn)介
為了解決one-thread-per-connection(每個(gè)連接一個(gè)線程)存在的頻繁創(chuàng)建和銷毀大量線程以及高并發(fā)情況下DB雪崩的問題馍刮，實(shí)現(xiàn)DB在高并發(fā)環(huán)境依然能保持較高的性能信夫。Oracle和MariaDB都推出了ThreadPool方案，目前Oracle的threadpool實(shí)現(xiàn)為plugin方式卡啰，并且只添加到在Enterprise版本中静稻，Percona移植了MariaDB的threadpool功能，并做了進(jìn)一步的優(yōu)化匈辱。本文的環(huán)境是基于Percona MySQL 5.7版本振湾。
（二）、MySQL線程池架構(gòu)
MySQL的threadpool（線程池）被劃分為多個(gè)group（組）亡脸，每個(gè)組又有對(duì)應(yīng)的工作線程押搪，整體的工作邏輯還是比較復(fù)雜树酪，下面我試圖通過簡(jiǎn)單的方式來介紹MySQL線程池的工作原理。
1大州、架構(gòu)圖
首先來看看threadpool的架構(gòu)圖
2续语、Thread Pool的組成
? ? 從架構(gòu)圖中可以看到Thread Pool由一個(gè)Timer線程和多個(gè)Thread Group組成，而每個(gè)Thread Group又由兩個(gè)隊(duì)列厦画、一個(gè)listener線程和多個(gè)worker線程構(gòu)成疮茄。下面分別來介紹每個(gè)各個(gè)部分的作用：
a、隊(duì)列（高優(yōu)先級(jí)隊(duì)列和低優(yōu)先級(jí)隊(duì)列）
? ? 用來存放待執(zhí)行的IO任務(wù)根暑，分為高優(yōu)先級(jí)隊(duì)列和低優(yōu)先級(jí)隊(duì)列力试，高優(yōu)先級(jí)隊(duì)列的任務(wù)會(huì)優(yōu)先被處理。
?? ?什么任務(wù)會(huì)放在高優(yōu)先級(jí)隊(duì)列呢排嫌？
? ? 事務(wù)中的語(yǔ)句會(huì)放到高優(yōu)先級(jí)隊(duì)列中畸裳，比如一個(gè)事務(wù)中有兩個(gè)update的SQL，有1個(gè)已經(jīng)執(zhí)行淳地，那么另外一個(gè)update的任務(wù)就會(huì)放在高優(yōu)先級(jí)中躯畴。這里需要注意，如果是非事務(wù)引擎薇芝，或者開啟了autocommit的事務(wù)引擎蓬抄，都會(huì)放到低優(yōu)先級(jí)隊(duì)列中。
?? ?還有一種情況會(huì)將任務(wù)放到高優(yōu)先級(jí)隊(duì)列中夯到，如果語(yǔ)句在低優(yōu)先級(jí)隊(duì)列停留太久嚷缭，該語(yǔ)句也會(huì)移到高優(yōu)先級(jí)隊(duì)列中，防止餓死的問題耍贾。xxxxxxxxx
b阅爽、listener線程
? ? listener線程監(jiān)聽該線程group的語(yǔ)句，并確定是自己轉(zhuǎn)變成worker線程立即執(zhí)行對(duì)應(yīng)的語(yǔ)句還是放到隊(duì)列中荐开，判斷的標(biāo)準(zhǔn)是看隊(duì)列中是否有待執(zhí)行的語(yǔ)句付翁。如果隊(duì)列中待執(zhí)行的語(yǔ)句數(shù)量為0，而listener線程轉(zhuǎn)換成worker線程晃听，并立即執(zhí)行對(duì)應(yīng)的語(yǔ)句百侧。如果隊(duì)列中待執(zhí)行的語(yǔ)句數(shù)量不為0，則認(rèn)為任務(wù)比較多能扒，將語(yǔ)句放入隊(duì)列中佣渴，讓其他的線程來處理。這里的機(jī)制是為了減少線程的創(chuàng)建初斑，因?yàn)橐话鉙QL執(zhí)行都非承寥螅快。
c见秤、worker線程
? ? worker線程是真正干活的線程
d砂竖、Timer線程
? ? Timer線程是用來周期性檢查group是否處于處于阻塞狀態(tài)真椿，當(dāng)出現(xiàn)阻塞的時(shí)候，會(huì)通過喚醒線程或者新建線程來解決乎澄。具體的檢測(cè)方法為突硝，通過queue_event_count的值和IO任務(wù)隊(duì)列是否為空來判斷線程組是否為阻塞狀態(tài)。每次worker線程檢查隊(duì)列中任務(wù)的時(shí)候三圆，queue_event_count會(huì)+1狞换，每次Timer檢查完group是否阻塞的時(shí)候會(huì)將queue_event_count清0避咆，如果檢查的時(shí)候任務(wù)隊(duì)列不為空舟肉，而queue_event_count為0，則說明任務(wù)隊(duì)列沒有被正常處理查库，此時(shí)該group出現(xiàn)了阻塞路媚，Timer線程會(huì)喚醒worker線程或者新建一個(gè)wokrer線程來處理隊(duì)列中的任務(wù)，防止group長(zhǎng)時(shí)間被阻塞樊销。
3整慎、Thread Pool的是如何運(yùn)作的？
?下面描述極簡(jiǎn)的Thread Pool運(yùn)作围苫，只是簡(jiǎn)單描述裤园，省略了大量的復(fù)雜邏輯，請(qǐng)不要挑刺剂府，~~
a拧揽、請(qǐng)求連接到MySQL，根據(jù)threadid%thread_pool_size確定落在哪個(gè)group
b腺占、group中的listener線程監(jiān)聽到所在的group有新的請(qǐng)求以后淤袜，檢查隊(duì)列中是否有請(qǐng)求還未處理，如果沒有衰伯，則自己轉(zhuǎn)換為worker線程立即處理該請(qǐng)求铡羡，如果隊(duì)列中還有未處理的請(qǐng)求，則將對(duì)應(yīng)請(qǐng)求放到隊(duì)列中意鲸，讓其他的線程處理烦周。
c、group中的thread線程檢查隊(duì)列的請(qǐng)求怎顾，如果隊(duì)列中有請(qǐng)求论矾，則進(jìn)行處理，如果沒有請(qǐng)求杆勇，則休眠贪壳，一直沒有被喚醒，超過thread_pool_idle_timeout后就自動(dòng)退出蚜退。線程結(jié)束闰靴。當(dāng)然彪笼，獲取請(qǐng)求之前會(huì)先檢查group中的running線程數(shù)是否超過thread_pool_oversubscribe+1，如果超過也會(huì)休眠蚂且。
d配猫、timer線程定期檢查各個(gè)group是否有阻塞，如果有杏死，就對(duì)wokrer線程進(jìn)行喚醒或者創(chuàng)建一個(gè)新的worker線程泵肄。
4、Thread Pool的分配機(jī)制
?? ?線程池會(huì)根據(jù)參數(shù)thread_pool_size的大小分成若干的group淑翼，每個(gè)group各自維護(hù)客戶端發(fā)起的連接腐巢，當(dāng)客戶端發(fā)起連接到MySQL的時(shí)候，MySQL會(huì)跟進(jìn)連接的線程id（thread_id）對(duì)thread_pool_size進(jìn)行取模玄括，從而落到對(duì)應(yīng)的group冯丙。thread_pool_oversubscribe參數(shù)控制每個(gè)group的最大并發(fā)線程數(shù)，每個(gè)group的最大并發(fā)線程數(shù)為thread_pool_oversubscribe+1個(gè)遭京，若對(duì)應(yīng)的group達(dá)到了最大的并發(fā)線程數(shù)胃惜，則對(duì)應(yīng)的連接就需要等待。這個(gè)分配機(jī)制在某個(gè)group中有多個(gè)慢SQL的場(chǎng)景下會(huì)導(dǎo)致普通的SQL運(yùn)行時(shí)間很長(zhǎng)哪雕，這個(gè)問題在后面的會(huì)做詳細(xì)描述船殉。
（三）、MySQL線程池參數(shù)說明
關(guān)于線程池參數(shù)不多斯嚎，使用show variables like 'thread%'可以看到如下圖的參數(shù)利虫，下面就一個(gè)一個(gè)來解析：
thread_handling
該參數(shù)是配置線程模型，默認(rèn)情況是one-thread-per-connection孝扛，也就是不啟用線程池列吼。將該參數(shù)設(shè)置為pool-of-threads即啟用了線程池
thread_pool_size
該參數(shù)是設(shè)置線程池的Group的數(shù)量，默認(rèn)為系統(tǒng)CPU的個(gè)數(shù)苦始，充分利用CPU資源
thread_pool_oversubscribe
該參數(shù)設(shè)置group中的最大線程數(shù)寞钥，每個(gè)group的最大線程數(shù)為thread_pool_oversubscribe+1，注意listener線程不包含在內(nèi)陌选。
thread_pool_high_prio_mode
高優(yōu)先級(jí)隊(duì)列的控制參數(shù)理郑，有三個(gè)值（transactions/statements/none），默認(rèn)是transactions咨油，三個(gè)值的含義如下：
transactions：對(duì)于已經(jīng)啟動(dòng)事務(wù)的語(yǔ)句放到高優(yōu)先級(jí)隊(duì)列中您炉，不過還取決于后面的thread_pool_high_prio_tickets參數(shù)
statements：這個(gè)模式所有的語(yǔ)句都會(huì)放到高優(yōu)先級(jí)隊(duì)列中，不會(huì)使用到低優(yōu)先級(jí)隊(duì)列
none：這個(gè)模式不使用高優(yōu)先級(jí)隊(duì)列
thread_pool_high_prio_tickets
該參數(shù)控制每個(gè)連接最多語(yǔ)序多少次被放入高優(yōu)先級(jí)隊(duì)列中役电，默認(rèn)為4294967295赚爵，注意這個(gè)參數(shù)只有在thread_pool_high_prio_mode為transactions的時(shí)候才有效果。
thread_pool_idle_timeout
worker線程最大空閑時(shí)間，默認(rèn)為60秒冀膝，超過限制后會(huì)退出
thread_pool_max_threads
該參數(shù)用來限制線程池最大的線程數(shù)唁奢，超過該限制后將無(wú)法再創(chuàng)建更多的線程，默認(rèn)為100000
thread_pool_stall_limit
該參數(shù)設(shè)置timer線程的檢測(cè)group是否異常的時(shí)間間隔窝剖，默認(rèn)為500ms
三麻掸、MySQL線程池的使用
線程池的使用比較簡(jiǎn)單，只需要添加配置后重啟實(shí)例即可
具體配置如下：
#thread pool
thread_handling=pool-of-threads
thread_pool_oversubscribe=3
thread_pool_size=24
performance_schema=off
#extra connection
extra_max_connections = 8
extra_port = 33333
備注：其他參數(shù)默認(rèn)即可
以上具體的參數(shù)在前面已經(jīng)有做詳細(xì)說明赐纱。下面是配置中需要注意的兩個(gè)點(diǎn)：
１脊奋、只所以添加performance_schema=off，是由于測(cè)試過程中發(fā)現(xiàn)Thread pool和PS同時(shí)開啟的時(shí)候會(huì)出現(xiàn)內(nèi)存泄漏疙描，在后面遇到的問題部分有詳細(xì)描述诚隙；
2、添加extra connection是防止線程池滿的情況下無(wú)法登錄MySQL淫痰，因此特意已用管理端口最楷，以備緊急的情況下使用整份；
重啟實(shí)例后待错，可以通過show variables like '%thread%';來查看配置的參數(shù)是否生效。
四烈评、使用MySQL線程池遇到的問題
在使用線程池的過程中火俄，遇到了幾個(gè)問題，也順便總結(jié)一下：
（一）內(nèi)存泄漏問題
DB啟用線程池后讲冠，內(nèi)存飆升了8G左右瓜客，如下圖：
不但啟用線程池后內(nèi)存飆升了8G左右，而且內(nèi)存還在持續(xù)增長(zhǎng)竿开，很明顯啟用線程池后存在內(nèi)存泄漏了谱仪。
這個(gè)問題在網(wǎng)上也有不少的人遇到，確認(rèn)是percona的bug導(dǎo)致（https://jira.percona.com/browse/PS-3734）否彩，只有開啟Performance_Schema和ThreadPool的時(shí)候才會(huì)出現(xiàn)疯攒，解決辦法是關(guān)閉Performance_Schema即可，具體操作方法是在配置文件添加performance_schema=off列荔，然后重啟MySQL就OK敬尺。下面是關(guān)閉PS后的內(nèi)存使用情況對(duì)比：
備注：
目前Percona server?5.7.21-20版本已經(jīng)修復(fù)了線程池和PS同時(shí)打開內(nèi)存泄漏的問題，從我測(cè)試的情況來看問題也得到了解決贴浙，大家也可以直接使用Percona server?5.7.21-20的版本砂吞，如下圖
（二）撥測(cè)異常問題
啟用線程池以后，相當(dāng)于限制了MySQL的并發(fā)線程數(shù)崎溃，當(dāng)達(dá)到最大線程數(shù)的時(shí)候蜻直，其他的線程需要等待，新連接也會(huì)卡在連接驗(yàn)證那一步，這時(shí)候會(huì)造成撥測(cè)程序連接MySQL超時(shí)概而，撥測(cè)返回錯(cuò)誤如下：
撥測(cè)程序連接實(shí)例超時(shí)后唤殴，就會(huì)認(rèn)為master已經(jīng)出現(xiàn)問題，極端情況下到腥，重試多次都有異常后朵逝，就啟動(dòng)自動(dòng)切換的操作，將業(yè)務(wù)切換到從機(jī)乡范。
這種情況有兩種解決辦法：
1配名、啟用MySQL的旁路管理端口，監(jiān)控和高可用相關(guān)直接使用MySQL的旁路管理端口
? ? 具體做法為是在my.cnf中添加如下配置后重啟晋辆，就可以通過旁路端口登錄MySQL了渠脉，不受線程池最大線程數(shù)的影響：
? ? extra_max_connections = 8
? ? extra_port = 33333
? ? 備注：建議啟用線程池后，這個(gè)也添加上瓶佳，方便緊急情況下進(jìn)行故障處理芋膘。
2、修改高可用探測(cè)腳本霸饲，將達(dá)到線程池最大活動(dòng)線程數(shù)返回的錯(cuò)誤做異常處理为朋，類似于當(dāng)作超過最大連接數(shù)的場(chǎng)景（備注：超過最大連接數(shù)只告警，不進(jìn)行自動(dòng)切換）
（三）慢SQL引入的問題
隨著對(duì)撥測(cè)超時(shí)的問題的深入分析厚脉，線程池滿只是監(jiān)控?fù)軠y(cè)出現(xiàn)超時(shí)的其中一種情況习寸，還有一種情況是線程池并沒有滿，線上的兩個(gè)配置：
thread_pool_oversubscribe=3
thread_pool_size=24
按照上面的兩個(gè)配置來計(jì)算的話傻工，總共能并發(fā)運(yùn)行24x(3+1)=96霞溪，但是根據(jù)多次問題的追中，發(fā)現(xiàn)有多次線程池并沒有達(dá)到96中捆，也就是說整體的線程池并沒有滿鸯匹。那會(huì)是什么問題導(dǎo)致?lián)軠y(cè)失敗呢？
鑒于線程池的結(jié)構(gòu)和分配機(jī)制泄伪，通過前面線程池部分的描述殴蓬，大家都知道了在內(nèi)部是將線程池分成一個(gè)一個(gè)的group，我們線上配置了24個(gè)group臂容，而線程池的分配機(jī)制是對(duì)Threadid進(jìn)行取模科雳，然后確定該線程是落在哪個(gè)group。出現(xiàn)超時(shí)的時(shí)候脓杉，有很多的load線程到導(dǎo)入數(shù)據(jù)糟秘。也就是說那個(gè)時(shí)候有部分線程比較慢的情況。那么會(huì)不會(huì)是某個(gè)group的線程滿了球散，從而導(dǎo)致新分配的線程等待尿赚？
有了這個(gè)猜想以后，接下來就是來驗(yàn)證這個(gè)問題。驗(yàn)證分為兩步：
1凌净、抓取線上運(yùn)行的processlist悲龟，然后對(duì)threadid取模，看看是否有多個(gè)load線程落在統(tǒng)一個(gè)group的情況
2冰寻、在測(cè)試環(huán)境模擬這種場(chǎng)景须教，看看是否符合預(yù)期
線上場(chǎng)景分析：
先來看線上的場(chǎng)景，通過抓取撥測(cè)超時(shí)時(shí)間點(diǎn)的processlist斩芭，找出當(dāng)時(shí)正在load的線程轻腺，根據(jù)threadid進(jìn)行去模，并進(jìn)行匯總統(tǒng)計(jì)后划乖，得出如下結(jié)果：
可以看出贬养，當(dāng)時(shí)第4和第7個(gè)group的請(qǐng)求個(gè)數(shù)都超過了4個(gè)，說明是單個(gè)group滿導(dǎo)致的撥測(cè)異常琴庵，當(dāng)然误算，也會(huì)導(dǎo)致部分運(yùn)行很快的SQL變慢。
測(cè)試環(huán)境模擬場(chǎng)景分析：
為了構(gòu)建快速重現(xiàn)環(huán)境迷殿，我將參數(shù)調(diào)整如下：
thread_pool_oversubscribe=1
thread_pool_size=2
通過上面參數(shù)的調(diào)整儿礼，可以計(jì)算出最大并發(fā)線程為2x(1+1)=4，如下圖贪庙，當(dāng)活動(dòng)線程數(shù)超過4個(gè)后蜘犁，其他的線程就必須等待：
我模擬線上環(huán)境的方法為翰苫，開啟1個(gè)線程的慢SQL止邮，這個(gè)時(shí)候，測(cè)試環(huán)境的線程池情況如下：
按照之前的推測(cè)奏窑，這個(gè)時(shí)候Group1的處理能力相當(dāng)于Group2的處理能力的50%导披，如果之前的推論是正確的，那么分配在Group1上的線程就會(huì)出現(xiàn)阻塞埃唯。比如此時(shí)來了20個(gè)線程請(qǐng)求撩匕，按照線程池的分配原則，此時(shí)Group1和Group2都會(huì)分到10個(gè)線程請(qǐng)求墨叛。稼穡所有的線程請(qǐng)求耗時(shí)都是一樣的止毕，那么分配到Group1上的線程請(qǐng)求整體處理時(shí)間應(yīng)該是分配到Group2上整體處理時(shí)間的2倍。
我使用腳本漠趁，并發(fā)發(fā)起12個(gè)線程請(qǐng)求扁凛，每個(gè)線程請(qǐng)求都運(yùn)行select sleep(2)，那么在Group1和Group2都空閑的情況下闯传，運(yùn)行情況如下：
2018-03-18-20:23:53
2018-03-18-20:23:53
2018-03-18-20:23:53
2018-03-18-20:23:53
2018-03-18-20:23:55
2018-03-18-20:23:55
2018-03-18-20:23:55
2018-03-18-20:23:55
2018-03-18-20:23:57
2018-03-18-20:23:57
2018-03-18-20:23:57
2018-03-18-20:23:57
每次4個(gè)線程谨朝，總共運(yùn)行了6秒
接下來在Group1被1個(gè)長(zhǎng)時(shí)間運(yùn)行的線程沾滿以后，看看測(cè)試結(jié)果是怎么樣的
2018-03-18-20:24:35
2018-03-18-20:24:35
2018-03-18-20:24:35
2018-03-18-20:24:37
2018-03-18-20:24:37
2018-03-18-20:24:37
2018-03-18-20:24:39
2018-03-18-20:24:39
2018-03-18-20:24:39
2018-03-18-20:24:41
2018-03-18-20:24:43
2018-03-18-20:24:45
從上面的結(jié)果中可以看出，在沒有阻塞的時(shí)候字币，每次都是4個(gè)線程则披，而后面有1個(gè)線程長(zhǎng)時(shí)間運(yùn)行的時(shí)候，就會(huì)出現(xiàn)那個(gè)長(zhǎng)時(shí)間線程對(duì)應(yīng)的group出現(xiàn)排隊(duì)的情況洗出，最后雖然有3個(gè)空閑的線程士复，但是卻是只有1個(gè)線程在處理（粗體部分結(jié)果）。
解決把法有兩個(gè)：
1翩活、將thread_pool_oversubscribe適當(dāng)調(diào)大判没，這個(gè)辦法只能緩解類似問題，無(wú)法根治隅茎；
2澄峰、找到慢的SQL，解決慢的問題辟犀；
五俏竞、參考文獻(xiàn)：
https://www.percona.com/doc/percona-server/LATEST/performance/threadpool.html
https://www.percona.com/blog/2013/03/16/simcity-outages-traffic-control-and-thread-pool-for-mysql/
http://www.cnblogs.com/cchust/p/4510039.html
