現象描述

執行 SHOW PROCESSLIST; 發現大量 Command=Sleep 進程，佔用連接數導致新請求報 Too many connections 或效能下降。Sleep 狀態表示連接已建立但無正在執行的查詢，等待客戶端發送下一條指令或超時斷開。

核心成因三大類

類別	典型原因	判斷線索
應用層連接管理缺失	未顯式關閉連接、連接池洩漏、長連接未歸還	Sleep 時間接近 wait_timeout；連接數與 QPS 不成比例
連接池配置不當	最大連接數過大、閒置回收機制失效、minIdle 設過高	連接池監控顯示大量 Idle 連接長期不釋放
慢查詢堵塞釋放	單查詢耗時超長，導致連接長期佔用	慢查詢日誌顯示高耗時 SQL；Sleep 前有長時間 Query 狀態

排查實戰步驟

1. 確認 wait_timeout 當前值：SHOW VARIABLES LIKE 'wait_timeout'; 預設 28800 秒（8 小時），過長會疊加 Sleep。
2. 關聯應用日誌：對比 Sleep 進程 Id 與應用存取日誌，定位具體業務入口。
3. 檢查連接池指標：HikariCP/Druid/Tomcat JDBC Pool 監控 active/idle/usage 曲線。
4. 開啟 General Log 短期抓包：SET GLOBAL general_log=ON; 觀察 Sleep 前最後一條 SQL。

分層治理方案

層級 1：應用代碼修正（根治）

• 使用 try-with-resources / using / defer 確保連接必關閉。
• 禁用舊式 mysql_pconnect（PHP）、Class.forName 手動管理連接。
• ORM 框架檢查：Hibernate hibernate.connection.release_mode、MyBatis SqlSession 作用域。

層級 2：連接池參數優化

# HikariCP 範例（application.yml）
spring:
  datasource:
    hikari:
      maximum-pool-size: 20          # 業務高峰 QPS * 平均響應時間(秒) * 1.5
      minimum-idle: 5                # 避免頻繁創建銷毀
      idle-timeout: 300000           # 5 分鐘閒置回收
      max-lifetime: 1800000          # 30 分鐘強制換新，防駐留連接
      connection-timeout: 30000      # 獲取連接超時
      validation-timeout: 5000
      leak-detection-threshold: 60000 # 洩漏偵測 60 秒告警

層級 3：MySQL 服務端參數調整

[mysqld]
# 縮短空閒斷開，配合應用層心跳
wait_timeout = 300          # 5 分鐘
interactive_timeout = 300

# 防止連接爆滿拒服務
max_connections = 2000      # 機器記憶體/線程棧大小評估
max_user_connections = 500  # 單用戶上限，防單租戶佔滿

# 效能優化
thread_cache_size = 100     # 緩存線程減少創建開銷

動態生效（無需重啟）：

SET GLOBAL wait_timeout = 300;
SET GLOBAL max_connections = 2000;

層級 4：兜底清理腳本（治標）

僅作緊急臨時手段，不可長期依賴。

<?php
// kill_long_sleep.php
$pdo = new PDO('mysql:host=localhost', 'root', 'pwd');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$maxSleep = 120; // 秒
$stmt = $pdo->query("SHOW PROCESSLIST");
while ($proc = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($proc['Command'] === 'Sleep' && $proc['Time'] > $maxSleep) {
        $pdo->exec("KILL {$proc['Id']}");
        error_log("Killed sleep process {$proc['Id']} (Time: {$proc['Time']}s)");
    }
}
?>

Crontab 每 2 分鐘執行：*/2 * * * * /usr/bin/php /opt/scripts/kill_long_sleep.php >/dev/null 2>&1

關於 wait_timeout 設為 10 秒的風險提示

• 批次作業/定時任務若 SQL 間隔 > 10 秒會斷連，報 MySQL server has gone away。
• 解決：應用層定期 SELECT 1 心跳、或調大批次任務專用連接的 wait_timeout（SET SESSION wait_timeout=3600）。

總結

1. Sleep 暴增本質是應用層連接釋放不及時，參數調優只能緩解。
2. 優先修正代碼與連接池配置，建立連接洩漏監控告警。
3. 服務端 wait_timeout 建議 300-600 秒，配合應用層心跳機制。
4. 拒絕在 Cron 長期跑 Kill 腳本，治標不治本且有誤殺風險。