MySQL中 timeout相关参数解析

 

本博客已经迁移至：http://cenalulu.github.io/

本篇博文已经迁移，阅读全文请点击：http://cenalulu.github.io/mysql/mysql-timeout/

 

 

前言：

MySQL中有两个关于连接超时的配置项。他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢？

本文将会通过一些测试实例来证明总结两者的相互关系。

 

参数介绍：

 interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See alsowait_timeout.

 

wait_timeout 

The number of seconds the server waits for activity on a noninteractive connection before closing it. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVEconnect option to mysql_real_connect()). See also interactive_timeout.

 

CLIENT_INTERACTIVE

Permit interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client's sessionwait_timeout variable is set to the value of the session interactive_timeout variable.

简单的说 interactive就是交互式的终端，例如在shell里面直接执行mysql，出现 mysql> 后就是交互式的连接。而mysql -e 'select 1' 这样的直接返回结果的方式就是非交互式的连接。

 

第二部分  测试 

2.1 继承关系 

Q：通过Socket连接 timeout会从哪个global timeout继承

A：由下例可见，通过socket登录，timeout 继承于global.interactive_timeout;

?
mysql> set global interactive_timeout =  11111;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global wait_timeout = 22222;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 22222    |
+----------------------------+----------+
10 rows in set (0.00 sec)
 
mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.16-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 11111    |
+----------------------------+----------+
10 rows in set (0.00 sec)

　　

Q：通过TCP/IP client 连接， timeout会从哪个global timeout继承

A：由下例可见，通过TCP/IP client 连接后的wait_timeout 仍然继承于 global.interactive_timeout 

?
mysql -uroot -ppassword -h 127.0.0.1 --port 3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.16-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 11111    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 11111    |
+----------------------------+----------+
10 rows in set (0.00 sec)

　　

2.2 起效关系

Q：timeout值，对于正在运行用的语句是否起效？

A：由下例可见SQL正在执行状态的等待时间不计入timeout时间

?
mysql> set session wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set session interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select 1,sleep(20) from dual;
+---+-----------+
| 1 | sleep(20) |
+---+-----------+
| 1 |         0 |
+---+-----------+
1 row in set (20.00 sec)
 
mysql>
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 10       |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 10       |
+----------------------------+----------+

　　

Q：wait_timeout 和 interacitve_timeout 如何相互作用。

A：只有session.wait_timeout 会起效

?
mysql> set session interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set session wait_timeout=20;
Query OK, 0 rows affected (0.00 sec)
 
---------------------another connection-------------------------
mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  1 | system user |                 | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 |
|  2 | system user |                 | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 |
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 10 | root        | localhost:58946 | NULL | Sleep   |     20 |                                                                             | NULL                  |         0 |             0 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
 
mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  1 | system user |           | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 |
|  2 | system user |           | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 |
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
3 rows in set (0.00 sec)

　　

Q：global timeout和session timeout哪个起作用。

A：只有session timeout 会起作用。 

测试1：

?
mysql> set session interactive_timeout = 10;Query OK, 0 rows affected (0.00 sec)
 
mysql> set session wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 10       |
| wait_timeout               | 10       |
+----------------------------+----------+
10 rows in set (0.00 sec)
 
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 20       |
| wait_timeout               | 20       |
+----------------------------+----------+
10 rows in set (0.00 sec)
 
 
mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 17 | root        | localhost:60585 | NULL | Sleep   |     10 |                                                                             | NULL                  |        10 |            10 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
 
mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
1 rows in set (0.00 sec)

 测试2：

?
mysql> show session variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 20       |
| wait_timeout               | 20       |
+----------------------------+----------+
10 rows in set (0.00 sec)
 
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 10       |<br>| wait_timeout               | 10       |
+----------------------------+----------+
10 rows in set (0.00 sec)
 
 
 
mysql> show full processlist;
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
| 19 | root        | localhost:50276 | NULL | Sleep   |     19 |                                                                             | NULL                  |        10 |            10 |        11 |
+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
 
mysql> show full processlist;
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |
+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+
1 rows in set (0.00 sec)

　　

第三部分 总结

由以上的阶段测试可以获得以下结论。

1. 超时时间只对非活动状态的connection进行计算。

2. 超时时间指通过 session wait_timeout 起效。

3. 交互式连接的wait_timeout 继承于 global.interactive_timeout

    非交互式连接的wait_timeout 继承于 global.wait_timeout

4. 继承关系和超时对 TCP/IP 和 Socket 连接均有效果

 

 

 

 

 

 

 

 

 

 

posted @   cenalulu  阅读(11947)  评论(0)  编辑  收藏  举报
编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
点击右上角即可分享
微信分享提示
AI IDE Trae