开发者日记

外观

连接堆积避坑实录：sleep 连接与 wait_timeout 联调 ​

问题背景：Sleep 连接为什么会拖垮 MySQL ​

一次典型故障通常这样开始：应用响应变慢，监控显示 MySQL Threads_connected 持续逼近 max_connections，但 SHOW PROCESSLIST 中大量会话的 Command 是 Sleep，Time 从几百秒到数小时不等。此时新请求无法获取连接，业务侧表现为接口超时，而数据库 CPU、IO 可能并不高。

Sleep 连接表示客户端已经建立连接，但当前没有正在执行的 SQL。它本身不是故障，连接池为了复用连接，本来就会保留一定数量的空闲连接；真正的问题是：空闲连接数量长期失控、应用忘记归还连接、网络断链未被发现，或 MySQL、连接池、防火墙三者的超时策略不一致。

治理这类问题不能只改一个参数。正确思路是先识别连接来源和空闲时长，再让数据库超时、连接池空闲回收、应用获取连接校验形成闭环。

排查流程：用系统表定位连接堆积来源 ​

先查看当前会话状态，重点关注 db、user、host、command、time 和 state：

sql
-- 查看当前所有连接，Sleep 连接重点看 Time 与 Host
SHOW FULL PROCESSLIST;

-- MySQL 8.0 推荐使用性能模式会话表，信息更结构化
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 120) AS sql_text
FROM information_schema.processlist
ORDER BY time DESC;

按用户、来源和数据库聚合，可以快速判断是单应用泄漏，还是所有服务都在堆积：

sql
-- 按来源统计 Sleep 连接数量与最长空闲时间
SELECT
    user,
    SUBSTRING_INDEX(host, ':', 1) AS client_ip,
    db,
    COUNT(*) AS sleep_count,
    MAX(time) AS max_idle_seconds,
    AVG(time) AS avg_idle_seconds
FROM information_schema.processlist
WHERE command = 'Sleep'
GROUP BY user, client_ip, db
ORDER BY sleep_count DESC, max_idle_seconds DESC;

再看实例级容量和历史峰值：

sql
-- 查看连接容量、当前连接数、历史峰值
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_clients';
SHOW STATUS LIKE 'Aborted_connects';

判断标准可以简单记为：

  • Threads_connected 长期接近 max_connections，风险高；
  • Max_used_connections / max_connections 长期低于 20%，连接池可能配置过大；
  • Aborted_clients 持续增长，常见于连接被服务端关闭后客户端仍在使用；
  • Sleep 时间集中在某个固定值附近，通常能在 wait_timeout、防火墙 NAT 超时或连接池空闲时间中找到对应关系。

方案对比：杀连接、调大上限，还是治理连接池 ​

方案见效速度优点风险适用阶段
临时 Kill Sleep 会话最快立即释放连接可能中断尚未正确归还的事务，根因仍在故障止血
调大 max_connections快容纳更多连接增加内存与调度压力，可能掩盖泄漏容量确实不足时
调小 wait_timeout中服务端强制回收空闲连接连接池拿到死连接，引发首次请求报错配合连接池校验
优化连接池配置慢但根治控制总量、空闲时间和连接有效性参数组合复杂，需要压测长期治理
修复应用连接泄漏根治避免连接被长期占用需要代码审计与发布必做事项

下面这张图展示了连接从应用到 MySQL 的完整超时链路：

应用请求借还连接连接池最小/最大/空闲回收防火墙或 NAT可能静默断链MySQLwait_timeout联调原则：连接池空闲回收时间 < 网络静默断链时间 < MySQL wait_timeout同时开启借出校验或保活，避免应用拿到失效连接

实操步骤一：确认并调整 wait_timeout ​

wait_timeout 控制交互式以外连接在无活动后被服务端关闭的秒数。注意还有一个 interactive_timeout，对普通连接池通常主要看 wait_timeout，但很多客户端工具会走交互式超时。

sql
-- 查看全局和会话级超时
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- 在线调整为 1800 秒，注意重启后会失效
SET GLOBAL wait_timeout = 1800;
SET GLOBAL interactive_timeout = 1800;

需要持久化时，在 MySQL 8.0 可使用：

sql
-- 持久化到 mysqld-auto.cnf，重启后仍生效
SET PERSIST wait_timeout = 1800;
SET PERSIST interactive_timeout = 1800;

不建议一开始就把它调到 8 小时甚至 24 小时。过长的 wait_timeout 会让泄漏连接长期占位；过短又可能让连接池保留的空闲连接被数据库提前关闭。若云数据库或防火墙在 600 秒左右回收 TCP 连接，而 MySQL 设置为 28800 秒，就会出现客户端以为连接可用、实际链路已失效的问题。

实操步骤二：建立连接堆积监控视图 ​

治理不能只靠故障时手动执行命令。可以用如下 SQL 定期采集，建议每 30 秒或 1 分钟一次：

sql
-- 创建一个监控用数据库，生产中可按规范放入运维库
CREATE DATABASE IF NOT EXISTS ops_monitor;

USE ops_monitor;

