MySQL timeout相关参数解析和测试

标签： mysql timeout 相关 | 发表时间：2015-01-20 08:00 | 作者：cenalulu(卢钧轶)
出处：http://cenalulu.github.io/

MySQL中有两个关于连接超时的配置项: wait_timeout和 interactive_timeout。他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢？ 本文将会通过一些测试实例来证明总结两者的相互关系。

参数介绍

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_INTERACTIVE connect 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 session wait_timeout variable is set to the value of the session interactive_timeout variable.

简单的说 interactive就是交互式的终端，例如在shell里面直接执行mysql，出现形如 mysql>的提示符后就是交互式的连接。而mysql -e ‘select 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)

起效关系

Q：timeout值，对于处于运行状态SQL语句是否起效（即是否等价于执行超时）？
A：由下例可见SQL正在执行状态的等待时间不计入timeout时间。即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：同一个session中， wait_timeout 和 interacitve_timeout是否都会生效。
A：只有 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)

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 会起作用。即一个session开始后，无论如何修改global级别的timeout都不会影响该session

  • 测试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       |
| 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 为超时依据，global级别只决定session初始化时的超时默认值。
  3. 交互式连接的 wait_timeout 继承于global的 interactive_timeout。非交互式连接的 wait_timeout继承于global的 wait_timeout
  4. 继承关系和超时对 TCP/IP 和 Socket 连接均有效果

相关 [mysql timeout 相关] 推荐：

MySQL timeout相关参数解析和测试

- - cenalulu's Tech Blog
MySQL中有两个关于连接超时的配置项:. wait_timeout和 interactive_timeout. 他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢. 本文将会通过一些测试实例来证明总结两者的相互关系. 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.

为线程执行设置timeout

- - ITeye博客
long timeout = 1000;// 任务必须在设定时间内完成，否则任务将被强制关闭. long timeNeed = 2000;// 任务完成需要的时长. TimeUnit timeUnit = TimeUnit.MILLISECONDS;// 时间单位. ExecutorService executor = Executors.newSingleThreadExecutor();// 高级并发API.

Dubbo之connet timeout问题排查

- - 开源软件 - ITeye博客
    购物车依赖推荐的dubbo接口，推荐服务每天凌晨3点会批量下线推荐dubbo服务，全量更新商品，更新完以后在执行上线操作，每天凌晨3点10分左右，购物车工程都会出现5000左右的connection timeout error. 正常依赖的dubbo服务工程在启动的时候，消费端会经常出现connection timeout error.

MySQL Temporary Table相关问题的探究

- comain - 淘宝核心系统团队博客
让我们先来观察几条非常简单的MySQL语句：. 这是丁奇提出的引导性的问题，几条语句看似简单，不过接下来我们提出的一连串问题与进. 看到以上语句，你很容易会产生类似于以下的疑问：. 上述语句在一个session中先后创建了两个名为’tmp’的table，只不过一个是temporary. table，一个是normal table.

c_socket.io_server笔记之长轮询超时(timeout)处理

- - BlogJava-首页技术区
当你习惯了现有WEB服务器，诸如nginx、apache，JAVA应用服务器Tomcat等，你就不能不注意HTTP请求的响应超时时间，需要小心，尤其是反向代理时. 当你可以自由控制请求timeout超时时，那是怎样一个快意. 在libev中使用timeout，没有像java那样封装的完善，一切都很原始，但确实锋利多了.

apache ab 性能测试 timeout 解决方案

- - 互联网 - ITeye博客
  源：http://blog.csdn.net/garn_hsia/article/details/12997477. 使用ab或者webbench做压力测试，如果并发数开到1000的时候，无法完成测试. 到晚上查看资料发现是linux网络参数设置. 在kernel2.6之前的添加项：. kernel2.6之后的添加项：.

spring-cloud服务网关中的Timeout设置

- - ImportNew
大家在初次使用spring-cloud的gateway的时候，肯定会被里面各种的Timeout搞得晕头转向. hytrix有设置，ribbon也有. 我们一开始也是乱设一桶，Github上各种项目里也没几个设置正确的. 对Timeout的研究源于一次log中的warning. The Hystrix timeout of 60000 ms for the command “foo” is set lower than the combination of the Ribbon read and connect timeout, 200000ms..

MySql性能相关的一些概念(性能tip0）

- - BlogJava-首页技术区
题首：这是最近读《高性能MySqL 第二版》记录下来的东西~. #读锁（共享锁）、写锁（排他锁）：读锁是共享的，互不阻塞，读取同一资源互不影响；写锁排他，一个写锁会阻塞其他的读写操作. #锁定对象的粒度：表锁和行锁. 表锁：整个表加锁，当写操作时，加写锁，资源访问排他. 当没有写时，加读锁，读锁互不冲突.

Linux Ksplice，MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统，为 Kernel 打补丁无需重启动，做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户，超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

MySQL Replication 线程

- - CSDN博客推荐文章
Replication 线程. Mysql 的Replication 是一个异步的复制过程，从一个Mysql instace（我们称之为Master）复制到另一个Mysql instance（我们称之Slave）. 在Master 与Slave 之间的实现整个复制过程主. 要由三个线程来完成，其中两个线程（Sql 线程和IO 线程）在Slave 端，另外一个线程（IO 线程）在Master 端.