一次数据库连接池优化的实践剖析

问题背景：MySQL 线程数只升不降

一段时间以来，XXX 部门开放平台 OPENXXX 系统在业务高峰频繁出现 MySQL 线程数升高的现象。升高本身不是问题，问题是随着业务高峰过去，QPS 下来后 ，MySQL 线程数却依然居高不下，这是什么原因？

思考方向上，大家都知道，MySQL 是通过线程池来进行线程管理的，基于过往经验，上述情况很可能是线程池的配置策略不合理导致线程创建后无法及时释放，而实际上线程的利用率是很低的————这一点通过分析系统线程也可以看到，waiting 态线程占据 MySQL 总线程数的一半有余（见下图）。

落地实践上，方向虽然是明确的，但具体是 MySQL 的哪一项策略配置不合理、又该做怎样的调整，需要做细致的调研分析才能回答。由此发起 MySQL 线程的优化治理专项。

追根溯源：问题根源的分析定位

对比业务高峰前后的 MySQL 线程，发现飙升的主要是 [MySQL Statement Cancellation Timer] ，由此引出第一阶段问题，[MySQL Statement Cancellation Timer] 线程是从哪里来的？

一、Timer 线程生命周期

走读代码流程，梳理得到 Timer 线程的生命周期，如下图所示（Timer 节点以及问题节点已标识）——

二、生命周期详细解读

Timer 线程创建链路

dump 现场线程，配合线程 stack 走读 mysql connector jar 的代码。

1、定位代码，java.util.TimerThread#run—— TimerThread 是 mysql-connector-java-xxx.jar 中的 Timer 的一个内部类，等待 Timer 队列中的任务以执行、调度

2、顺藤摸瓜，可以追到 [MySQL Statement Cancellation Timer] 线程的生成链路

com.mysql.jdbc.ConnectionImpl#getCancelTimer

3、查看 getCancelTimer 的上游调用 ，主要是 mysql-connector-java-xxx.jar 中的主管 sql 查询的 Statement

com.mysql.jdbc.StatementImpl#executeQuery

小结

走读 [MySQL Statement Cancellation Timer] 线程的调用链逻辑，可以抽象 3 点核心信息——

• Timer 线程是 mysql connection 连接维度的
• 应用开启 mysql 的 queryTimeouts 且 timeoutInMillis != 0 的话，伴随每一个连接的创建，都会同步开启一个 Timer 线程，以进行超时控制
• Timer 线程便是之前通过 jstack 抓取到的 DB 异常线程 [MySQL Statement Cancellation Timer]

可以推断 OPENXXX 应用必定开启了 queryTimeout。查看 mybatis-config.xml，确定在每次 DB 查询的时候，均插上了 queryTimeout——defaultStatementTimeout 设置对全局 sql 生效，包括 insert、select、update

Timer 线程销毁链路

jdk 规范保证，任何线程都有自身的退出机制。查看 Statement 中 cancelTask 的执行过程，依次追溯。

1、com.mysql.jdbc.StatementImpl.CancelTask#run——调用 Connection 进行 cancel

2、com.mysql.jdbc.ConnectionImpl#close

3、com.mysql.jdbc.ConnectionImpl#realClose——关闭 Timer 线程

小结

至此获取到 [MySQL Statement Cancellation Timer] 线程的 cancel 链路，走读代码逻辑，抽象核心信息——连接关闭时，会调用 Connection.close 方法 cancel 掉 Timer 线程，即 [MySQL Statement Cancellation Timer] 线程。

三、核心问题总结定位

连接创建时，queryTimeout 会使 jdbc driver 新建 cancelTask 并使用 Timer 进行调度，一旦 sql 查询超时则执行 cancel 动作；连接关闭时，调用 Connection 以 cancel 掉 Timer 线程。

问题来到第二个阶段：既然连接超时关闭的时候，才会将 Timer 线程 cancel 掉，那么控制超时的具体是哪些策略呢？

超时策略：MySQL 线程数下降的关键

对于选型关系型数据库的应用而言，数据库的连接关闭策略自上而下由两层组成：1、JDBC；2、Mysql，经由各层的一系列超时参数进行控制。需要注意的是，网络文档对各层各参数的释义大多不够精准，甚至相互矛盾。以下参数分析均来自官方文档，并随载官方链接以便详细查阅。

一、JDBC 层（c3p0）

连接超时参数

• maxIdleTime：在从池中剔除连接之前，允许连接闲置多少秒

有效性检测参数

