【MySQL】淺析interactive_timeout和wait_timeout

楊奇龍發表於2017-08-16
MySqlAI
一 前言
  這篇文章源於自己一個無知的提問，作為一個DBA 老鳥，實在汗顏 。如圖，修改wait_timeout引數之後 並沒有及時生效，於是乎去跑到技術支援群裡問了。。ps 應該去查g.cn 才對。。
 
  本文通過測試我們要弄清楚兩個問題
  a 繼承關係 wait_timeout在session和global級別分別繼承那個引數？
  b 生效引數 在會話中到底哪個引數決定了會話的存活時間？

二 引數介紹  
首先說明兩個關鍵詞 通過MySQL 客戶端連線db的是互動會話，通過jdbc等程式連線db的是非互動會話。
interactive_timeout: MySQL伺服器關閉互動式連線前等待的秒數。互動式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。引數預設值：28800秒（8小時）
wait_timeout:          MySQL伺服器關閉非互動連線之前等待的秒數。在會話啟動時，根據全域性wait_timeout值或全域性interactive_timeout值初始化會話wait_timeout值，取決於客戶端型別--由mysql_real_connect()的連線選項CLIENT_INTERACTIVE定義。引數預設值：28800秒（8小時）
2.1 繼承關係 
1) 單獨設定global級別的interactive_timeout  
  1. set global interactive_timeout = 300
  2. session1 [RO] 09:34:20 >set global interactive_timeout=300;
  3. Query OK, 0 rows affected (0.00 sec)
  4. session1 [RO] 09:39:15 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  5. +---------------------+----------------+
  6. | variable_name       | variable_value |
  7. +---------------------+----------------+
  8. | INTERACTIVE_TIMEOUT | 300            |
  9. | WAIT_TIMEOUT        | 28800          |
  10. +---------------------+----------------+
  11. 2 rows in set (0.00 sec)
  12. session1 [RO] 09:39:21 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  13. +---------------------+----------------+
  14. | variable_name       | variable_value |
  15. +---------------------+----------------+
  16. | INTERACTIVE_TIMEOUT | 300            |
  17. | WAIT_TIMEOUT        | 28800          |
  18. +---------------------+----------------+
  19. 2 rows in set (0.00 sec)
  20. 登陸另外一個會話
  21. session2 [RO] 09:39:35 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  22. +---------------------+----------------+
  23. | variable_name       | variable_value |
  24. +---------------------+----------------+
  25. | INTERACTIVE_TIMEOUT | 300            |
  26. | WAIT_TIMEOUT        | 28800          |
  27. +---------------------+----------------+
  28. 2 rows in set (0.00 sec)
  29. session2 [RO] 09:39:51 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  30. +---------------------+----------------+
  31. | variable_name       | variable_value |
  32. +---------------------+----------------+
  33. | INTERACTIVE_TIMEOUT | 300            |
  34. | WAIT_TIMEOUT        | 300            |
  35. +---------------------+----------------+
  36. 2 rows in set (0.00 sec)
分析
  在互動模式下，session和global級別的 interactive_timeout 繼承了 interactive_timeout global的值。而 wait_timeout 的值，session級別繼承了interactive_timeout。global級別的wait_timeout 則不受影響 。
2) 設定session級別的 interactive_timeout 
  1. session1 [RO] 09:44:07 >set session interactive_timeout=300;
  2. Query OK, 0 rows affected (0.00 sec)
  3. session1 [RO] 09:44:27 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  4. +---------------------+----------------+
  5. | variable_name       | variable_value |
  6. +---------------------+----------------+
  7. | INTERACTIVE_TIMEOUT | 28800          |
  8. | WAIT_TIMEOUT        | 28800          |
  9. +---------------------+----------------+
  10. 2 rows in set (0.00 sec)
  11. session1 [RO] 09:44:31 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  12. +---------------------+----------------+
  13. | variable_name       | variable_value |
  14. +---------------------+----------------+
  15. | INTERACTIVE_TIMEOUT | 300            |
  16. | WAIT_TIMEOUT        | 28800          |
  17. +---------------------+----------------+
  18. 2 rows in set (0.00 sec)
  19. 另外開啟一個會話
  20. session2 [RO] 09:44:41 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  21. +---------------------+----------------+
  22. | variable_name | variable_value |
  23. +---------------------+----------------+
  24. | INTERACTIVE_TIMEOUT | 28800 |
  25. | WAIT_TIMEOUT | 28800 |
  26. +---------------------+----------------+
  27. 2 rows in set (0.01 sec)
  28. session2 [RO] 09:44:44 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  29. +---------------------+----------------+
  30. | variable_name       | variable_value |
  31. +---------------------+----------------+
  32. | INTERACTIVE_TIMEOUT | 28800          |
  33. | WAIT_TIMEOUT        | 28800          |
  34. +---------------------+----------------+
  35. 2 rows in set (0.00 sec)
