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

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

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

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

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

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

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

?

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

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

新增加的參數變量:
thread_handling增加了一個值,loaded-dynamically,當成功加載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:
如果這些值設置的不正確,那么啟動mysql的時候插件會初始化失敗,插件將不能加載。
這些變量的具體設置方法,會在接下來的優化章節里面詳細的介紹。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mysql線程池系列三(Oracle Mysql Thread pool調優)

首先明確調優的目的是提高TPS。

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

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

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

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

thread_pool_max_unused_threads:
這個參數限制thread pool中sleeping thread的最大數量。從而限制sleeping thread對內存的使用。
如果參數的值為0,也就是默認值,意味著對sleeping thread沒有限制.假設值為N，當N大于0的時候,意味著1個consumer thread和N-1個 reserve thread。意思也就是說,當一個線程執行完一個statement,將要轉為sleeping狀態的時候,這時sleeping狀態的
線程數量已經達到了允許的sleeping thread的最大數量,那么這個線程將會退出。
關于consumer thread:sleeping thread由consumer thread和reserve thread組成,thread pool允許sleeping thread中有一個consumer thread,一個thread要轉變為sleepling thread的時候,如果沒有consumer thread 存在,那么
這個thread將轉變為consumer thread.當一個sleeping thread要被喚醒的時候,如果存在consumer thread,那么優先喚醒consumer thread,如果consumer thread不存在,那么喚醒reserve thread。

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

Mysql線程池優化筆記