mysql threadpool_MySQL threadpool（优先队列）介绍及性能测试

背景介绍

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>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服务器：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序列：

set autocommit=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序列：

set autocommit=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