mysql 开启 thread pool_MySQL線程池(THREAD POOL)的處理

背景介紹

MySQL常用(目前線上使用)的線程調度方式是one-thread-per-connection(每連接一個線程)，server為每一個連接創建一個線程來服務，連接斷開后，這個線程進入thread_cache或者直接退出(取決於thread_cache設置及系統當前已經cache的線程數目)，one-thread-per-connection調度的好處是實現簡單，而且能夠在系統沒有遇到瓶頸之前保證較小的響應時間，比較適合活躍的長連接的應用場景，而在大量短連接或者高並發情況下，one-thread-per-connection需要創建/調度大量的線程，產生較高的的context-switch代價，從而使得系統性能下降

為了解決這個問題，Oracle和MariaDB分別推出了threadpool方案，目前Oracle的threadpool實現為plugin方式，並且只添加到在Enterprise版本中，沒有公布代碼，MariaDB threadpool在5.5版本中引入，我們一直密切關注社區動態並在第一時間測試了MariaDB threapool性能，並且發現了一些其中的問題，比如：要像發揮線程池的優勢，需要盡量控制線程池中線程數目，否則會退化成one-thread-per-connection，而如果嚴格控制線程池中線程數據，可能會出現調度上的死鎖，percona在移植MariaDB threadpool的實現后進一步優化了線程池性能，通過引入優先隊列很好解決了這個問題，經測試效果明顯，因此我們將這個特性port到了AliMySQL中

實現簡介

1、threadpool中worker線程處理單位為一個statement，而不是one-thread-per-connection對應的一個連接；當worker線程處理完A連接發送來的一個sql后，A連接沒有立刻發送第二條sql，worker線程回去服務其它連接發送來的sql，因此worker線程工作效率更高，系統需要的線程數也更少

2、threadpool本質上是一個生產者-消費者模型，為了減小競爭，threadpool被划分為N個group(n默認為cpu核心數)，連接發送的sql根據連接id分配到不同的group中，因此，同一個連接發送的所有sql是被同一個group中的worker線程處理的

3、每個group都有2個任務隊列， 即優先隊列和普通隊列 ，如果一個sql所在的事務已經開啟，則將任務放到優先隊列中，否則放到普通隊列中，worker線程優先從優先隊列中取任務執行，當優先隊列為空則從普通隊列取任務執行，這個可以保證已經開啟的事務優先得到執行，從而盡早釋放其占用的資源(主要是鎖)，可以有效減小響應時間，別且避免調度上的死鎖(A和B被分到不同的group中，A事務已經開啟，並且獲得了鎖，可能無法立即得到調度執行，B事務依賴A事務釋放鎖資源，但是先於A得到調度)

4、每個group中每個worker線程地位一樣，如果遇到任務隊列為空的情況，線程會調用epoll_wait批量取任務

5、threadpool額外創建了一個timer線程，每隔一段時間檢查一遍所有的group，如果發現group出現異常(堵塞/超時/worker線程數目不夠)，及時喚醒線程

threadpool相關參數

root@(none) 05:33:27>showglobal variables like '%thread_pool%';

+-------------------------------+--------------+

| Variable_name | Value |

+-------------------------------+--------------+

| thread_pool_high_prio_mode | transactions |

| thread_pool_high_prio_tickets | 4294967295 |

| thread_pool_idle_timeout | 60 |

| thread_pool_max_threads | 100000 |

| thread_pool_oversubscribe | 3 |

| thread_pool_size | 24 |

| thread_pool_stall_limit | 500 |

+-------------------------------+--------------+

7 rows in set(0.00 sec)

> thread_pool_high_prio_mode

有三個取值：transactions / statements / none

transactions(default): 使用優先隊列和普通隊列，對於事務已經開啟的statement，放到優先隊列中，否則放到普通隊列中

statements：只使用優先隊列

none: 只是用普通隊列，本質上和statements相同，都是只是用一個隊列

> thread_pool_high_prio_tickets

取值0~4294967295，當開啟了優先隊列模式后(thread_pool_high_prio_mode=transactions)，每個連接最多允許thread_pool_high_prio_tickets次被放到優先隊列中，之后放到普通隊列中，默認為4294967295

> thread_pool_idle_timeout

worker線程最大空閑時間，單位為秒，超過限制后會退出，默認60

> thread_pool_max_threads

threadpool中最大線程數目，所有group中worker線程總數超過該限制后不能繼續創建更多線程，默認100000

> thread_pool_oversubscribe

一個group中線程數過載限制，當一個group中線程數超過次限制后，繼續創建worker線程會被延遲，默認3

> thread_pool_size

threadpool中group數量，默認為cpu核心數，server啟動時自動計算

> thread_pool_stall_limit

timer線程檢測間隔，單位為毫秒，默認500

性能測試

測試硬件：

mysql服務器：myb160031.cm3

mytest壓力機：mya094004.cm3

兩台機器都為24核心cpu，192G內存

性能指標：

QPS/TPS/RT(Response time)

實驗對照

one-thread-per-connection: 基准數據

threadpool(high prio off): 線程池方案，優先隊列不開啟

threadpool(high prio on): 線程池方案，開啟優先隊列

只讀場景

