数据库内核月报 － 2019 / 02

随着信息技术的进步，各行各业对数据价值的重视程度急剧上升，越来越多的数据被分门别类地积聚下来，对数据库的并发要求越来越高，即同一时间点的数据请求越来越多，对实时性的要求也越来越高。实时性其实是不经过批量排队的高并发实时请求的代名词，同一时间的请求量和请求的处理速度直接决定了并发度：

并发度 = 单位时间请求数/单位时间处理能力

以日常生活中的高铁买票为例，假设每秒钟要卖出1000张票，每张表的处理时间为1s（单个窗口每秒处理1张票），则需要1000个售票窗口。因为物理资源的限制，实际上无法建设1000个售票窗口，大家只好在售票窗口前排起队来。MySQL数据库也是如此，CPU的核数可以理解为物理资源的限制（相当于售票员，假设还无法自动），每一个线程可以理解为一个售票窗口，每一个事务或查询可以理解为买票动作，每个购票者可以理解为一个连接，默认的请求处理方式是每个人都有一个专用的售票窗口，需要售票员跑来跑去（CPU上下文切换，售票窗口越多，跑起来越费力）来为你服务，可以看到这是不够合理的，特别是售票员比较少而购票者很多的场景。为了提升MySQL的处理效率，Oracle官方和Percona / MariaDB都实现了线程池机制（Thread Pool），不再是每个人都有一个专用的售票窗口（每个客户端对应一个后端线程），通过限定售标窗口数，让购票者排队，来减少售票员跑来跑去的成本。

优化思路

这个看似合理的线程池机制，在实际的应用场景中使用极少，原因是它的设计不够合理。同样以高铁购票为例，有的购票者是去现场买票（需要临时决策，花费较长时间，类似于数据库中的事务），有的购票者是直接指定车次快速付款或者直接取票（花费较短时间，类似于数库中的查询或简单更新）。MySQL线程池现在的实现机制就是不区分买票和取票，统一排队共享资源池（默认买票优先，取票操作会被延后），完全没有队列的概念，导致高并发的更新或事务操作会阻止短平快的小查询，对于取票者来讲，是极不合理的（假定读者都做过高铁取票者）。

MySQL线程池目前只有一层排队，即从网络接收请求进行排队，实现线程资源共享，即不知道是购票还是取票，大家共排一个队列。改进的方法是引入多层队列，第一层队列接收网络请求，读取网络包，再根据网络包进行操作类型识别，区分是购票还是取票操作，再引导到购票队列和取票队列。再进行合理的购票窗口和取票窗口配比，使得购票（大操作）和取票（小操作）不会有严重的相互阻塞。从网络包中可以分析操作类型，并得到SQL语句，并可以根据SQL语句类型和事务上下文，将操作分为以下四类：

• 查询操作，会话处于自动提交模式，SQL类型为查询语句。
• 更新操作，会话处于自动提交模式，SQL类型为DML语句。
• 事务操作，会话处于事务模式（start transaction或autocommit=0)下的任何语句。
• 管理操作，以上之外的操作，比如“show”、“set”等操作。

相对应的，可以在线程池中，实现真正的队列机制，进行更加合理和先进的排队机制。如下所示：

• 第一层队列为网络请求队列，可以区分为请求队列（不在事务状态中的请求）和高优先级队列（已经在事务状态中的请求，收到请求后会马上执行，不进入第二队列）。
• 第二层队列为工作任务队列，可以区分为查询队列、更新队列和事务队列。第一层请求队列的请求经过快速的处理和分析进入第二层队列。如果是管理操作，则直接执行，假定所有管理操作都是小操作。

对第二层队列，可以分别设置一个允许的并发度（可以接近售票员/CPU的个数），以实现总线程数的控制。只要线程数大于四类操作的设计并发度之和，则不同类型的操作不会互相干涉（在这里是假定同一操作超过各自并发度而进行排队是合理的）。任何一个队列超过一定的时间，如果没有售出任何票，处于阻塞模式，则可以考虑放行，在MySQL线程池中有“thread_pool_stall_limit”变量来控制这个间隔，以防止任何一个队列挂起。

可以从配置参数的变化来了解优化后的线程池工作机制：

• thread_pool_oversubscribe：每个Thread Group的目标线程数
• thread_pool_normal_weights：查询、更新操作的目标线程比例（假定这两类操作的比重相同），即并发度= thread_pool_oversubscribe * 目标比例/100。
• thread_pool_trans_weights：事务操作的目标线程比例，即并发度= thread_pool_oversubscribe * 目标比例/100。
• thread_pool_stall_limit：阻塞模式检查频率（同时检查5个队列的状态）
• thread_pool_size：线程组的个数（在优化锁并发后，线程组的个数不是很关键，可以用来根据物理机器的资源配置情况来软性调节处理能力）

线程池优化的思路是将线程池从单一的对操作类无感知的无优先级资源共享队列，变成可感知操作类型的优先级队列，实现相同操作排队，不同操作相互之间无干扰的目标。相较于原始的线程池，优化后的线程池，可以使用一个连接地址来适应不同类型的操作请求，不再需要前端应用仔细设计请求队列，降底应用研发的要求和成本。

效果测试

测试的版本为内部实验室版本，在公共云上并不可见。下面进行TPCC 1000DW的测试，并发数为1000，使用“show status like ‘thread%’”查看线程数的结果为：

mysql> show status like 'thread%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 31    |
| Threadpool_threads      | 179   |
| Threadpool_wait_threads | 23    |
| Threads_cached          | 0     |
| Threads_connected       | 1001  |
| Threads_created         | 179   |
| Threads_running         | 172   |
+-------------------------+-------+
7 rows in set (0.02 sec)

可以看到，总共创建了179个线程，服务了1000个客户端压测连接，每秒的事务数约为5000，TpmC值约为8万。接下来使用3000并发连接进行测试，结果如下所示：

mysql> show status like 'thread%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 32    |
| Threadpool_threads      | 179   |
| Threadpool_wait_threads | 24    |
| Threads_cached          | 0     |
| Threads_connected       | 3001  |
| Threads_created         | 179   |
| Threads_running         | 172   |
+-------------------------+-------+
7 rows in set (0.05 sec)

可以看到线程数并没有上涨，同样是用179个线程来服务了3000个客户端连接，TpmC值约为8万，没有看到总体TPS的损失，并且“show”执行的速度比较快，没有阻塞的感觉。

适用场景

优化后的线程池也并不是万能的，在以下几种场景中表现会不够理想：

• 有较高的大查询（指一次查询需要一秒钟以上）并发，如果累计的大查询并发度超过总的查询并发度，则大查询会累积起来，阻止小查询。同理的大的更新也会阻止小的更新，当然这样的场景下，不管是否使用线程池，数据库的表现都是不够理想的，需要应用侧控制大查询的并发度。
• 有较严重的锁冲突，如果处于锁等待的并发度超过总的处理并发度，也会累积起来，阻止无锁待的处理请求。当然这样的场景下，不管是否使用线程池，数据库的表现都是不够理想的，需要应用层进行优化。
• 极高并发的Prepared Statement请求，使用Prepared Statement（Java应用不算）时，会使用MySQL Binary Protocol，会增加很多的网络来回操作，比如参数的绑定、结果集的返回，在极高请求压力下会给epoll监听进程带来一定的压力，处于事务状态中时，会让第一层队列的普通请求得不到执行机会。

当处于积累以后，每种类型的操作，都会等待一个阻塞时间，由参数“thread_pool_stall_limit”控制。