分析
  從上面的例子來看 wait_timeout 並不受session級別的interactive_timeout的值的影響。  
3) 同時設定兩者的值，且不同。
  1. session1 [RO] 09:46:42 >
  2. (none) [RO] 09:46:42 >set global interactive_timeout=300;
  3. Query OK, 0 rows affected (0.00 sec)
  4. session1 [RO] 09:46:55 >set global wait_timeout=360;
  5. Query OK, 0 rows affected (0.00 sec)
  6. 另開啟一個會話
  7. session2 [RO] 09:47:20 >select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  8. +---------------------+----------------+
  9. | variable_name       | variable_value |
  10. +---------------------+----------------+
  11. | INTERACTIVE_TIMEOUT | 300            |
  12. | WAIT_TIMEOUT        | 300            |
  13. +---------------------+----------------+
  14. 2 rows in set (0.00 sec)
  15. session2 [RO] 09:47:22 >select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  16. +---------------------+----------------+
  17. | variable_name       | variable_value |
  18. +---------------------+----------------+
  19. | INTERACTIVE_TIMEOUT | 300            |
  20. | WAIT_TIMEOUT        | 360            |
  21. +---------------------+----------------+
  22. 2 rows in set (0.00 sec)
分析
 從案例1 2中可以得出session級別的wait_timeout 繼承global 級別的 interactive_timeout 的值 global級別的session則不受影響。在沒有改變 interactive_timeout的值的情況下，去修改wait_timeout的值
 結果無效。就會出現前言中我遇到的情況。
2.2 有效引數
  通過一個例子檢測影響會話的引數是哪個？驗證方式通過設定全域性的timeout時間(注意兩者時間不同)，另外起一個會話
  1. session1 [RO] 10:20:56 >set global interactive_timeout=20;
  2. Query OK, 0 rows affected (0.00 sec)
  3. session1 [RO] 10:23:32 >set global wait_timeout=10;
  4. Query OK, 0 rows affected (0.00 sec)
會話2進行查詢
  1. mysql> select sleep(5);
  2. +----------+
  3. | sleep(5) |
  4. +----------+
  5. | 0        |
  6. +----------+
  7. 1 row in set (5.01 sec)
然後在session1 中 檢視show processlist;

分析
    互動式 timeout時間受global級別的interactive_timeout影響。
2)非互動模式
  目前的測試並沒有達到預期，測試模型如下設定
  1. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  2.   +---------------------+----------------+
  3.   | variable_name       | variable_value |
  4.   +---------------------+----------------+
  5.   | INTERACTIVE_TIMEOUT | 35             |
  6.   | WAIT_TIMEOUT        | 35             |
  7.   +---------------------+----------------+
  8.   2 rows in set (0.00 sec)
  9.   mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  10.   +---------------------+----------------+
  11.   | variable_name       | variable_value |
  12.   +---------------------+----------------+
  13.   | INTERACTIVE_TIMEOUT | 35             |
  14.   | WAIT_TIMEOUT        | 25             |
  15.   +---------------------+----------------+
  16.   2 rows in set (0.00 sec)
在python命令列中模擬非互動資料來訪問資料庫，檢視資料庫timeout引數。同時在資料庫中執行show processlist 檢視python的連線多久會被關閉。

檢視session級別的引數

檢視show processlist，通過python程式連線資料庫的會話等待了25s之後，被中斷。

分析
1 通過python 命令列獲取的timeout 引數和互動方式獲取的並不一致，在互動命令列中獲取session級別的wait_timeout 的值為35，使用非互動命令獲取的值為25，說明wait_timeout繼承全域性的wait_timeout。
2 互動模式下會話空閒時間超過wait_timeout立即會被斷開。

3) 思考題
  session1 通過非互動命令連線到db，此時全域性的wait_timeout的值是28800，session 2 修改全域性的wait_timeout 為30s ，問題 session1的會話會受到影響嗎？

三 總結
 1 timeout 只是針對空閒會話有影響。
 2 session級別的wait_timeout繼承global級別的interactive_timeout的值。而global級別的session則不受interactive_timeout的影響。
 3 互動式會話的timeout時間受global級別的interactive_timeout影響。因此要修改非互動模式下的timeout，必須同時修改interactive_timeout的值。
 4 非互動模式下，wait_timeout引數繼承global級別的wait_timeout。

四 參考資料 
注意 本文測試的和參考資料並不完全相符，需要各位讀者親自測試，得到自己的結論。
[1]MySQL timeout相關引數解析和測試 
[2]MySQL中interactive_timeout和wait_timeout的區別 
[3]官方文件

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2143671/，如需轉載，請註明出處，否則將追究法律責任。

相關文章