mysql线程池配置

线程池(Thread Pool)

为了解决one-thread-per-connection(每个连接一个线程)的频繁创建和销毁大量线程的性能问题，Oracle和MariaDB都推出了ThreadPool方案。

MySQL的线程池（Thread Pool）由一个Timer线程和多个线程组（Thread Group）组成。每个线程组（Thread Group）包含两个队列（高优先级队列和低优先级队列）、监听线程(Listener Thread)和多个工作线程（Worker Thread）。

1.1、定时线程（Timer Thread)

Timer线程主要负责周期性检查Thread Group是否处于阻塞状态，出现阻塞时会通过唤醒线程或者新建线程来解决，防止线程组（Thread Group）长时间被阻塞。

阻塞状态：当任务队列不为空且queue_event_count＝0 时，说明Thread Group处于阻塞状态。

1.2、线程组（Thread Group）

线程组（Thread Group）由两个队列（高优先级队列和低优先级队列）、监听线程(Listener Thread)和多个工作线程（Worker Thread）组成。

1.2.1 队列（Queue）

队列(Queue)用于存放待执行的IO任务，包括高优先级队列(High-priority Queue)和低优先级队列(Low-priority Queue)，高优先级队列(High-priority Queue)中的任务会被优先处理。

1.2.2 监听线程（Listener Thread）

监听线程（Listener Thread）负责监听线程组（Thread Group)的语句并确定当转变成worker线程时，是立即执行语句还是放到队列中。

判断依据：

如果队列中待执行的语句数量为0，则Listener线程转换成worker线程，并立即执行对应的语句。

如果队列中待执行的语句数量不为0，则认为任务比较多，将语句放入队列中，让其他的线程来处理。

这里的机制是为了减少线程的创建，因为一般SQL执行都非常快。

1.2.3 工作线程（Worker Thread）

工作线程（Worker Thread）负责具体SQL语句的执行。

2、MySQL线程池相关参数说明

3、MySQL线程池使用3.1 查看MySQL线程池配置参数

连接并登录到MySQL，并执行如下语句。

> show variables like ‘thread%‘

3.2 配置MySQL线程池参数

添加以下内容到my.cnf配置文件并重启实例。

my.cnf

#----------------------------------------------------

# Thread Pool

thread_handling=pool-of-threads

thread_pool_oversubscribe=10

thread_pool_size=24

performance_schema=off

#extra connection

extra_max_connections = 8

extra_port = 13306

#----------------------------------------------------

说明：

1、关闭PG(performance_schema=off)是因为测试过程中发现Thread pool和PS同时开启的时候会出现内存泄漏问题。

2、添加extra connection是防止线程池满的情况下无法登录MySQL，此时可以使用管理端口登录。

3.3 验证MySQL线程池配置参数

重启实例后，连接并登录到MySQL并执行如下语句以确认配置的参数是否生效。

> show variables like ‘thread%‘

4、MySQL线程池使用中的问题4.1 内存泄漏问题

MySQL启用线程池后出现内存飙升，并且持续增长的情况。经确认只有开启Performance_Schema和Thread Pool的时候才会出现。

解决方法：

关闭Performance_Schema，即在配置文件添加performance_schema=off，然后重启MySQL。

注：

Percona server 5.7.21-20版本已经修复了线程池和PS同时打开内存泄漏的问题。

4.2 拨测异常问题

启用线程池以后，相当于限制了MySQL的并发线程数，当达到最大线程数的时候，其他的线程需要等待，新连接也会卡在连接验证那一步，这时候会造成拨测程序连接MySQL超时。

拨测程序连接实例超时后，就会认为master已经出现问题。极端情况下，重试多次都有异常后，就启动自动切换的操作，将业务切换到从机。

解决方法：

启用MySQL的旁路管理端口，监控和高可用相关直接使用MySQL的旁路管理端口。

旁路管理端口配置

#extra connection

extra_max_connections = 8

extra_port = 13306