MySQL数据库性能优化之thread pool 原理分析，值得收藏
概述
今天分享下MySQL数据库的 thread pool。
一、大连接问题
mysql5.6之前处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接，新建的线程会处理该连接所发送的所有 SQL 请求，即 one-thread-per-connection 的方式，其创建连接的堆栈为：
mysqld_main handle_connections_sockets create_new_thread create_thread_to_handle_connection handle_one_connection
线程建立后，处理请求的堆栈如下：
0 mysql_execute_command 1 0x0000000000936f40 in mysql_parse 2 0x0000000000920664 in dispatch_command 3 0x000000000091e951 in do_command 4 0x00000000008c2cd4 in do_handle_one_connection 5 0x00000000008c2442 in handle_one_connection 6 0x0000003562e07851 in start_thread () from /lib64/libpthread.so.0 7 0x0000003562ae767d in clone () from /lib64/libc.so.6
二、优点及存在的问题
在连接数较小的情况下可以很快的响应客户端的请求，但当连接数非常大时会创建很多线程，这样会引起以下问题：
- 过多线程之间的切换会加重系统的负载，造成系统资源紧张且响应不及时；
- 频繁的进行线程的创建及销毁以及线程间同时无序的竟争系统资源加重了系统的负载。
thread_pool正是为了解决以上问题而产生的；
三、thread_pool
thread_pool(线程池)，是指mysql 创建若干工作线程来共同处理所有连接的用户请求，用户的请求的方式不再是 ‘one thread per connection’，而是多个线程共同接收并处理多个连接的请求，在数据库的底层处理方面(mysql_execute_command)，单线程的处理方式和线程池的处理方式是一致的。
四、thread_pool 的工作原理
启动 thread_pool 的mysql 会创建thread_pool_size 个thread group , 一个timer thread, 每个thread group 最多拥有thread_pool_oversubscribe个活动线程，一个listener线程，listener线程负责监听分配到thread group中的连接，并将监听到的事件放入到一个queue中，worker线程从queue中取出连接的事件并执行具体的操作，执行的过程和one thread per connection 相同。timer threaad 则是为了监听各个threadgroup的运行情况，并根据是否阴塞来创建新的worker线程。
1、thread_pool 建立连接
thread_pool 建立连接的堆栈如下：
mysqld_main handle_connections_sockets create_new_thread tp_add_connection queue_put
2、worker 处理请求
thread group中的 worker 处理请求的堆栈如下：
0 mysql_execute_command 1 0x0000000000936f40 in mysql_parse 2 0x0000000000920664 in dispatch_command 3 0x000000000091e951 in do_command 4 0x0000000000a78533 in threadpool_process_request 5 0x000000000066a10b in handle_event 6 0x000000000066a436 in worker_main 7 0x0000003562e07851 in start_thread () 8 0x0000003562ae767d in clone ()
其中worker_main函数是woker线程的主函数，调用mysql本身的do_command 进行消息解析及处理，和one_thread_per_connection 是一样的逻辑； thread_pool 自行控制工作的线程个数，进而实现线程的管理。
3、thread_pool中线程的创建
- listener线程将监听事件放入mysql放入queue中时，如果发现当前thread group中的活跃线程数active_thread_count为零，则创建新的worker 线程；
- 正在执行的线程阻塞时，如果发现当前thread group中的活跃线程数active_thread_count为零，则创建新的worker 线程；
- timer线程在检测时发现没有listener线程且自上次检测以来没有新的请求时会创建新的worker线程，其中检测的时间受参数threadpool_stall_limit控制；
- timer线程在检测时发现没有执行过新的请求且执行队列queue 不为空时会创建新的worker线程；
worker线程的伪码如下：
4、thread_pool中线程的销毁
当从队列queue中取出的connection为空时，则此线程销毁，取connection所等待的时间受参数thread_pool_idle_timeout的控制； 综上，thread_pool通过线程的创建及销毁来自动处理worker的线程个数，在负载较高时，创建的线程数目较高，负载较低时，会销毁多余的worker线程，从而降低连接个数带来的影响的同时，提升稳定性及性能。同时，threadpool中引入了Timer 线程，主要做两个事情。
- 定期检查每个thread_group是否阻塞，如果阻塞，则进行唤醒或创建线程的工作；
- 检查每个thread_group中的连接是否超时，如果超时则关掉连接并释放相应的资源；
五、线程池实现
下图是线程池的实现框架，以及关键接口
每一个绿色的方框代表一个group，group数目由thread_pool_size参数决定。每个group包含一个优先队列和普通队列，包含一个listener线程和若干个工作线程，listener线程和worker线程可以动态转换，worker线程数目由工作负载决定，同时受到thread_pool_oversubscribe设置影响。此外，整个线程池有一个timer线程监控group，防止group“停滞”。
关键接口
1. tp_add_connection[处理新连接]
1) 创建一个connection对象 2) 根据thread_id%group_count确定connection分配到哪个group 3) 将connection放进对应group的队列 4) 如果当前活跃线程数为0，则创建一个工作线程
2. worker_main[工作线程]
1) 调用get_event获取请求 2) 如果存在请求，则调用handle_event进行处理 3) 否则，表示队列中已经没有请求，退出结束。
3. get_event[获取请求]
1) 获取一个连接请求 2) 如果存在，则立即返回，结束 3) 若此时group内没有listener，则线程转换为listener线程，阻塞等待 4) 若存在listener，则将线程加入等待队列头部 5) 线程休眠指定的时间(thread_pool_idle_timeout) 6) 如果依然没有被唤醒，是超时，则线程结束，结束退出 7) 否则，表示队列里有连接请求到来，跳转1
备注：获取连接请求前，会判断当前的活跃线程数是否超过了thread_pool_oversubscribe+1，若超过了，则将线程进入休眠状态。
4. handle_event[处理请求]
1) 判断连接是否进行登录验证，若没有，则进行登录验证 2) 关联thd实例信息 3) 获取网络数据包，分析请求 4) 调用do_command函数循环处理请求 5) 获取thd实例的套接字句柄，判断句柄是否在epoll的监听列表中 6) 若没有，调用epoll_ctl进行关联 7) 结束
5.listener[监听线程]
1) 调用epoll_wait进行对group关联的套接字监听，阻塞等待 2) 若请求到来，从阻塞中恢复 3) 根据连接的优先级别，确定是放入普通队列还是优先队列 4) 判断队列中任务是否为空 5) 若队列为空，则listener转换为worker线程 6) 若group内没有活跃线程，则唤醒一个线程
备注：这里epoll_wait监听group内所有连接的套接字，然后将监听到的连接
请求push到队列，worker线程从队列中获取任务，然后执行。
6. timer_thread[监控线程]
1) 若没有listener线程，并且最近没有io_event事件 2) 则创建一个唤醒或创建一个工作线程 3) 若group最近一段时间没有处理请求，并且队列里面有请求，则 4) 表示group已经stall，则唤醒或创建线程 5）检查是否有连接超时
备注：timer线程通过调用check_stall判断group是否处于stall状态，通过调用timeout_check检查客户端连接是否超时。
7.tp_wait_begin[进入等待状态流程]
1) active_thread_count减1，waiting_thread_count加1 2）设置connection->waiting= true 3) 若活跃线程数为0，并且任务队列不为空，或者没有监听线程，则 4) 唤醒或创建一个线程
8.tp_wait_end[结束等待状态流程]
1) 设置connection的waiting状态为false 2) active_thread_count加1，waiting_thread_count减1
备注：
- waiting_threads这个list里面的线程是空闲线程，并非等待线程，所谓空闲线程是随时可以处理任务的线程，而等待线程则是因为等待锁，或等待io操作等无法处理任务的线程。
- tp_wait_begin和tp_wait_end的主要作用是由于汇报状态，即使更新active_thread_count和waiting_thread_count的信息。
9. tp_init/tp_end
分别调用thread_group_init和thread_group_close来初始化和销毁线程池
六、线程池与连接池
连接池通常实现在Client端，是指应用(客户端)创建预先创建一定的连接，利用这些连接服务于客户端所有的DB请求。如果某一个时刻，空闲的连接数小于DB的请求数，则需要将请求排队，等待空闲连接处理。通过连接池可以复用连接，避免连接的频繁创建和释放，从而减少请求的平均响应时间，并且在请求繁忙时，通过请求排队，可以缓冲应用对DB的冲击。
线程池实现在server端，通过创建一定数量的线程服务DB请求，相对于one-conection-per-thread的一个线程服务一个连接的方式，线程池服务的最小单位是语句，即一个线程可以对应多个活跃的连接。通过线程池，可以将server端的服务线程数控制在一定的范围，减少了系统资源的竞争和线程上下文切换带来的消耗，同时也避免出现高连接数导致的高并发问题。连接池和线程池相辅相成，通过连接池可以减少连接的创建和释放，提高请求的平均响应时间，并能很好地控制一个应用的DB连接数，但无法控制整个应用集群的连接数规模，从而导致高连接数，通过线程池则可以很好地应对高连接数，保证server端能提供稳定的服务。
如图所示，每个web-server端维护了3个连接的连接池，对于连接池的每个连接实际不是独占db-server的一个worker，而是可能与其他连接共享。
这里假设db-server只有3个group，每个group只有一个worker，每个worker处理了2个连接的请求。
七、threadpool相关参数
1、thread_pool_high_prio_mode
有三个取值：transactions / statements / none
- transactions(default): 使用优先队列和普通队列，对于事务已经开启的statement，放到优先队列中，否则放到普通队列中
- statements：只使用优先队列
- none: 只是用普通队列，本质上和statements相同，都是只是用一个队列
2、thread_pool_high_prio_tickets
取值0~4294967295，当开启了优先队列模式后（thread_pool_high_prio_mode=transactions），每个连接最多允许thread_pool_high_prio_tickets次被放到优先队列中，之后放到普通队列中，默认为4294967295
3、thread_pool_idle_timeout
worker线程最大空闲时间，单位为秒，超过限制后会退出，默认60
4、thread_pool_max_threads
threadpool中最大线程数目，所有group中worker线程总数超过该限制后不能继续创建更多线程，默认100000
5、thread_pool_oversubscribe
一个group中线程数过载限制，当一个group中线程数超过次限制后，继续创建worker线程会被延迟，默认3
6、thread_pool_size
threadpool中group数量，默认为cpu核心数，server启动时自动计算
7、thread_pool_stall_limit
timer线程检测间隔，单位为毫秒，默认500
八、threadpool优化
1.调度死锁解决
引入线程池解决了多线程高并发的问题，但也带来一个隐患。假设，A，B两个事务被分配到不同的group中执行，A事务已经开始，并且持有锁，但由于A所在的group比较繁忙，导致A执行一条语句后，不能立即获得调度执行；而B事务依赖A事务释放锁资源，虽然B事务可以被调度起来，但由于无法获得锁资源，导致仍然需要等待，这就是所谓的调度死锁。由于一个group会同时处理多个连接，但多个连接不是对等的。比如，有的连接是第一次发送请求；而有的连接对应的事务已经开启，并且持有了部分锁资源。为了减少锁资源争用，后者显然应该比前者优先处理，以达到尽早释放锁资源的目的。
因此mysql数据库在group里面添加一个优先级队列，将已经持有锁的连接，或者已经开启的事务的连接发起的请求放入优先队列，工作线程首先从优先队列获取任务执行。
2.大查询处理
假设一种场景，某个group里面的连接都是大查询，那么group里面的工作线程数很快就会达到thread_pool_oversubscribe参数设置值，对于后续的连接请求，则会响应不及时(没有更多的连接来处理)，这时候group就发生了stall。通过前面分析知道，timer线程会定期检查这种情况，并创建一个新的worker线程来处理请求。如果长查询来源于业务请求，则此时所有group都面临这种问题，此时主机可能会由于负载过大，导致hang住的情况。这种情况线程池本身无能为力，因为源头可能是烂SQL并发，或者SQL没有走对执行计划导致，通过其他方法，比如SQL高低水位限流或者SQL过滤手段可以应急处理。但是，还有另外一种情况，就是dump任务。很多下游依赖于数据库的原始数据，通常通过dump命令将数据拉到下游，而这种dump任务通常都是耗时比较长，所以也可以认为是大查询。如果dump任务集中在一个group内，并导致其他正常业务请求无法立即响应，这个是不能容忍的，因为此时数据库并没有压力，只是因为采用了线程池策略，才导致了请求响应不及时，为了解决这个问题，mysql数据库将group中处理dump任务的线程不计入thread_pool_oversubscribe累计值，避免上述问题。
