Mysql線程池系列一(Thread pool FAQ)

首先介紹什么是mysql thread pool,干什么用的？
使用線程池主要可以達(dá)到以下兩個(gè)目的：
1、在大并發(fā)的時(shí)候,性能不會(huì)因?yàn)檫^(guò)載而迅速下降。
2、減少性能抖動(dòng)

thread pool的工作原理？
線程池使用分而治之的方法來(lái)限制和平衡并發(fā)性。與默認(rèn)的thread_hand ling 不同,線程池將連接和線程劃分開(kāi),所以連接數(shù)量和執(zhí)行語(yǔ)句的線程數(shù)不再是固定的關(guān)系,線程池可以通過(guò)
配置線程組來(lái)管理連接,然后再根據(jù)每個(gè)語(yǔ)句的關(guān)鍵字來(lái)確定是優(yōu)先執(zhí)行或者排隊(duì)執(zhí)行。

mysql thread pool和client端的connection pool的不同之處？
client段的connection pool：連接池主要用來(lái)管理客戶(hù)端的連接,避免重復(fù)的連接/斷開(kāi)操作,而是將空閑的連接緩存起來(lái),可以復(fù)用。從而減少了連接mysql server/斷開(kāi)mysql server的開(kāi)銷(xiāo)與成本,從而提升性能。
但是mysql的connection pool不能獲取mysql server的查詢(xún)處理能力以及當(dāng)前的負(fù)載情況。
thread pool:線程池的操作是在mysql server端,并且設(shè)計(jì)就是用來(lái)管理當(dāng)前并發(fā)的連接和查詢(xún).

thread pool到底能夠提升多少性能?
根據(jù)Oracle Mysql官方的性能測(cè)試
在并發(fā)達(dá)到128個(gè)連接以后.沒(méi)有線程池的Mysql性能會(huì)迅速降低。使用線程池以后,性能不會(huì)出現(xiàn)波動(dòng),會(huì)一直保持在較好的狀態(tài)運(yùn)行。
在讀寫(xiě)模式下,128個(gè)連接以后,有線程池的Mysql比沒(méi)有線程池的Mysql性能高出60倍。
在只讀模式下,512個(gè)連接以后,有線程池的Mysql比沒(méi)有線程池的Mysql性能高出18倍。

什么時(shí)候可以考慮使用thread_pool？
* show global status like ‘%threads_running%’;的值是mysql server當(dāng)前并發(fā)執(zhí)行語(yǔ)句的數(shù)量軌跡,如果這個(gè)值一直保持在40左右的區(qū)間,那么可以考慮使用thread pool。
*如果你使用了innodb_thread_concurrency參數(shù)來(lái)控制并發(fā)的事物量,那么使用線程池將會(huì)獲得更好的效果。
*如果你的工作是有很多短連接組成的,那么使用線程池是有益的。

說(shuō)一下oracle mysql thread pool插件的限制：
1、Oracle Mysql enterprise 6.10版本添加的,也就是說(shuō)小于這個(gè)版本的企業(yè)版不支持,目前所有的oracle mysql community版本也不支持。
2、如果是windows的系統(tǒng),需要是vista或者以后的版本,如果是 linux ,需要2.6.9以后的內(nèi)核。

?

Mysql線程池系列二(Oracle Mysql Thread pool的安裝和原理)

thread pool的組件和安裝
thread pool是以插件的方式存在的,安裝thread pool插件以后,會(huì)增加一些information_schema表和相關(guān)參數(shù)變量。
information_schema表包含:
TP_THREAD_STATE
TP_THREAD_GROUP_STATE
TP_THREAD_GROUP_STATS

新增加的參數(shù)變量:
thread_handling增加了一個(gè)值,loaded-dynamically,當(dāng)成功加載thread pool插件的時(shí)候就是這個(gè)值了。
thread_pool_algorithm:
thread_pool_high_priority_connection:
thread_pool_prio_kickup_timer:
thread_pool_max_unused_threads:
thread_pool_size：
thread_pool_stall_limit:
如果這些值設(shè)置的不正確,那么啟動(dòng)mysql的時(shí)候插件會(huì)初始化失敗,插件將不能加載。
這些變量的具體設(shè)置方法,會(huì)在接下來(lái)的優(yōu)化章節(jié)里面詳細(xì)的介紹。

