mysql几个timeout参数_MySQL中 timeout相关参数解析

前言：

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

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

参数介绍：

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 theCLIENT_INTERACTIVEoption tomysql_real_connect(). See alsowait_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

Permitinteractive_timeoutseconds (instead ofwait_timeoutseconds) of inactivity before closing the connection. The client's sessionwait_timeoutvariable is set to the value of the sessioninteractive_timeoutvariable.

简单的说 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, 0rows affected (0.00 sec)

mysql>set global wait_timeout = 22222;

Query OK, 0rows affected (0.00 sec)

mysql> showglobal variableslike '%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    |

+----------------------------+----------+

10rows in set (0.00 sec)

mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sock

Welcometo the MySQL monitor.  Commandsend with ;or \g.

Your MySQLconnection idis 4

Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracleand/or its affiliates.All rights reserved.

Oracleis a registered trademarkof Oracle Corporationand/or its

affiliates. Other names may be trademarksof their respective

owners.

Type'help;' or '\h' for help. Type'\c' to clear thecurrent input statement.

mysql> show session variableslike '%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    |

+----------------------------+----------+

10rows 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

Welcometo the MySQL monitor.  Commandsend with ;or \g.

Your MySQLconnection idis 6

Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracleand/or its affiliates.All rights reserved.

Oracleis a registered trademarkof Oracle Corporationand/or its

affiliates. Other names may be trademarksof their respective

owners.

Type'help;' or '\h' for help. Type'\c' to clear thecurrent input statement.

mysql> show session variableslike '%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    |

+----------------------------+----------+

10rows in set (0.00 sec)

2.2 起效关系

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

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

mysql>set session wait_timeout=10;

Query OK, 0rows affected (0.00 sec)

mysql>set session interactive_timeout=10;

Query OK, 0rows affected (0.00 sec)

mysql>select 1,sleep(20)from dual;

+---+-----------+

| 1 | sleep(20) |

+---+-----------+

| 1 |         0 |

+---+-----------+

1 rowin set (20.00 sec)

mysql>

mysql> show session variableslike '%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, 0rows affected (0.00 sec)

mysql>set session wait_timeout=20;

Query OK, 0rows affected (0.00 sec)

---------------------another connection-------------------------

mysql> showfull processlist;

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host            | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  1 | systemuser |                 |NULL |Connect | 103749 | Slave hasread all relay log; waitingfor the slave I/O threadto update it |NULL                  |         0 |             0 |         1 |

|  2 | systemuser |                 |NULL |Connect | 103750 | Connectingto master                                                        |NULL                  |         0 |             0 |         1 |

|  3 | root        | localhost       |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

| 10 | root        | localhost:58946 |NULL | Sleep   |     20 |                                                                             |NULL                  |         0 |             0 |        11 |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

4rows in set (0.00 sec)

mysql> showfull processlist;

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host      | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  1 | systemuser |           |NULL |Connect | 103749 | Slave hasread all relay log; waitingfor the slave I/O threadto update it |NULL                  |         0 |             0 |         1 |

|  2 | systemuser |           |NULL |Connect | 103750 | Connectingto master                                                        |NULL                  |         0 |             0 |         1 |

|  3 | root        | localhost |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

3rows in set (0.00 sec)

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

A：只有session timeout 会起作用。

测试1：

mysql>set session interactive_timeout = 10;

Query OK, 0rows affected (0.00 sec)

mysql>set session wait_timeout = 10;

Query OK, 0rows affected (0.00 sec)

mysql> show session variableslike '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| interactive_timeout        | 10       |

| wait_timeout               | 10       |

+----------------------------+----------+

10rows in set (0.00 sec)

mysql> showglobal variableslike '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| interactive_timeout        | 20       |

| wait_timeout               | 20       |

+----------------------------+----------+

10rows in set (0.00 sec)

mysql> showfull processlist;

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host            | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  3 | root        | localhost       |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

| 17 | root        | localhost:60585 |NULL | Sleep   |     10 |                                                                             |NULL                  |        10 |            10 |        11 |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

2rows in set (0.00 sec)

mysql> showfull processlist;

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host      | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  3 | root        | localhost |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

1rows in set (0.00 sec)

测试2：

mysql> show session variableslike '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| interactive_timeout        | 20       |

| wait_timeout               | 20       |

+----------------------------+----------+

10rows in set (0.00 sec)

mysql> showglobal variableslike '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| interactive_timeout        | 10       |
| wait_timeout               | 10       |

+----------------------------+----------+

10rows in set (0.00 sec)

mysql> showfull processlist;

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host            | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  3 | root        | localhost       |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

| 19 | root        | localhost:50276 |NULL | Sleep   |     19 |                                                                             |NULL                  |        10 |            10 |        11 |

+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

2rows in set (0.00 sec)

mysql> showfull processlist;

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

| Id |User        | Host      | db   | Command |Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

|  3 | root        | localhost |NULL | Query   |      0 |NULL                                                                        | showfull processlist |         0 |             0 |        11 |

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+

1rows 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 连接均有效果