首页 > 未分类 > MySQL中 timeout相关参数解析

MySQL中 timeout相关参数解析

2015年5月7日

MySQL中 timeout相关参数解析

http://www.cnblogs.com/cenalulu/archive/2012/06/20/2554863.html

本博客已经迁移至: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 <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)

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 |<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)
第三部分 总结
由以上的阶段测试可以获得以下结论。

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

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

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

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

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

分类: 未分类 标签:
本文的评论功能被关闭了.