tc_read_1_3(讀取數據占所有數據1/3)，tcbuyer_0000(數據量120G)，sql序列：

setautocommit=0; select...; commit; select; commit;...

QPS：

RT：

結果說明

threadpool方案，開啟優先隊列后能夠在高並發下維持最高性能，且RT更低，而不開啟優先隊列，在高並發下性能甚至會比one-thread-per-connection低，原因：本測試中，sql執行序列為：

set autocommit=0; select…; commit; select…; commit;…在不開啟優先隊列的情況下，因為線程池以statement為調度單位，會導致事務鏈表相對於one-thread-per-connection更長

讀寫場景>

tc_rw_5_1(讀寫壓力為5:1)，tcbuyer_0000(數據量約120G)，sql序列：

setautocommit=0; update;update;commit/select;select;commit/update;select;commit/...

QPS + TPS

RT：

結果說明

開啟優先隊列的threadpool在高並發下可以保持最高性能，同時rt也較小，而one-thread-per-connection與不開啟優先隊列的threadpool調度方案在高並發下性能急劇下降，rt明顯升高

測試結論

開啟優先隊列的threadpool通過優先調度已開啟事務縮短事務執行時間，在高並發下可以保持最高性能，同時保證較小的rt

MySQL 參數配置

[mysqld_safe]

pid-file=/u01/my3306/run/mysqld.pid

malloc-lib=/u01/mysql/lib/libjemalloc.so

[mysql]

port=3306

prompt=\\u@\\d \\r:\\m:\\s>

default-character-set=gbk

no-auto-rehash

[client]

port=3306

socket=/u01/my3306/run/mysql.sock

[mysqld]

#dir

basedir=/u01/my3306

datadir=/u01/my3306/data

tmpdir=/u01/my3306/tmp

lc_messages_dir=/u01/mysql/share

#log-error=/u01/my3306/log/alert.log

slow_query_log_file=/u01/my3306/log/slow.log

general_log_file=/u01/my3306/log/general.log

socket=/u01/my3306/run/mysql.sock

#innodb

innodb_data_home_dir=/u01/my3306/data

innodb_log_group_home_dir=/u01/my3306/data

innodb_data_file_path = ibdata1:4G;ibdata2:16M:autoextend

innodb_buffer_pool_size=70G

innodb_buffer_pool_instances=8

innodb_log_files_in_group=4

innodb_log_file_size=1G

innodb_log_buffer_size=200M

innodb_flush_log_at_trx_commit=1

innodb_additional_mem_pool_size=20M

innodb_max_dirty_pages_pct=60

innodb_io_capacity=1000

innodb_thread_concurrency=32

innodb_read_io_threads=8

innodb_write_io_threads=8

innodb_open_files=60000

innodb_file_format=Barracuda

innodb_file_per_table=1

innodb_flush_method=O_DIRECT

innodb_flush_neighbor_pages=0

innodb_change_buffering=inserts

innodb_adaptive_flushing=1

innodb_adaptive_flushing_method=keep_average

innodb_adaptive_hash_index_partitions=1

innodb_old_blocks_time=1000

innodb_fast_checksum=1

innodb_stats_on_metadata=0

innodb_lazy_drop_table=0

innodb_read_ahead=0

innodb_use_native_aio=0

innodb_lock_wait_timeout=5

innodb_rollback_on_timeout=0

innodb_purge_threads=1

innodb_strict_mode=1

transaction-isolation=READ-COMMITTED

#myisam

key_buffer=64M

myisam_sort_buffer_size=64M

concurrent_insert=2

delayed_insert_timeout=300

#replication

master-info-file=/u01/my3306/log/master.info

relay-log=/u01/my3306/log/relaylog

relay_log_info_file=/u01/my3306/log/relay-log.info

relay-log-index=/u01/my3306/log/mysqld-relay-bin.index

slave_load_tmpdir=/u01/my3306/tmp

slave_type_conversions="ALL_NON_LOSSY"

slave_net_timeout=4

skip-slave-start

sync_master_info=1000

sync_relay_log_info=1000

#binlog

log-bin=/u01/my3306/log/mysql-bin

server_id=0

binlog_cache_size=32K

max_binlog_cache_size=2G

max_binlog_size=500M

binlog-format=ROW

sync_binlog=1000

log-slave-updates=1

expire_logs_days=0

#server

default-storage-engine=INNODB

character-set-server=gbk

lower_case_table_names=1

skip-external-locking

open_files_limit=655360

safe-user-create

local-infile=1

#sqlmod="STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE"

performance_schema=0

log_slow_admin_statements=1

log_slow_verbosity=full

log_warnings=1

long_query_time=1

slow_query_log=1

general_log=0

query_cache_type=0

query_cache_limit=1M

query_cache_min_res_unit=1K

table_definition_cache=65536

table_cache=65536

thread_stack=512K

thread_cache_size=256

read_rnd_buffer_size=128K

sort_buffer_size=256K

join_buffer_size=128K

read_buffer_size=128K

port=3306

skip-name-resolve

skip-ssl

max_connections=18500

max_user_connections=18000

max_connect_errors=65536

max_allowed_packet=128M

connect_timeout=8

net_read_timeout=30

net_write_timeout=60

back_log=1024