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

首先介紹什么是mysql thread pool,干什么用的？
使用線程池主要可以達到以下兩個目的：
1、在大并發(fā)的時候,性能不會因為過載而迅速下降。
2、減少性能抖動

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

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

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

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

說一下oracle mysql thread pool插件的限制：
1、Oracle Mysql enterprise 6.10版本添加的,也就是說小于這個版本的企業(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插件以后,會增加一些information_schema表和相關(guān)參數(shù)變量。
information_schema表包含:
TP_THREAD_STATE
TP_THREAD_GROUP_STATE
TP_THREAD_GROUP_STATS

新增加的參數(shù)變量:
thread_handling增加了一個值,loaded-dynamically,當(dāng)成功加載thread pool插件的時候就是這個值了。
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è)置的不正確,那么啟動mysql的時候插件會初始化失敗,插件將不能加載。
這些變量的具體設(shè)置方法,會在接下來的優(yōu)化章節(jié)里面詳細的介紹。

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

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

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

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

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

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

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

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

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

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

對于非事務(wù)的存儲引擎的statements,或者是autocommit的存儲引擎,都會被放入低優(yōu)先級的queue處理,因為每個statement都是一個事務(wù)。因此,使用innodb和myisam混合引擎的 數(shù)據(jù)庫 ,thread pool認為innodb的優(yōu)先級高于myisam的優(yōu)先級,除非innodb開啟了autocommit。如果autocommit開啟,那么所有的statements都屬于低優(yōu)先級。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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