一、为什么使用MySQL线程池
1、减少线程重复创建与销毁部分的开销,提高性能
线程池技术通过预先创建一定数量的线程,在监听到有新的请求时,线程池直接从现有的线程中分配一个线程来提供服务,服务结束后这个线程不会直接销毁,而是又去处理其他的请求。这样就避免了线程和内存对象频繁创建和销毁,减少了上下文切换,提高了资源利用率,从而在一定程度上提高了系统的性能和稳定性。
2、对系统起到保护作用
线程池技术限制了并发线程数,相当于限制了MySQL的runing线程数,无论系统目前有多少连接或者请求,超过最大设置的线程数的都需要排队,让系统保持高性能水平,从而防止DB出现雪崩,对底层DB起到保护作用。
可能有人会问,使用连接池能否也达到类似的效果?
也许有的人会把线程池和连接池混淆,但其实两者是有很大区别的:连接池一般在客户端设置,而线程池是在DB服务器上配置;另外连接池可以起到避免了连接频繁创建和销毁,但是无法控制MySQL活动线程数的目标,在高并发场景下,无法起到保护DB的作用。比较好的方式是将连接池和线程池结合起来使用;
二、MySQL线程池介绍MySQL线程池简介
为了解决one-thread-per-connection(每个连接一个线程)存在的频繁创建和销毁大量线程以及高并发情况下DB雪崩的问题,实现DB在高并发环境依然能保持较高的性能。
Oracle和MariaDB都推出了ThreadPool方案,目前Oracle的Thread pool实现为Plugin方式,并且只添加到在Enterprise版本中,Percona移植了MariaDB的Thread pool功能,并做了进一步的优化。本地环境就基于Percona MySQL 5.7版本。
MySQL线程池架构
MySQL的Thread pool(线程池)被划分为多个group(组),每个组又有对应的工作线程,整体的工作逻辑还是比较复杂,下面我试图通过简单的方式来介绍MySQL线程池的工作原理。
1、架构图
首先来看看Thread Pool的架构图
2、Thread Pool的组成
从架构图中可以看到Thread Pool由一个Timer线程和多个Thread Group组成,而每个Thread Group又由两个队列、一个listener线程和多个worker线程构成。下面分别来介绍各个部分的作用:
队列(高优先级队列和低优先级队列)
用来存放待执行的IO任务,分为高优先级队列和低优先级队列,高优先级队列的任务会优先被处理, 什么任务会放在高优先级队列呢?
事务中的语句会放到高优先级队列中,比如一个事务中有两个update的SQL,有1个已经执行,那么另外一个update的任务就会放在高优先级中。这里需要注意,如果是非事务引擎,或者开启了Autocommit的事务引擎,都会放到低优先级队列中。还有一种情况会将任务放到高优先级队列中,如果语句在低优先级队列停留太久,该语句也会移到高优先级队列中,防止饿死。
listener线程
listener线程监听该线程group的语句,并确定当自己转变成worker线程,是立即执行对应的语句还是放到队列中,判断的标准是看队列中是否有待执行的语句。
如果队列中待执行的语句数量为0,而listener线程转换成worker线程,并立即执行对应的语句。如果队列中待执行的语句数量不为0,则认为任务比较多,将语句放入队列中,让其他的线程来处理。这里的机制是为了减少线程的创建,因为一般SQL执行都非常快。
worker线程
worker线程是真正干活的线程
Timer线程
Timer线程是用来周期性检查group是否处于处于阻塞状态,当出现阻塞的时候,会通过唤醒线程或者新建线程来解决。
具体的检测方法为:通过queue_event_count的值和IO任务队列是否为空来判断线程组是否为阻塞状态。
每次worker线程检查队列中任务的时候,queue_event_count会+1,每次Timer检查完group是否阻塞的时候会将queue_event_count清0,如果检查的时候任务队列不为空,而queue_event_count为0,则说明任务队列没有被正常处理,此时该group出现了阻塞,Timer线程会唤醒worker线程或者新建一个wokrer线程来处理队列中的任务,防止group长时间被阻塞。
3、Thread Pool的是如何运作的?
下面描述极简的Thread Pool运作
Step1:请求连接到MySQL,根据threadid%thread_pool_size确定落在哪个group;
Step2:group中的listener线程监听到所在的group有新的请求以后,检查队列中是否有请求还未处理。如果没有,则自己转换为worker线程立即处理该请求,如果队列中还有未处理的请求,则将对应请求放到队列中,让其他的线程处理;
Step3:group中的thread线程检查队列的请求,如果队列中有请求,则进行处理,如果没有请求,则休眠,一直没有被唤醒,超过thread_pool_idle_timeout后就自动退出。线程结束。当然,获取请求之前会先检查group中的running线程数是否超过thread_pool_oversubscribe+1,如果超过也会休眠;
Step4:timer线程定期检查各个group是否有阻塞,如果有,就对wokrer线程进行唤醒或者创建一个新的worker线程。
4、Thread Pool的分配机制
线程池会根据参数thread_pool_size的大小分成若干的group,每个group各自维护客户端发起的连接,当客户端发起连接到MySQL的时候,MySQL会跟进连接的线程id(thread_id)对thread_pool_size进行取模,从而落到对应的group。
thread_pool_oversubscribe参数控制每个group的最大并发线程数,每个group的最大并发线程数为thread_pool_oversubscribe+1个。若对应的group达到了最大的并发线程数,则对应的连接就需要等待。这个分配机制在某个group中有多个慢SQL的场景下会导致普通的SQL运行时间很长,这个问题会在后面做详细描述。
MySQL线程池参数说明
关于线程池参数不多,使用show variables like 'thread%'可以看到如下图的参数,下面就一个一个来解析:
thread_handling: 该参数是配置线程模型,默认情况是one-thread-per-connection,即不启用线程池;将该参数设置为pool-of-threads即启用了线程池;
thread_pool_size: 该参数是设置线程池的Group的数量,默认为系统CPU的个数,充分利用CPU资源;
thread_pool_oversubscribe: 该参数设置group中的最大线程数,每个group的最大线程数为thread_pool_oversubscribe+1,注意listener线程不包含在内;
thread_pool_high_prio_mode: 高优先级队列的控制参数,有三个值(transactions/statements/none),默认是transactions,三个值的含义如下:
transactions:对于已经启动事务的语句放到高优先级队列中,不过还取决于后面的thread_pool_high_prio_tickets参数;
statements:这个模式所有的语句都会放到高优先级队列中,不会使用到低优先级队列;
none:这个模式不使用高优先级队列;
thread_pool_high_prio_tickets: 该参数控制每个连接最多语序多少次被放入高优先级队列中,默认为4294967295,注意这个参数只有在thread_pool_high_prio_mode为transactions的时候才有效果;
thread_pool_idle_timeout: worker线程最大空闲时间,默认为60秒,超过限制后会退出;
thread_pool_max_threads: 该参数用来限制线程池最大的线程数,超过该限制后将无法再创建更多的线程,默认为100000;
thread_pool_stall_limit: 该参数设置timer线程的检测group是否异常的时间间隔,默认为500ms;
三、MySQL线程池的使用
线程池的使用比较简单,只需要添加配置后重启实例即可
具体配置如下:
#thread pool
thread_handling=pool-of-threads
thread_pool_oversubscribe=3
thread_pool_size=24
#extra connection
extra_max_connections = 8
extra_port = 33333
备注:其他参数默认即可
以上具体的参数在前面已做详细说明,下面是配置中需要注意的一点:
1、添加extra connection是防止线程池满的情况下无法登录MySQL,因此特意用管理端口,以备紧急的情况下使用;
重启实例后,可以通过show variables like '%thread%';来查看配置的参数是否生效