MySQL核心月報2014.12-MySQL·　效能優化·threadpool原理分析

大連線問題

現有mysql 處理客戶端連線的方式會觸發mysql 新建一個執行緒來處理新的連線，新建的執行緒會處理該連線所傳送的所有 SQL 請求，即 one-thread-per-connection 的方式，其建立連線的堆疊為：

執行緒建立後，處理請求的堆疊如下：

1. 0 mysql_execute_command
2. 1 0x0000000000936f40 in mysql_parse
3. 2 0x0000000000920664 in dispatch_command
4. 3 0x000000000091e951 in do_command
5. 4 0x00000000008c2cd4 in do_handle_one_connection
6. 5 0x00000000008c2442 in handle_one_connection
7. 6 0x0000003562e07851 in start_thread () from /lib64/libpthread.so.0
8. 7 0x0000003562ae767d in clone () from /lib64/libc.so.6

優點及存在的問題

在連線數較小的情況下可以很快的響應客戶端的請求，但當連線數非常大時會建立很多執行緒，這樣會引起以下問題：

1. 過多執行緒之間的切換會加重系統的負載，造成系統資源緊張且響應不及時；

2. 頻繁的進行執行緒的建立及銷燬以及執行緒間同時無序的竟爭系統資源加重了系統的負載。

thread_pool正是為了解決以上問題而產生的；

什麼是thread_pool

thread_pool(執行緒池)，是指mysql 建立若干工作執行緒來共同處理所有連線的使用者請求，使用者的請求的方式不再是 ‘one thread per connection’，而是多個執行緒共同接收並處理多個連線的請求，在資料庫的底層處理方面(mysql_execute_command)，單執行緒的處理方式和執行緒池的處理方式是一致的。

thread_pool 的工作原理

啟動 thread_pool 的mysql 會建立thread_pool_size 個thread group , 一個timer thread, 每個thread group 最多擁有thread_pool_oversubscribe個活動執行緒，一個listener執行緒，listener執行緒負責監聽分配到thread group中的連線，並將監聽到的事件放入到一個queue中，worker執行緒從queue中取出連線的事件並執行具體的操作，執行的過程和one thread per connection 相同。timer threaad 則是為了監聽各個threadgroup的執行情況，並根據是否陰塞來建立新的worker執行緒。

thread_pool 建立連線的堆疊如下：

thread group中的 worker 處理請求的堆疊如下：

1. 0 mysql_execute_command
2. 1 0x0000000000936f40 in mysql_parse
3. 2 0x0000000000920664 in dispatch_command
4. 3 0x000000000091e951 in do_command
5. 4 0x0000000000a78533 in threadpool_process_request
6. 5 0x000000000066a10b in handle_event
7. 6 0x000000000066a436 in worker_main
8. 7 0x0000003562e07851 in start_thread ()
9. 8 0x0000003562ae767d in clone ()

其中worker_main函式是woker執行緒的主函式，呼叫mysql本身的do_command 進行訊息解析及處理，和one_thread_per_connection 是一樣的邏輯； thread_pool 自行控制工作的執行緒個數，進而實現執行緒的管理。

thread_pool中執行緒的建立：

1. listener執行緒將監聽事件放入mysql放入queue中時，如果發現當前thread group中的活躍執行緒數active_thread_count為零，則建立新的worker 執行緒；

2. 正在執行的執行緒阻塞時，如果發現當前thread group中的活躍執行緒數active_thread_count為零，則建立新的worker 執行緒；

3. timer執行緒在檢測時發現沒有listener執行緒且自上次檢測以來沒有新的請求時會建立新的worker執行緒，其中檢測的時間受引數threadpool_stall_limit控制；

4. timer執行緒在檢測時發現沒有執行過新的請求且執行佇列queue 不為空時會建立新的worker執行緒；

worker執行緒的偽碼如下：

thread_pool中執行緒的銷燬：

當從佇列queue中取出的connection為空時，則此執行緒銷燬，取connection所等待的時間受引數thread_pool_idle_timeout的控制； 綜上，thread_pool通過執行緒的建立及銷燬來自動處理worker的執行緒個數，在負載較高時，建立的執行緒數目較高，負載較低時，會銷燬多餘的worker執行緒，從而降低連線個數帶來的影響的同時，提升穩定性及效能。同時，threadpool中引入了Timer 執行緒，主要做兩個事情。

1. 定期檢查每個thread_group是否阻塞，如果阻塞，則進行喚醒或建立執行緒的工作；

2. 檢查每個thread_group中的連線是否超時，如果超時則關掉連線並釋放相應的資源；

threadpool在使用中存在的問題：

1. 由於threadpool嚴格控制活躍執行緒數的限制，如果同時有多個大查詢同時分配到了同一個thread group，則會造成此group中的請求過慢，rt 升高，最典型的就是多個binlog dump 執行緒同時分配到了同一個group內；

2. 開啟了事務模式時，非事務模式的請求會放入低優先順序佇列，因此可能在較長時間內得不到有效處理，極端情況下，會導致例項hang 住，例如某個連線執行了 flush tables with read lock ,並且此連線的後續請求都會放入低優先順序，那麼有可能會造成實列hang住；

3. 較小併發下，threadpool 效能退化的問題；