MySQL threadpool(优先队列)介绍及性能测试

By | 2014 年 3 月 13 日

背景介绍

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线程处理单位为一个sql,而不是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线程数目不够),及时唤醒线程

关于MySQL线程调度接口以及MariaDB threadpool实现细节可以参考之前我写的两篇博客:mysql thread sheduler 源码分析mariadb 5.5 threadpool 源码分析

threadpool相关参数

root@(none) 05:33:27>show global 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服务器:mybxxxxxx.cm3
mytest压力机:myayyyyyy.cm3
两台机器都为24核心cpu,192G内存,bufferpool: 70G

性能指标:
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序列:

set autocommit=0; select...; commit; select...; commit;...

QPS:
commit

RT:
commit

结果说明

threadpool方案,开启优先队列后能够在高并发下维持最高性能,且RT更低,而不开启优先队列,在高并发下性能甚至会比one-thread-per-connection低,原因:本测试中,sql执行序列为:
set autocommit=0; select…; commit; select…; commit;…在不开启优先队列的情况下,因为线程池以sql为调度单位,导致活跃事务链表相对于one-thread-per-connection更长

thread_pool_stall_limit影响:

1. thread_pool_stall_limit = 500(default,RT 50ms,活跃事务589个,QPS:22K

- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- 
- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|
16:01:45|24.42 28.74 41.41| 58  26  16   0|    0    0|    0     0     0  22156     0|   45074 100.00|    0     0     0  22147|  32 2050    0    0|  27401  51453  48623  50722|
16:01:50|24.71 28.72 41.34| 56  27  17   0|    0    0|    0     0     0  22197     0|   45085 100.00|    0     0     0  22188|  32 2050    0    0|  29130  50076  47418  49414|
16:01:55|24.97 28.71 41.27| 57  27  17   0|    0    0|    0     0     0  22368     0|   45490 100.00|    0     0     0  22363|  28 2050    0    0|  28890  50118  47570  49484|
16:02:01|24.74 28.60 41.17| 57  26  17   0|    0    0|    0     0     0  22139     0|   44947 100.00|    0     0     0  22126|  30 2050    0    0|  29835  49205  46741  48593|
16:02:06|24.44 28.47 41.06| 57  26  17   0|    0    0|    0     0     0  22350     0|   45434 100.00|    0     0     0  22343|  33 2050    0    0|  28861  50235  47547  49553|
$mysql -uroot -e "show engine innodb status\G"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l
589

2. thread_pool_stall_limit = 50(RT 30ms,活跃事务233个,QPS:43K

- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- 
- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|
16:05:07|36.72 30.31 39.44| 61  34   5   0|    0    0|    0     0     0  43161     0|   87709 100.00|    0     0     0  43086|  90 2050    0    0|  27352  32342  28501  31226|
16:05:13|37.14 30.51 39.46| 61  34   5   0|    0    0|    0     0     0  43569     0|   88545 100.00|    0     0     0  43489|  91 2050    0    0|  25431  34640  30410  33380|
16:05:18|39.69 31.15 39.61| 62  34   5   0|    0    0|    0     0     0  42948     0|   87307 100.00|    0     0     0  42872|  93 2050    0    0|  26660  33376  29565  32292|
16:05:23|41.88 31.74 39.76| 61  34   5   0|    0    0|    0     0     0  43063     0|   87508 100.00|    0     0     0  42977|  88 2050    0    0|  28459  32175  28363  31055|
16:05:28|44.05 32.36 39.92| 62  33   5   0|    0    0|    0     0     0  43448     0|   88287 100.00|    0     0     0  43375|  89 2050    0    0|  25910  34201  30115  32981|
$mysql -uroot -e "show engine innodb status\G"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l
233

3. thread_pool_stall_limit = 30(RT 30ms,活跃事务127个,QPS:52K

- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- 
- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|
16:06:43|57.76 39.25 41.74| 64  33   3   0|    0    0|    0     0     0  52850     0|  107456 100.00|    0     0     0  52740|  95 2050    0    0|  26054  28743  24475  27464|
16:06:48|59.46 39.91 41.94| 66  32   3   0|    0    0|    0     0     0  53107     0|  107873 100.00|    0     0     0  52997|  95 2050    0    0|  22254  32018  27256  30635|
16:06:54|61.02 40.56 42.14| 64  33   3   0|    0    0|    0     0     0  51907     0|  105618 100.00|    0     0     0  51837|  95 2050    0    0|  23849  30579  26223  29298|
16:06:59|59.73 40.97 42.26| 64  33   3   0|    0    0|    0     0     0  52717     0|  107101 100.00|    0     0     0  52571|  94 2050    0    0|  23591  30359  26042  29117|
16:07:04|60.31 41.40 42.39| 64  33   3   0|    0    0|    0     0     0  52412     0|  106468 100.00|    0     0     0  52306|  93 2050    0    0|  26934  28257  24280  27082|
$mysql -uroot -e "show engine innodb status\G"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l
127

解释:thread_pool_stall_limit 是后台timer线程检测任务是否堵塞的时间间隔,在并发压力较大时,该参数设置过大可能会造成timer线程无法及时唤醒/创建worker线程,从测试结果中可以看出该参数设置为最大可接受的RT比较合适(应用期望DB的最大响应时间)

读写场景>/h4>
tc_rw_5_1(读写压力为5:1),tcbuyer_0000(数据量约120G),sql序列:

set autocommit=0; update...;update;commit/select...;select...;commit/update...;select...;commit/...

QPS + TPS
commit

RT:
commit

结果说明

开启优先队列的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

5 thoughts on “MySQL threadpool(优先队列)介绍及性能测试

  1. 赖明星

    “比如:要像发挥线程池的优势,需要尽量控制线程池中线程数目,否则会退化成one-thread-per-connection,而如果严格控制线程池中线程数据,可能会出现调度上的死锁”,调度上的死锁,这个怎么说,怎么构造死锁场景?

    Reply
  2. 赖明星

    “A和B被分到不同的group中,A事务已经开启,并且获得了锁,可能无法立即得到调度执行,B事务依赖A事务释放锁资源,但是先于A得到调度)”……额,我懂了

    Reply
  3. penfei

    A和B被分到不同的group中,A事务已经开启,并且获得了锁,可能无法立即得到调度执行,B事务依赖A事务释放锁资源,但是先于A得到调度

    这不能叫死锁吧?这只能说 B 等待 A, 即使是 one thread per connection , 也不能保证 A 优先调度

    Reply
  4. Pingback: mysql并发线程控制之thread pool和优先队列-IT学习分享网(学IT吧)-专注于IT学习与分享!

发表评论

电子邮件地址不会被公开。 必填项已用*标注