Mysql線程池最佳化筆記

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

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

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

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 groups,每個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,更加積極的增加並發數量的演算法,有時候會比最佳線程數量效能更好,但是隨著串連的增加,效能會逐漸下降。所以這個參數主要用在實驗環境。