背景介紹
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