thread pool插件的對(duì)象庫(kù)必須放在plugin_dir變量對(duì)應(yīng)的目錄里。為了使thread pool生效,可以在啟動(dòng)mysql的時(shí)候使用–plugin-load選項(xiàng)。或者修改my.cnf文件.在[mysqld]區(qū)段中添加如下信息
[mysqld]
plugin-load=thread_pool.so

thread pool的原理
thread pool包含數(shù)個(gè)thread grou ps ,每個(gè)thread group管理一組客戶(hù)端連接。當(dāng)連接建立以后,thread pool以輪詢(xún)的方式分配他們到thread group.
thread group的數(shù)量是通過(guò)thread_pool_size配置得到的,默認(rèn)是16個(gè),最大64個(gè),最小1個(gè)。
每個(gè)thread group最大可以有4096個(gè)線程。

線程池把連接和線程分開(kāi)了,所以連接和線程不是固定對(duì)應(yīng)的,線程執(zhí)行從connections收到的語(yǔ)句,這和默認(rèn)的thread_handling模式不同。

thread_handling參數(shù)
原來(lái)的版本里面有一個(gè)thread_handling參數(shù),可以設(shè)置thread的工作模式,有兩個(gè)值,
一個(gè)是no-threads,指任意時(shí)刻最多只有一個(gè)連接可以連接到mysql server,一般用于調(diào)試。另外一個(gè)是one-thread-per-connection,是指針對(duì)每個(gè)連接創(chuàng)建一個(gè)線程來(lái)處理這個(gè)連接的所有請(qǐng)求,直到連接斷開(kāi),線程結(jié)束.這也是thread_handling的默認(rèn)值。
由此可見(jiàn),默認(rèn)情況下,多少連接就會(huì)產(chǎn)生多少個(gè)線程,并發(fā)越大,線程越多,線程之間的資源競(jìng)爭(zhēng)越激烈,性能越低。

thread pool插件提供另外的一種thread_handling方法,用來(lái)有效的管理執(zhí)行線程與大量客戶(hù)端連接,從而提高性能。
線程池解決的幾個(gè)問(wèn)題:
*高并發(fā)的多線程棧導(dǎo)致CPU的緩存幾乎失效,線程池促進(jìn)線程堆棧重用,減少CPU緩存量。
*太多的線程并發(fā)執(zhí)行,上下文切換開(kāi)銷(xiāo)很高,這對(duì)操作系統(tǒng)的任務(wù)調(diào)度是一個(gè)很大的挑戰(zhàn),線程池可以把mysql活躍的并發(fā)線程控制在一個(gè)適合mysql server運(yùn)行的水平。
*太多的事務(wù)并發(fā)執(zhí)行會(huì)增加資源爭(zhēng)用,在innodb引擎里,會(huì)增加獲取central mutexes的時(shí)間,線程池可以控制事務(wù)的并發(fā)量。

thread pool嘗試保證每個(gè)thread group中的每個(gè)thread盡量執(zhí)行更多的語(yǔ)句,但是有些時(shí)候允許更多的線程執(zhí)行一些臨時(shí)的任務(wù)來(lái)提高性能。算法的工作方式如下:
*每個(gè)trhead group有一個(gè)listener,這個(gè)listener負(fù)責(zé)監(jiān)聽(tīng)分配給thread group的statements,thread group有兩種執(zhí)行方案,一是立即執(zhí)行,一種是排隊(duì)執(zhí)行。
*立即執(zhí)行的條件是當(dāng)前只收到一條statement,并且當(dāng)前沒(méi)有statements在執(zhí)行。
*排隊(duì)執(zhí)行發(fā)生在不能立即執(zhí)行的時(shí)候
*當(dāng)立即執(zhí)行發(fā)生的時(shí)候,是由listener線程執(zhí)行的,也就是說(shuō)listener在執(zhí)行一些臨時(shí)的statements,如果立即執(zhí)行的statement很快執(zhí)行完成,那么這個(gè)線程會(huì)變回listener線程,如果其他情況thread pool會(huì)考慮從新開(kāi)啟一個(gè)listener線程來(lái)代替它,是否需要?jiǎng)?chuàng)建listener線程是由thread pool的后臺(tái)線程來(lái)監(jiān)控和執(zhí)行的。
當(dāng)thread pool插件啟動(dòng)以后,每個(gè)thread group會(huì)創(chuàng)建一個(gè)listener線程,加上background線程,其他線程根據(jù)是否需要而創(chuàng)建。

