MySQL中 timeout相關(guān)參數(shù)解析

HiLinz 2014-12-24

展開全文

前言：

MySQL中有兩個關(guān)于連接超時的配置項。他們之間在某些條件下會互相繼承，那究竟這兩個參數(shù)會在什么情況下起作用呢？

本文將會通過一些測試實例來證明總結(jié)兩者的相互關(guān)系。

參數(shù)介紹：

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里面直接執(zhí)行mysql，出現(xiàn) mysql> 后就是交互式的連接。而mysql -e 'select 1' 這樣的直接返回結(jié)果的方式就是非交互式的連接。

第二部分測試

2.1 繼承關(guān)系

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 <span style="color: #000000;">-S /usr/local/mysql3310/mysql.sock</span>

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)

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)

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 起效關(guān)系

Q：timeout值，對于正在運行用的語句是否起效？

A：由下例可見SQL正在執(zhí)行狀態(tài)的等待時間不計入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 |<span style="color: #000000;"> NULL

| Sleep   |     10 |   </span>                                                                          |

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)