-- 保存 Sleep 连接快照
CREATE TABLE IF NOT EXISTS mysql_sleep_connection_snapshot (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    snapshot_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_name VARCHAR(128) NOT NULL,
    client_ip VARCHAR(128) NOT NULL,
    db_name VARCHAR(128) DEFAULT NULL,
    sleep_count INT NOT NULL,
    max_idle_seconds INT NOT NULL,
    avg_idle_seconds DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_snapshot_time (snapshot_time),
    KEY idx_user_ip (user_name, client_ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 每次采集时写入聚合结果
INSERT INTO mysql_sleep_connection_snapshot
    (user_name, client_ip, db_name, sleep_count, max_idle_seconds, avg_idle_seconds)
SELECT
    user,
    SUBSTRING_INDEX(host, ':', 1) AS client_ip,
    db,
    COUNT(*) AS sleep_count,
    MAX(time) AS max_idle_seconds,
    AVG(time) AS avg_idle_seconds
FROM information_schema.processlist
WHERE command = 'Sleep'
GROUP BY user, client_ip, db;

再配合状态指标做告警：

sql
-- 查看最近 1 小时内最严重的 Sleep 来源
SELECT
    user_name,
    client_ip,
    db_name,
    MAX(sleep_count) AS peak_sleep_count,
    MAX(max_idle_seconds) AS peak_idle_seconds
FROM mysql_sleep_connection_snapshot
WHERE snapshot_time >= NOW() - INTERVAL 1 HOUR
GROUP BY user_name, client_ip, db_name
HAVING peak_sleep_count >= 20 OR peak_idle_seconds >= 600
ORDER BY peak_sleep_count DESC;

告警阈值不要只设连接总数，建议同时包含三类：连接数使用率超过 80%；单来源 Sleep 数异常增长；最长 Sleep 时间超过连接池预期空闲周期。

实操步骤三：连接池配置与 MySQL 超时联动 ​

常见 HikariCP 配置可以参考下面的组合。假设 MySQL wait_timeout=1800 秒，连接池应在数据库关闭前主动回收：

yaml
spring:
  datasource:
    hikari:
      # 最大连接数按实例容量和应用副本数反推，不是越大越好
      maximum-pool-size: 20
      minimum-idle: 5
      # 10 分钟空闲后回收，小于 MySQL 的 1800 秒
      idle-timeout: 600000
      # 连接最大生命周期 30 分钟，定期换新
      max-lifetime: 1800000
      # 借出连接时校验，适合网络设备会静默断链的环境
      connection-test-query: SELECT 1
      keepalive-time: 300000
      connection-timeout: 3000
      validation-timeout: 3000

核心关系是：idle-timeout 和 max-lifetime 应小于 wait_timeout；如果中间存在 600 秒断链的防火墙，保活间隔也要小于 600 秒。连接池总上限还要乘以应用实例数。例如 MySQL 设置 500 连接，不能让 10 个应用实例各配 100，因为理论峰值会达到 1000。

Druid 则重点关注这些参数：

properties
# 初始与活动连接数
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20

# 空闲连接检测与回收，单位毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.keep-alive=true

# 借出时校验，避免失效连接直接到达业务 SQL
spring.datasource.druid.test-on-borrow=true
spring.datasource.druid.validation-query=SELECT 1

避坑总结：三个高频错误及修正方法 ​

1. 只调大 max_connections，结果内存和线程压力上升 ​

常见现象是连接数告警消失不久后复发，数据库内存上涨，慢查询反而增多。连接数不是吞吐能力，事务竞争、锁等待和上下文切换会随并发增加而恶化。修正方法是先按来源统计 Sleep 数，再根据 Max_used_connections、应用副本数和压测结果计算连接池上限，而不是把 max_connections 当作唯一阀门。

2. wait_timeout 调小后没有配置连接池校验 ​

典型报错是 The last packet successfully received from the server was ... milliseconds ago，或业务偶发第一次请求失败、重试后恢复。原因是 MySQL 已关闭空闲连接，但连接池仍把旧连接借给应用。修正方法是让连接池空闲回收时间小于 wait_timeout，并开启 keepalive、借出校验或驱动级连接有效性检测。

3. 把连接池 maxActive 配得比数据库总容量还大 ​

多个微服务各自设置 max-active=100，低峰看似正常，发布或重试时同时建满连接，直接触发 Too many connections。修正方法是建立全局连接预算：预留管理连接后，将业务连接额度按服务重要性和实例数分配，并在监控中展示每个服务的连接占比。

4. Kill Sleep 会话却忽略未提交事务 ​

有些 Sleep 会话并不是无害空闲，可能处于事务中并持有行锁或元数据锁。盲目批量 Kill 会造成业务回滚，虽然比长时间锁等待更安全，但仍应先确认。可使用如下 SQL：

sql
-- 查看事务仍在运行的 Sleep 会话及其锁等待
SELECT
    p.id AS processlist_id,
    p.user,
    p.host,
    p.time AS sleep_seconds,
    trx.trx_started,
    trx.trx_state,
    trx.trx_rows_locked,
    trx.trx_query
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist p
  ON trx.trx_mysql_thread_id = p.id
WHERE p.command = 'Sleep'
ORDER BY trx.trx_started;

连接堆积治理的关键不是追求某个“最佳 wait_timeout”，而是让数据库、网络和连接池对空闲连接的认知一致。先用 SQL 找到来源，再用监控验证趋势，最后通过连接池预算、主动回收和有效性校验闭环，才能既避免 Sleep 连接占满实例，也避免超时调整引入新的偶发故障。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。