thread_pool_stall_limit系統(tǒng)變量的含義可以理解為完成一個(gè)statement需要的時(shí)間,默認(rèn)認(rèn)為stalled的時(shí)間是60ms,最大可以設(shè)置為6s。配置這個(gè)參數(shù)可以讓你平衡服務(wù)器的工作負(fù)載.這個(gè)值設(shè)置的越小線程啟動(dòng)越快,更小的值可以更好的避免死鎖,更大的值通常在很多長(zhǎng)查詢(xún)的時(shí)候使用,為了避免啟動(dòng)太多的線程。

thread pool的焦點(diǎn)在于限制并發(fā)的短查詢(xún)語(yǔ)句的數(shù)量,在一個(gè)語(yǔ)句執(zhí)行時(shí)間沒(méi)有達(dá)到stall的時(shí)候,阻止其他statements開(kāi)始執(zhí)行,如果一個(gè)statement執(zhí)行超過(guò)了stall time,它將會(huì)繼續(xù)執(zhí)行,但是不在阻止其他statement開(kāi)始執(zhí)行。用這種方法,thread pool嘗試確保每個(gè)thread group從來(lái)沒(méi)有超過(guò)一個(gè)short-running statement,盡管會(huì)有多個(gè)long-running statement。讓長(zhǎng)時(shí)間執(zhí)行的語(yǔ)句阻止其他語(yǔ)句的執(zhí)行,這是不可取的,因?yàn)闆](méi)有限制等待的最長(zhǎng)時(shí)間.例如,在一個(gè)replication的master,一個(gè)線程一直發(fā)送binlog給slave。

一個(gè)statement因?yàn)镮/O操作或者用戶(hù)級(jí)別的鎖被阻塞了,這個(gè)阻塞將會(huì)導(dǎo)致thread group無(wú)效,所以回調(diào)函數(shù)會(huì)通知thread pool確認(rèn),并且thread pool會(huì)馬上在這個(gè)thread group中啟動(dòng)一個(gè)新的線程執(zhí)行其他的statement.當(dāng)被阻塞的線程返回時(shí),thrad pool允許馬上重新啟動(dòng)。

這里有兩種隊(duì)列(queue),一種是高優(yōu)先級(jí)的隊(duì)列(high-priority queue),和一種低優(yōu)先級(jí)的隊(duì)列(low-priority queue).事務(wù)中的第一個(gè)statement會(huì)被分配到低優(yōu)先級(jí)的隊(duì)列,剩下的statement將會(huì)被分配到高優(yōu)先級(jí)的隊(duì)列里(前提是這個(gè)事務(wù)已經(jīng)開(kāi)始執(zhí)行了),或者被分配到低優(yōu)先級(jí)隊(duì)列。
隊(duì)列的分配受到thread_pool_high_priority_connection系統(tǒng)變量影響,這個(gè)參數(shù)的默認(rèn)值是0,表示同時(shí)使用低優(yōu)先級(jí)隊(duì)列和高優(yōu)先級(jí)隊(duì)列,如果值設(shè)置為1,所有queued statements都會(huì)被直接分配到高優(yōu)先級(jí)的隊(duì)列。

對(duì)于非事務(wù)的存儲(chǔ)引擎的statements,或者是autocommit的存儲(chǔ)引擎,都會(huì)被放入低優(yōu)先級(jí)的queue處理,因?yàn)槊總€(gè)statement都是一個(gè)事務(wù)。因此,使用innodb和myisam混合引擎的 數(shù)據(jù)庫(kù) ,thread pool認(rèn)為innodb的優(yōu)先級(jí)高于myisam的優(yōu)先級(jí),除非innodb開(kāi)啟了autocommit。如果autocommit開(kāi)啟,那么所有的statements都屬于低優(yōu)先級(jí)。

當(dāng)thread group選擇一個(gè)queue中的statement執(zhí)行的時(shí)候,它會(huì)優(yōu)先在高優(yōu)先級(jí)的queue中查找,然后才在低優(yōu)先級(jí)的queue中查找,如果找到tatement,他就會(huì)從queue中刪除這個(gè)statement,然后開(kāi)始執(zhí)行它。