1. idleConnecnTestPeriod：定时检测池中空闲连接的周期，用以校验连接的有效性
2. testConnectionOnCheckin：连接提交时，异步校验其有效性
3. testConnectionOnCheckout：连接回收时，同步校验其有效性
4. preferredTestQuery：连接的有效性校验语句。JDBC4 的连接包括一个名为 isvalid（）的方法，该方法可作为快速、可靠的连接测试来执行

解读一下

1. testConnectionOnCheckin 是一个异步检测参数而非同步参数，connection 提交指的是 connection 连接到 mysql server 而非执行任务
2. 3个检测参数是搭配使用的——连接提交后，会开启连接状态的定期检测机制（testConnectionOnCheckin 为 true），即每 30 秒（idleConnectionTestPeriod=30）通过 SELECT 1（preferredTestQuery=SELECT 1）语句检测一次连接状态，从而降低 Communications link failure 的发生概率

关于参数的 Q&A

设置完连接超时参数 maxIdleTime 之后，有必要设置有效性检测参数么——两者的关系是：连接空闲超过 maxIdleTime 后，就会被 mysql server 断开。但此时连接池并没有回收这个连接，直到连接池检测到该连接已被废弃后，才会进行回收。在这个时间段内，如果客户端使用了这个连接，就会报错：Communications link failure。

二、DB Server 层

wait_timeout：mysql server 关闭连接之前，允许连接闲置多少秒。默认是 28800，单位秒，即 8 个小时

三、超时策略探究总结

既然 jdbc 层面以及 mysql 层面都有完备的连接关闭策略，那么问题来到第三个阶段：OPENXXX 系统自身的配置策略是怎样的？

系统分析：OPENXXX 的超时关闭策略

依据上文调研的连接关闭策略，摸查 OPENXXX 应用，1、JDBC；2、Mysql。

一、JDBC 层（c3p0）

OPENXXX 在 jdbc 层面未配置连接关闭策略（无 maxIdleTime），如此一来，只能依赖下层 mysql 的 timeout 机制进行连接的关闭。但实际上，mysql server 能否关掉连接呢？

二、DB Server 层

1、查询 mysql server 的 wait_timeout 参数，观察 DB 设定的连接超时配置——[select variable_name,variable_value from information_schema.session_variables where variable_name like 'wait_timeout']

2、查询 mysql server 的 Threads_connected 参数，观察 DB 当前打开的连接数——[show status where variable_name = 'Threads_connected']

3、查询 DB 日常的 qps

4、汇总信息：connectionSize～800，qps～800，keepAliveTime～28800s。由此计算线程释放的概率：(qps keepAliveTime) / connectionSize，即 80028800/800=28800——意味着每个连接在关闭之前，有 28800 次机会拿到任务而不被终止。这种概率下，连接是不可能释放的，连接空置率也会很高。

三、系统分析判断印证

• 查询 DB 日常的总连接数，可以看到连接无法主动释放

• 查询 DB 当前所有连接的运行情况，可以看到连接空置率很高，绝大多数处于 idle 状态——[show full processlist] 查看，其中 Command 标识连接的运行状态，比如：Sleep，Query，Connect 等

解决方案：恰当的超时关闭策略

通过配置 jdbc 层的连接关闭策略，及时关掉空闲连接，从而确保 timer 线程的 cancle。问题来到第四个阶段：如何配置 OPENXXX 的连接关闭策略？

一、建议方案

实际上，官方已经给出了建议：

The most reliable time to test Connections is on check-out. But this is also the most costly choice from a client-performance perspective. Most applications should work quite reliably using a combination of idleConnectionTestPeriod and testConnectionOnCheckin. Both the idle test and the check-in test are performed asynchronously, which can lead to better performance, both perceived and actual.

最可靠的连接测试时机是在 connection 回收时进行（testConnectionOnCheckout），但从系统性能的角度来看，这也是最耗费性能的选择。大多数应用程序应该组合使用 idleConnectionTestPeriod 和 testconConnectionCheckin，一方面可以保证系统非常可靠地运行，另一方面空闲测试和提交测试都是异步执行的，这会带来更好的系统性能。

Set idleConnectionTestPeriod to 30, fire up you application and observe. This is a pretty robust setting, all Connections will tested on check-in and every 30 seconds thereafter while in the pool. Your application should experience broken or stale Connections only very rarely, and the pool should recover from a database shutdown and restart quickly

