MySQL 連接超時變量詳解：interactive_timeout、wait_timeout 與連接池配置最佳實踐

MySQL 連接超時機制直接影響應用可用性與資源佔用。本文深度解析 interactive_timeout、wait_timeout、connect_timeout 差異、生效範圍、動態修改方法，並給出 Hibernate + C3P0 連接池配置黃金法則，防止「斷連重連」與「Too many connections」。

1. 核心超時變量對比

2. 關鍵機制細節

• 判斷依據：客戶端調用 mysql_real_connect() 時是否傳遞 CLIENT_INTERACTIVE 標誌。JDBC、ODBC、PHP mysqli 默認 不傳遞，屬非交互式，受 wait_timeout 控制。
• 動態修改：
  • SET GLOBAL wait_timeout = 300; 對新建連接生效，現有連接不受影響。
  • SET SESSION wait_timeout = 300; 對當前連接立即生效。
• 配置文件持久化：

  [mysqld]
  wait_timeout = 300
  interactive_timeout = 300

• 檢測當前會話值：SHOW VARIABLES LIKE '%timeout';

3. 生產環境常見問題與對策

問題 1：連接池拿到的連接已被服務端斷開 (Stale Connection)

現象：應用閒置一夜後首次請求報 Communications link failure、Connection reset。

根因：服務端 wait_timeout=8h 斷開空閒連接，連接池不知情仍借出失效連接。

解決 (三層防線)：

1. 縮短服務端 wait_timeout：設 300~600 秒 (5~10 分鐘)，配合連接池 idle_test_period < wait_timeout。
2. 連接池啟用驗證查詢：preferredTestQuery = "SELECT 1"，testConnectionOnCheckout = true (借出前驗證) 或 idleConnectionTestPeriod = 120 (定期掃描空閒連接)。
3. 應用層捕獲異常重試：DataSource 封裝重試邏輯 (如 Spring RetryTemplate)。

問題 2：Too many connections

根因：大量 Sleep 連接佔滿 max_connections，多因應用未正確關閉連接或連接池配置過大。

對策：

• 縮短 wait_timeout 加速回收。
• 監控 SHOW PROCESSLIST Sleep 連接數，設置告警。
• 調大 max_connections (需評估內存：每連接 ~256KB+)。
• 排查代碼連接洩漏 (未 close、未 return pool)。

4. Hibernate + C3P0 黃金配置範本

hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800          -- 連接在池中最大空閒秒數 (30分)，需 < wait_timeout
hibernate.c3p0.max_statements=50     -- Statement 緩存大小
hibernate.c3p0.idle_test_period=120  -- 每 120 秒檢查一次空閒連接 (需 < timeout)
hibernate.c3p0.acquire_increment=2   -- 擴容步長
hibernate.c3p0.validate=true         -- 拿連接時驗證 (SELECT 1)
hibernate.c3p0.preferredTestQuery=SELECT 1

關鍵不等式：

idle_test_period < timeout < wait_timeout (服務端)
建議數值: 120s < 1800s < 300s (服務端 wait_timeout=300)

5. 驗證配置生效

-- 服務端
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW PROCESSLIST;  -- 觀察 Sleep 連接存活時間

-- 客戶端 (C3P0 日誌開啟 DEBUG)
log4j.logger.com.mchange.v2.c3p0=DEBUG
-- 觀察 "Checking idle connections"、"Testing connection" 日誌