如果一個(gè)statement在低優(yōu)先級(jí)的queue中等待很久,它將被thread pool移動(dòng)到高優(yōu)先級(jí)的queue里.等待的時(shí)間由thread_pool_prio_kickup_timer決定。

thread pool對(duì)活躍線程的重用,可以更好的使用CPU caches.這個(gè)很小的調(diào)整對(duì)性能的提升卻有很大幫助。

thread group分配多個(gè)線程執(zhí)行statement的情況:
*一個(gè)線程開(kāi)始執(zhí)行一個(gè)statement,但是執(zhí)行時(shí)間達(dá)到stalled以后,thread group允許其他線程開(kāi)始執(zhí)行其他statement,之前的線程繼續(xù)執(zhí)行之前的statement。
*一個(gè)線程開(kāi)始執(zhí)行一個(gè)statement,但是線程被阻塞了,報(bào)告給thread pool以后,thread group允許其他線程開(kāi)始執(zhí)行其他statement。
*一個(gè)線程開(kāi)始執(zhí)行一個(gè)statement,但是線程被阻塞了,由于阻塞不是發(fā)生在代碼層,所以沒(méi)有報(bào)告給thread pool。當(dāng)阻塞時(shí)間達(dá)到stall以后,thread group允許其他線程執(zhí)行其他statement。

線程的設(shè)計(jì)可以針對(duì)不斷增加的連接具有擴(kuò)展性,同時(shí)他的設(shè)計(jì)也可以通過(guò)限制并發(fā)的thread來(lái)盡量避免死鎖發(fā)生.但是要注意的是,阻塞的線程如果沒(méi)有報(bào)告thread pool,那么thread pool就不會(huì)阻塞其他線程的運(yùn)行,
這種情況可能會(huì)導(dǎo)致線程池死鎖。
*長(zhǎng)時(shí)間運(yùn)行的statments,很少的statements將使用所有的資源,這將導(dǎo)致服務(wù)器拒絕所有其他的訪問(wèn)。
*binary log dump線程讀取binlog,然后發(fā)送給slave,這是一種長(zhǎng)時(shí)間運(yùn)行的”statement”,他不會(huì)阻止其他的statements運(yùn)行.
*statement可以被row級(jí)別、table級(jí)別的鎖阻塞,也可以被sleep等其他原因的鎖阻塞,或者其他被阻塞的沒(méi)有報(bào)告thread pool的thread阻塞了。

上面每種情況,都是為了防止死鎖,沒(méi)有快速執(zhí)行完成的statement將被移動(dòng)到stalled分類(lèi),所以thread group允許其他statement開(kāi)始執(zhí)行。由于這個(gè)設(shè)計(jì),當(dāng)線程在執(zhí)行或者被阻塞的時(shí)間內(nèi),thread pool把這些線程
標(biāo)記為stalled類(lèi)型,然后余下的statement將會(huì)被執(zhí)行,它沒(méi)有拒絕其他statments的執(zhí)行.

最大的線程數(shù)可以達(dá)到max_connections和thread_pool_size的和,這種情況只有在所有的連接都在同時(shí)執(zhí)行,并且每個(gè)thread group開(kāi)啟一個(gè)listen線程來(lái)監(jiān)聽(tīng)新的statement。這種情況很難發(fā)生,但是理論上存在。

Mysql線程池系列三(Oracle Mysql Thread pool調(diào)優(yōu))

首先明確調(diào)優(yōu)的目的是提高TPS。

thread_pool_size:
是一個(gè)非常重要的參數(shù),控制thread pool的性能,具體表現(xiàn)為thread group的數(shù)量。只能在server啟動(dòng)之前設(shè)置,我們測(cè)試thread pool的結(jié)果如下:
*如果主存儲(chǔ)引擎是innodb,thread_pool_size設(shè)置在16至36之間,大多數(shù)情況設(shè)置在24到36,我們還沒(méi)有發(fā)現(xiàn)什么情況需要設(shè)置超過(guò)36，也只有一些特殊的環(huán)境需要設(shè)置小于16.
使用DBT2或者sysbench做測(cè)試的時(shí)候,innodb引擎下通常設(shè)置為36個(gè),如果在一些寫(xiě)入特別多的環(huán)境,這個(gè)值可以設(shè)置的更小一些。
*如果主存儲(chǔ)引擎是myisam,thread_pool_size需要設(shè)置的更低,我們推薦的值是4到8,更高的值可能會(huì)對(duì)性能有負(fù)面影響。

