通过线程池解决连接数突增问题
He3DB He3DB
数据库的运行，有两个池的概念，一个是连接池，面向的是数据库连接，是针对数据库Client侧的优化；另一个是线程池，面向的是数据库内的工作线程，是针对数据库Server侧的优化。本期深入解析移动云RDS MySQL产品中线程池解决方案。
连接池可将数据库连接数固定在一定范围内，避免业务端创建过多连接，超出Server端的最大承载，并支持业务端请求的复用，节省业务侧的建连时间。
线程池将工作线程数量固定在一定范围内（当连接数过多时会涉及排队），可避免并发过高时频繁上下文切换、缓存失效等问题，有效提升CPU利用率及数据库吞吐。
连接池的局限和线程池的提出
社区版MySQL的连接处理方法默认是 one-thread-per-connection ，即为每个连接创建一个工作线程，简称每 线程（Per_thread）模式。这种模式存在如下弊端：
由于系统的资源是有限的，随着连接数的增加，资源的竞争也会增加，连接的响应时间也随之增加，如 response time图所示。
在资源未耗尽时，数据库整体吞吐随着连接数增加。一旦连接数超过了某个耗尽系统资源的临界点，由于各线程互相竞争， CPU时间片在大量线程间频繁调度，不同线程上下文频繁切换，徒增系统开销，数据库整体吞吐反而会下降，如下图所示：
那么，如何避免在连接数暴增时，因资源竞争而导致系统吞吐下降？MariaDB & Percona的答案是：线程池。
线程池的原理，大致可类比为早高峰期间需要通过同一座桥的大量汽车，如果采用 one-thread-per-connection 的方式类似放任汽车自由行驶，由于桥面宽度有限，最终将导致汽车拥堵、寸步难行。线程池的解决方案是限制同时行驶的汽车数，让桥面时刻保持最大吞吐，尽快让所有汽车通过大桥。
MySQL默认的线程模式，是为每个会话都创建一个独占的线程。所以当有大量的会话存在时，会导致大量的资源竞争，同时，大量的系统线程调度和缓存失效也会导致性能急剧下降。此时通过线程池实现线程复用，可以极大的缓解此类问题：
当连接多、并发低时，通过连接复用，避免创建大量空闲线程，减少系统资源开销；
当连接多、并发高时，通过限制线程数，可避免线程调度频繁和缓存失效堆积，减少线程池间上下文切换和热锁争用，从而对OLTP场景产生积极影响。
当连接数上升时，在线程池的帮助下，可将数据库整体吞吐维持在一个较高水准，如下图所示：
适用场景
线程池采用一定数量的工作线程来处理连接请求，在查询相对较短且工作负载受CPU限制的情况下效率最高，通常适应于以下OLTP 工作负载场景：
l 针对大量连接的OLTP短查询场景；
l 针对大量连接的只读短查询场景；
l 针对大量连接高并发下数据库性能衰减场景。
如果工作负载不受CPU限制，那么仍然可以通过限制线程数量来节省数据库内存缓冲区所占用的内存。
但也有其局限性，当请求偏向于慢查询时，工作线程阻塞在高时延操作上，难以快速响应新的请求，导致系统吞吐量反而相较于传统 one-thread-per-connection 模式更低。因此，不太适用于以下场景：
l 具有突发工作负载的场景。在这种场景下，许多用户往往长时间处于非活跃状态，但个别时候又处于特别活跃 的状态，同时，对延迟的容忍度较低，因此，线程池节流效果不太理想。不过，即使在这种情况下，也可以通 过调整线程的退役频率（ thread_pool_idle_timeout 参数）来提高性能。
l 高并发、长耗时语句的场景。在这种场景下，并发较多，且都是执行时间较长的语句，会导致工作线程堆积，一旦达到上限，完全阻止后续语句的执行，比如最常见的数据仓库场景。当然这样的场景下，不管是否使用线程池，数据库的表现都是不够理想的，需要应用侧控制慢查询的并发度。
l 有较严重的锁冲突的场景。如果处于锁等待的工作线程数超过总线程数，也会堆积起来，阻止无锁等待的处理 请求。比如某个会话执行 FLUSH TABLES WITH READ LOCK 语句获得全局锁后暂停， 那么其他执行写操作的 客户端连接就会阻塞，当阻塞的数量超过线程池的上限时，整个 server 都会阻塞。当然这样的场景下，不管是否使用线程池，数据库的表现都是不够理想的，需要应用侧进行优化。
l 极高并发的Prepared Statement请求。使用Prepared Statement时，会使用MySQL Binary Protocol，会 增加很多的网络开销，比如参数的绑定、结果集的返回，在极高请求压力下会给epoll监听进程带来一定的压力，处于事务状态中时，可能会让普通请求得不到执行机会。
为了应对上述的阻塞问题，一般会允许配置extra_port或admin_port来管理连接。
总之， 线程池更适合短连接或短查询的场景。
解决方案
市面上的线程池方案大多都借鉴了 percona 、mariadb 的方案：
l 腾讯云TXSQL整合了percona的线程池方案，在此基础上实现了线程池的动态切换（动态开启或关闭线程池）、负载均衡优化（percona分配线程组时采用的轮询算法，TXSQL做了改进）。
l 阿里云AliSQL一定程度上也借鉴了percona的线程池方案，主要不同在于其采用了两层队列，第一层为网络请求队列（区分为普通队列、高优先级队列），第二层为工作任务队列（区分为查询队列、更新队列、事 务队列）。
2.Percona：
l Percona的实现移植自MariaDB，并在此基础上支持了优先级队列，是现在主流的开源线程池方案。其基本原理为：
预先创建一定数量的工作线程（worker线程）。在线程池监听线程（listener线程）从现有连接中监听到新请求时，判断当前请求是否属于高优先级队列，若属于，则放入高优先级队列，反之，则放入低优先级队列；之后，由工作线程按照先高优后低优的顺序来处理请求。工作线程在服务结束之后不销毁线程（处于 idle 状态一段时间后会退出），而是保留在线程池中继续等待下一个请求来临。
3.MariaDB vs Percona：
Percona的实现移植自 MariaDB，并在此基础上添加了一些功能。特别是 Percona 在 5.5-5.7 版本添加了优先级 调度。而 MariaDB 10.2 也支持了优先级调度，和 Percona 的工作方式类似，只是细节有所不同。
l MariaDB 10.2 版本的参数
thread_pool_piority=auto,high,low 对应于 Percona 的thread_pool_high_prio_mode=transactions,statements,none
l MariaDB 10.2 版本中只有处于事务中的连接才是高优先级，而 Percona 中符合高优先级的情况包括： 1）处于事务中； 2）持有表锁； 3）持有 MDL 锁； 4）持有全局读锁； 5）持有 backup 锁。
l 关于避免低优先级队列语句饿死的问题：
Percona有个 thread_pool_high_prio_tickets 参数，用于指定每个连接在高优先级队列中的 tickets 数量，而 MariaDB 没有相应参数。
MariaDB 有个 thread_pool_prio_kickup_timer 参数，可让低优先队列中的语句在等待指定时间 后移入高优先级队列，而 Percona 没有相应参数。
l MariaDB 有参数
thread_pool_dedicated_listener 、 thread_pool_exact_stats ，而 Percona 没有。
thread_pool_dedicated_listener ：可用于指定专有listener线程，其只负责 epoll_wait 等待网 络事件，不会变为 worker 线程。默认为OFF，表示不固定listener。
thread_pool_exact_stats ：是否使用高精度时间戳。
l MariaDB （比如 10.9 版本）在 information_schema 中新增了四张表：
（ THREAD_POOL_GROUPS 、 THREAD_POOL_QUEUES 、 THREAD_POOL_STATS 、 THREAD_POOL_WAITS ），便 于监控线程池状态。
3.MySQL 企业版 vs MariaDB：
MySQL 企业版是在5.5 版本引入的线程池，以插件的方式实现的。
相同点：
l 都具备线程池功能，都支持 thread_pool_size 参数。
l 都支持专有 listener 线程（ thread_pool_dedicated_listeners 参数）。
l 都支持高低优先级队列，且在避免低优先级队列事件饿死方面，二者采用了相同方案，即低优先级队列事件等待一段时间（ thread_pool_prio_kickup_timer 参数）即可移入高优先级队列。
l 都使用相同的机制来探测处于停滞（stall）状态的线程，都提供了 thread_pool_stall_limit 参数（MariaDB 单位是 ms， MySQL 企业版单位是10ms）。
不同点：Windows 平台实现方式不同。
l MariaDB 使用Windows自带的线程池，而 MySQL企业版的实现用到了 WSAPoll() 函数（为了便于移植Unix 程序而提供），因此， MySQL 企业版的实现将不能使用命名管道和共享内存。
l MariaDB 为每个操作系统都使用最高效的 IO 多路复用机制。
Windows：原生线程池；
Linux：epoll
Solaris（event ports）
FreeBSD and OSX （kevent）
l MySQL 企业版只在Linux上才使用优化过的 IO 多路复用机制epoll ，其他平台则用poll 。
移动云方案设计思路
核心功能与 percona 线程池方案类似，优先级调度算法及避免低优先级队列语句饿死的策略也有所参考，除此之外，额外做了一些改进：
1.使用插件方式实现：
借鉴了 MariaDB 的实现，添加了参数 thread_pool_dedicated_listener ，即支持固定 listener 功能。
2.监控线程池状态：
借鉴了 MariaDB 的实现，在 information_schema 中新增了四张表
（ THREAD_POOL_GROUPS 、 THREAD_POOL_QUEUES 、 THREAD_POOL_STATS 、 THREAD_POOL_WAITS ），便 于监控线程池状态
3.优化性能：
l 添加参数 thread_pool_toobusy ，表示线程组是否过于忙碌的线程数阈值。当线程组中活跃的工作线 程数+锁或IO等待中的工作线程数＞该阈值加1时，认为线程组过于忙碌，不再处理低优先级的任务，等 待当前执行的任务和高优先级队列中的任务被处理，直到线程组回到非忙碌的状态。 该优化能避免percona的问题——极端高并发场景下，随着工作线程的持续创建，退化为每线程模式。
l 高优先级 session 独占 worker 线程：在连接数很大，高负载时，对于一些事务取得了锁等资源时，可优先处理。
percona/mariadb的处理逻辑是此类连接发生可读事件后，会被线程组加到优先队列中，等待空闲 worker线程优先处理。
移动云优化后的逻辑，需要优先处理的session不将当前worker还给线程池，继续独占当前worker线程，类似每线程每连接的模式，独占worker线程专用于处理该优先连接之后的所有语句，直到该连接释放了优先资源转为普通连接，例如该连接事务执行结束释放锁资源。
4.利用worker线程
listener 线程调用 io_poll_wait 后，只要线程组不繁忙，则按需批量唤醒或创建一批 worker 线程（根据本次获得的 event 数量、活跃线程数来决定worker数量）。
具体设计方案
1.线程池的架构：
线程池由多个线程组（thread group）和timer线程组成，如下图所示。
线程组的数量是线程池并发的上限，通常而言线程组的数量需要配置成数据库实例的CPU核心数量（通过参数 thread_pool_size 设置），从而充分利用CPU。线程组之间通过 线程ID % 线程组数 的方式分配连接，线程组内通过竞争方式处理连接。
线程池中还有一个服务于所有线程组的timer线程，负责周期性（检查时间间隔为 threadpool_stall_limit 毫 秒）检查线程组是否处于阻塞状态。当检测到阻塞的线程组时， timer线程会通过唤醒或创建新的工作线程来让线程组恢复工作。
创建新的工作线程并不是每次都能创建成功的，要根据当前的线程组中的线程数是否大于线程组中的连接数，活跃线程 数是否为0，以及上一次创建线程的时间间隔是否超过阈值（这个阈值与线程组中的线程数有关，线程组中的线程 数越多，时间间隔越大）等条件来决定。
线程组内部由多个worker线程、0或1个动态listener线程、高低优先级事件队列（由网络事件event构成）、 mutex 、epollfd、统计信息等组成。如下图所示：
worker 线程：主要作用是从队列中读取并处理事件。
l worker 线程：主要作用是从队列中读取并处理事件。
如果该线程所在组中没有listener线程，则该worker线程将成为listener线程，通过epoll的方式监听数据，并 将监听到的event放到线程组中的队列。
worker线程数目动态变化，并发较大时会创建更多的worker线程，当从队列中取不到event时， work线程将 休眠，超过一定时间后结束线程。
一个worker线程只属于一个线程组。
l listener 线程：当高低队列为空，listener线程会自己处理（无论这次获取到多少事务）。否则listener线程会把请求加入到队列中，如果此时active_thread_count=0，则唤醒一个工作线程。
l 高低优先级队列：为了提高性能，将队列分为高优先队列和普通队列。这里采用引入两个新变量
thread_pool_high_prio_tickets 和 thread_pool_high_prio_mode 。由它们控制高优先级队列策略。对每 个新连接分配可以进入高优先级队列的ticket。
2.新连接的创建与分配:
新连接接入时，线程池按照新连接的线程id取模线程组个数来确定新连接归属的线程组（ group_count）。
选定新连接归属的线程组后， 新连接申请被作为事件放入低优先级队列中，等待线程组中worker线程将高优先级事 件队列处理完后，就会处理低优先级队列中的请求。
3.listener线程:
listener线程是负责监听连接请求的线程， 每个线程组都有一个listener线程。线程池的listener采用epoll实现。当epoll监听到请求事件时， listener会根据请求事件的类型来决定将其 放入哪个优先级事件队列。 将事件放入高优先级队列的条件如下：
l 当前线程池的工作模式为高优先级模式，在此模式下只启用高优先级队列。
l 当前线程池的工作模式为事务模式，在此模式下每个连接的event最多被放入高优先级队列 threadpool_high_prio_tickets 次。超过 threadpool_high_prio_tickets 次后，该连接的请求事件 只能被放入低优先级，同时也会重置票数。 以下条件只需要满足其一即可被放入高优先级队列的事件可以优先被worker线程处理：
连接持有表锁
连接持有mdl锁
连接持有全局读锁
连接持有backup锁
只有当高优先级队列为空且当前线程组不繁忙的时候，才处理低优先级队列中的事件。线程组繁忙的判断条件是当前组内活跃工作线程数+组内处于等待状态的线程数大于线程组工作线程额定值（ thread_pool_oversubscribe+1 ）。
listener线程将事件放入高低优先级队列后，如果线程组的活跃worker数量为0，则唤醒或创建新的worker线程来 处理事件。
线程池中listener线程和worker线程是可以互相切换的，详细的切换逻辑会在「worker线程」一节介绍。
l epoll监听到请求事件时，如果高低优先级事件队列都为空，意味着此时线程组非常空闲，大概率不存在活跃 的worker线程。
l listener在此情况下会将除第一个事件外的所有事件按前述规则放入高低优先级事件队列， 然后退出监听任务，亲自处理第一个事件。
l 这样设计的好处在于当线程组非常空闲时，可以避免listener线程将事件放入队列，唤醒或创建worker线程来处理事件的开销，提高工作效率。
4.worker线程:
worker线程是线程池中真正干活的线程，正常情况下，每个线程组都会有一个活跃的worker线程。worker在理想状态下，可以高效运转并且快速处理完高低优先级队列中的事件。但是在实际场景中，worker经常 会遭遇IO、锁等等待情况而难以高效完成任务，此时任凭worker线程等待将使得在队列中的事件迟迟得不到处理， 甚至可能出现长时间没有listener线程监听新请求的情况。为此，每当worker遭遇IO、锁等等待情况，如果此时线 程组中没有listener线程或者高低优先级事件队列非空，并且没有过多活跃worker，则会尝试唤醒或者创建一个worker。
为了避免短时间内创建大量worker，带来系统吞吐波动，线程池创建worker线程时有一个控制单位时间创建 worker线程上限的逻辑，线程组内连接数越多则创建下一个线程需要等待的时间越长。在极端情况下，可能会出现worker线程总数接近最大连接数（max_connections）的情况，相当于退化为每线程模式。
当线程组活跃worker线程数量大于等于 too_many_active_threads+1 时，认为线程组的活跃worker数量过多。此时需要对worker数量进行适当收敛，首先判断当前线程组是否有listener线程：
l 如果没有listener 线程，则将当前worker线程转化为listener线程。
l 如果当前有listener线程，则在进入休眠前尝试通过 epoll_wait 获取一个尚未进入队列的事件，成功获取到 后立刻处理该事件，否则进入休眠等待被唤醒，等待 threadpool_idle_timeout 时间后仍未被唤醒则销毁 该worker线程。
worker线程与listener线程的切换如下图所示：
5.timer线程:
timer线程每隔 threadpool_stall_limit 时间进行一次所有线程组的扫描。当线程组高低优先级队列中存在事件，并且自上次检查至今没有新的事件被worker消费，则认为线程组处于停滞状态。
停滞的主要原因可能是长时间执行的非阻塞请求。
timer线程会通过唤醒或创建新的worker线程来让停滞的线程组恢复工作。
timer线程除上述工作外， 还负责终止空闲时间超过 wait_timeout 秒的客户端。
总结
移动云优化后的线程池会将工作线程数控制在一定范围内，随着并发数的增加，性能基本与最高点持平，无明显下降趋势。
1.功能方面对比：
MySQL企业版MariaDBPercona
移动云
实现方式
插件 非插件非插件插件版本
5.5 版本引入5.5 版本引入，10.2 版 本完善5.5-5.7/8.05.7/8.0借鉴方案-
-MariaDB
Percona +MariaDB 10.2 及之后版本
动态开关线程池
插件式，不支持不支持不支持插件式，不支持优先级处理策略设定高低优先级，且低 优先级事件等待一段时 间可升为高优先级队列设定高低优先级，且低 优先级事件等待一段时 间可升为高优先级队列设定高低优先级， 且限制每个连接在 高优先级队列中的 票数设定高低优先级， 且限制每个连接在 高优先级队列中的 票数监控
-
2个状态变量2个状态变量4张状态信息表
线程池阻塞处理方式：MySQL 8.0.14 以前的版本使用admin_port 功能，（percona & mariadb） 8.0.14及之后版本官方支持admin_port 功能。
2.参数对比
由于业内线程池方案基本都会参考 MariaDB或 Percona，因此，以 Percona和 MariaDB 的参数为准，基于MySQL 8.0，总结其他方案是否有相同或类似参数。
3.监控方面
Percona只有两个状态变量：
Threadpool_threads
Threadpool_idle_threads
移动云借鉴了MariaDB的实现方式，在 information_schema 中增加了四张状态信息表：
l THREAD_POOL_GROUPS 查询线程组相关信息。
l THREAD POOL_QUEUES 查询线程组队列中连接的信息。
l THREAD_POOL_STATS 查询线程组状态信息的统计值，比如线程组由于check_stall创建的线程数、由listener 线程poll到的任务数等。
l THREAD_POOL_WAITS 提供线程组的worker线程在执行SQL语句时，各类等待原因的统计数据。等待原因有：UNKNOWN 、SLEEP 、DISKIO 、ROW_LOCK 、GLOBAL_LOCK 、META_DATA_LOCK 、TABLE_LOCK、 USER_LOCK 、BINLOG 、GROUP_COMMIT 、SYNC 、NET。
作者介绍
卢文双，移动云高级研发工程师，负责RDS MySQL内核功能设计、开发。拥有十年以上单机及分布式数据库内核开发经验，在云计算领域也有丰富的开发实践经验。
