MySQL中如何淺析interactive_timeout和wait_timeout

本篇文章給大家分享的是有關MySQL中如何淺析interactive_timeout和wait_timeout，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

一 前言
  這篇文章源于自己一個無知的提問，作為一個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。

以上就是MySQL中如何淺析interactive_timeout和wait_timeout，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。