thread_pool_stall_limit:
這個(gè)參數(shù)對(duì)于處理阻塞和長(zhǎng)時(shí)間執(zhí)行的語(yǔ)句很重要。這個(gè)時(shí)間是從一個(gè)statement從開(kāi)始執(zhí)行到執(zhí)行完成總花費(fèi)的時(shí)間,如果超過(guò)設(shè)置值就被認(rèn)定為stalled,此時(shí)線程池也開(kāi)始允許執(zhí)行另外
一個(gè)statement。這個(gè)值的單位是10毫秒,默認(rèn)值是6，也就是默認(rèn)間隔是60ms,一個(gè)statement執(zhí)行超過(guò)60ms,就被認(rèn)為是stalled。最大值是600,也就是6秒。一般這個(gè)值設(shè)置為你99%的statement可以執(zhí)行完的時(shí)間。比如我慢查詢(xún)?cè)O(shè)置的
是0.1,那么這里就設(shè)置為10。另外可以通過(guò)
SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED) FROM information_schema.TP_THREAD_GROUP_STATS;來(lái)獲取stalled的比例,這個(gè)值盡量的小,為了避免stall,可以調(diào)高thread_pool_stall_limit的值。

thread_pool_prio_kickup_timer:
這個(gè)值影響低優(yōu)先級(jí)的statements的queue。參數(shù)值的單位是毫秒,低優(yōu)先級(jí)的statement需要等到多少毫秒才能被移動(dòng)到高優(yōu)先級(jí)的queue.默認(rèn)是1000,也就是1秒,值的范圍是(0-4294967294)。

thread_pool_high_priority_connection:
這個(gè)參數(shù)主要決定新來(lái)的statements的執(zhí)行優(yōu)先級(jí)。默認(rèn)值是0,表示同時(shí)使用low-prority queue和high-priority queue。如果設(shè)置為1,所有的statement都會(huì)分配到high-priority queue。

thread_pool_max_unused_threads:
這個(gè)參數(shù)限制thread pool中sleeping thread的最大數(shù)量。從而限制sleeping thread對(duì)內(nèi)存的使用。
如果參數(shù)的值為0,也就是默認(rèn)值,意味著對(duì)sleeping thread沒(méi)有限制.假設(shè)值為N，當(dāng)N大于0的時(shí)候,意味著1個(gè)consumer thread和N-1個(gè) reserve thread。意思也就是說(shuō),當(dāng)一個(gè)線程執(zhí)行完一個(gè)statement,將要轉(zhuǎn)為sleeping狀態(tài)的時(shí)候,這時(shí)sleeping狀態(tài)的
線程數(shù)量已經(jīng)達(dá)到了允許的sleeping thread的最大數(shù)量,那么這個(gè)線程將會(huì)退出。
關(guān)于consumer thread:sleeping thread由consumer thread和reserve thread組成,thread pool允許sleeping thread中有一個(gè)consumer thread,一個(gè)thread要轉(zhuǎn)變?yōu)閟leepling thread的時(shí)候,如果沒(méi)有consumer thread 存在,那么
這個(gè)thread將轉(zhuǎn)變?yōu)閏onsumer thread.當(dāng)一個(gè)sleeping thread要被喚醒的時(shí)候,如果存在consumer thread,那么優(yōu)先喚醒consumer thread,如果consumer thread不存在,那么喚醒reserve thread。

thread_pool_algorithm:
此參數(shù)決定thread pool使用那種算法.默認(rèn)值是0,表示使用較低的并發(fā)算法,在大多數(shù)測(cè)試和生產(chǎn)環(huán)境下效果很好。
另外一個(gè)值是1,更加積極的增加并發(fā)數(shù)量的算法,有時(shí)候會(huì)比最佳線程數(shù)量性能更好,但是隨著連接的增加,性能會(huì)逐漸下降。所以這個(gè)參數(shù)主要用在實(shí)驗(yàn)環(huán)境。

Mysql線程池優(yōu)化筆記