将 idleConnectionTestPeriod 设置为 30，启动系统并观察。这是一个非常健壮的设置，所有连接都将在提交时进行测试，之后每隔 30 秒在池中进行一次测试。这样应用程序可以很少拿到断开或过时的连接，并且可以在 DB 重启之后支持连接的快速恢复。

二、策略配置

<property name="preferredTestQuery">SELECT 1</property> <!-- 有效性检测语句 -->
<property name="testConnectionOnCheckin">true</property> <!-- 提交连接时校验连接的有效性。Default: false -->=
<property name="idleConnectionTestPeriod">30</property> <!-- 每 30 秒检查连接池中的空闲连接。若为 0 则永不检测。Default: 0 -->
<property name="maxIdleTime">30</property> <!-- 最大空闲时间，30 秒内未使用连接被丢弃。若为 0 则永不丢弃。Default: 0 -->

测试验证：超时策略的有效性验证

test 环境进行测试，验证配置策略的有效性，三步走：
1、高 qps，mock db 流量，重复发起 query 请求——观察 cat 堆栈，是否生成大量的 [MySQL Statement Cancellation Timer]
2、低 qps，mock db 流量，间隔发起 query 请求——观察 cat 堆栈，是否开始缩减 [MySQL Statement Cancellation Timer]
3、无 qps，关闭 db 流量——观察 cat 堆栈，无 [MySQL Statement Cancellation Timer]

一、测试代码展示

@Controller
@RequestMapping("/dbtimer")
public class DBTimerController {
    @Resource
    private PushCallbackService callbackService;
    @Autowired
    private MccClient mccClient;
    private static final Logger LOGGER = LoggerFactory.getLogger(DBTimerController.class);
    private static final ExecutorService executorService = Executors.newFixedThreadPool(50);//创建线程池

    @ResponseBody
    @RequestMapping(value = "/dbtest", method = RequestMethod.GET)
    @Ignore("工具接口,无需鉴权")
    public void dbTest() {
        TimerQ timerQ = new TimerQ();
        for (int i = 0; i < 50; i++) {
            executorService.execute(new TimerR(timerQ));
        }
    }

    @ResponseBody
    @RequestMapping(value = "/dbtestdown", method = RequestMethod.GET)
    @Ignore("工具接口,无需鉴权")
    public void dbTestDown() {
        executorService.shutdown();
    }

    class TimerQ {
        public void queryTimer() throws InterruptedException {
            int i = 1;
            while (Boolean.valueOf(mccClient.getValue("mcc_timer_query_switch"))) {
                CallbackLog callbackLog = callbackService.querybyid(i);
                if (callbackLog==null) {
                    continue;
                }
                LOGGER.warn("query timer, callbackInfo:{}", callbackLog.getId());
                ++i;
                if (Boolean.valueOf(mccClient.getValue("mcc_timer_sleep_switch"))) {
                    Thread.sleep(Long.valueOf(mccClient.getValue("mcc_timer_time_switch")));
                }
            }
        }
    }

    class TimerR implements Runnable {
        private TimerQ timerQ;

        public TimerR(TimerQ timerQ) {
            this.timerQ = timerQ;
        }

        @Override
        public void run() {
            try {
                timerQ.queryTimer();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
  
}

二、3 重场景验证

1、初始阶段
MySQL Statement Cancellation Timer 线程数为 0
2、高 qps——生成大量的 [MySQL Statement Cancellation Timer]
MySQL Statement Cancellation Timer 线程数为 50
3、低 qps——[MySQL Statement Cancellation Timer] 开始缩减
MySQL Statement Cancellation Timer 线程数为 35
4、无 qps——无 [MySQL Statement Cancellation Timer]
MySQL Statement Cancellation Timer 线程数为 0

三、上线效果展示

详见 OPENXXX 系统 DB 异常线程优化案——总结报告。一句话总结：DB 连接超时策略的引入，可以及时有效的关闭连接，进而关闭 [MySQL Statement Cancellation Timer]，使得 OPENXXX 系统线程表现出了良好的业务弹性，且未损失原有的 sql 性能。

个人总结：抽象提炼优化方法

本次问题的表象是明确的，但掩藏的内核是艰深的。历经「三方包代码（原理）— jdbc（c3p0 文档）— mysql server(manual 文档) — openXXX(分析) — 测试（验证）」，个人尽力呈现本次优化实践从调研到上线的完整过程，亦收获良多。同时在这里抽象、提炼一下，主要是个人对于 DB 线程调优的提纲式整理，方便各位同学进行参考，寻找优化思路——