MySQL 各种超时参数的含义

MySQL 各种超时参数的含义

  • 今日在查看锁超时的设置时，看到show variables like '%timeout%';语句输出结果中的十几种超时参数时突然想整理一下，不知道大家有没有想过，这么多的timeout参数，到底有什么区别，都是做什么用的呢？
MySQL [(none)]> show variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |
+------------------------------+----------+
13 rows in set (0.00 sec)
  • PS：文档说明
  • 根据这些参数的global和session级别分别进行阐述
  • 基于MySQL 5.6.30编写
  • 加载了半同步复制插件，所以才能看到半同步相关的参数
  • 验证演示过程可能会打开两个MySQL会话进行验证，也可能只打开一个MySQL会话进行验证
  • 只针对大家平时容易高混淆的或者说不好理解的超时参数做步骤演示，容易理解的超时参数只做文字描述，不做步骤演示
  • 大部分参数基于MySQL命令行客户端做的演示，但wait_timeout和interactive_timeout这两个比较特殊，为了对比不同客户端的差异，还使用了python演示

1、连接、网络类超时

  • 共有如下几个：
  • connect_timeout：默认为10S
  • wait_timeout：默认是8小时，即28800秒
  • interactive_timeout：默认是8小时，即28800秒
  • net_read_timeout：默认是30S
  • net_write_timeout：默认是60S

1.1. 针对网络类超时参数，先简单梳理一下在MySQL建立连接、发送数据包的整个过程中，每一个阶段都用到了哪些超时参数

a)、connect_timeout：在获取连接阶段（authenticate）起作用

  • 获取MySQL连接是多次握手的结果，除了用户名和密码的匹配校验外，还有IP->HOST->DNS->IP验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过connect_timeout的连接请求将会被拒绝。
  • 官方描述：connect_timeout(The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds)

b)、interactive_timeout和wait_timeout：在连接空闲阶段（sleep）起作用

  • 即使没有网络问题，也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout（或interactive_timeout，取决于client_interactive标志）的客户端，MySQL会主动断开连接。

  • 官方描述：

  • wait_timeout：The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeoutvalue, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).

  • 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()

c)、net_read_timeout和net_write_timeout：则是在连接繁忙阶段（query）起作用。

  • 即使连接没有处于sleep状态，即客户端忙于计算或者存储数据，MySQL也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应（发送、接收、或者处理数据包太慢）。
  • 为了保证连接不被浪费在无尽的等待中，MySQL也会选择有条件（net_read_timeout和net_write_timeout）地主动断开连接。
  • 这个参数只对TCP/IP链接有效，只针对在Activity状态下的线程有效
  • 官方描述：
  • net_read_timeout：The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client,net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort
  • net_write_timeout：The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

d)、 handshake流程

  • 在TCP三次握手的基础之上，简历MySQL通讯协议的连接，这个连接建立过程受connect_timeout参数控制
    --------------------TCP established--------------------
    MySQL Server(10.10.20.96)------->Client(10.10.20.51)
    Client(10.10.20.51)------->MySQL Server(10.10.20.96)
    MySQL Server(10.10.20.96)------->Client(10.10.20.51)

--------------------established--------------------

  • 在MySQL通讯协议建立连接之后，此时客户端连接的超时受wait_timeout和interactive_timeout参数控制
    建立连接后无交互：MySQL server ---wait_timeout--- Client
    建立连接交互后：MySQL server ---interactive_timeout--- Client

  • 在如果客户端有数据包传输，那么这个数据包的传输超时由net_read_timeout和net_write_timeout参数控制
    -------------------client与server端有数据传输时-------------------
    client ----->MySQL Server(net_read_timeout)
    client <-----MySQL Server(net_write_timeout)

1.2. connect_timeout：该参数没有session级别，是一个global级别变量

## 使用mysql客户端打开一个会话，并设置全局 connect_timeout=5
MySQL [(none)]> set global connect_timeout=5;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 

## 由于mysql客户端不是很好模拟连接阶段（authenticate）的超时，所以使用telnet来发包给mysql，因为telnet的包并不遵循mysql的通讯协议
[root@localhost ~]# time telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N
5.6.30-logwA{k)'&)S9#A`?Z&O9pJ`mysql_native_passwordConnection closed by foreign host.

real	0m5.022s  #这里可以看到5S之后连接断开
user	0m0.000s
sys	0m0.010s

## 回到mysql客户端：修改全局 connect_timeout为10S
MySQL [(none)]> set global connect_timeout=10;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 

## 使用telnet再试一次
[root@localhost ~]# time telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N
5.6.30-loggZoA3{6:S\D}iu3;n:uafmysql_native_passwordConnection closed by foreign host.

real	0m10.012s
user	0m0.000s
sys	0m0.002s
  • 从上面的结果中可以看到，MySQL客户端与服务端的连接阶段（authenticate）的超时由参数connect_timeout控制。

1.3. interactive_tineout和wait_timeout参数

1.3.1. interactive_timeout：（MySQL命令行客户端）
1.3.1.1. session级别修改interactive_timeout
## 打开第一个会话，设置session级别的interactive_timeout=2
MySQL [(none)]> set session interactive_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 2        |  #session级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

## 打开第二个会话，执行show语句
MySQL [(none)]> show session variables like '%timeout%';show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)
  • 从上面的结果可以看到，设置session级别的interactive_timeout对wait_timeout的session和global级别都没有影响
1.3.1.2. global级别修改interactive_timeout
### 回到第一个会话中，设置global interactive_timeout=20
MySQL [(none)]> set global interactive_timeout=20;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 2        |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #global级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 第二个会话断开之后重连，再执行show语句
MySQL [(none)]> show session variables like '%timeout%';show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #session级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 20       |  #session级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 20       |  #global级别的interactive_timeout改变了
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)
  • 从上面的结果中可以看到：如果改变了global级别的interactive_timeout值，对当前连接不生效，对后续新连接的wait_timeout的session级别生效，global级别不生效，interactive_timeout的global级别和session级别都生效
1.3.2. wait_timeout：（MySQL命令行客户端）
1.3.2.1. session级别修改wait_timeout
  • 这里为了验证后续的值不产生混乱，先把interactive_timeout的值恢复为172800并重连连接(connect_timeout默认是10，此时已经是这个值了，不用再修改)，然后再修改wait_timeout
MySQL [(none)]> set global interactive_timeout=172800;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> Ctrl-C -- exit!
Aborted
[root@localhost ~]# mysql -uqogir_env -p'letsg0' -S /home/mysql/data/mysqldata1/sock/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> show session variables like '%timeout%';show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |
+------------------------------+----------+
13 rows in set (0.00 sec)
  • 现在，开始1.3.2.小节的验证

# 打开第一个会话，修改session级别wait_timeout=2
MySQL [(none)]> set session wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    22
Current database: *** NONE ***  #从这里可以看到，当前连接被断开并重连了

+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #重连之后的session级别参数， interactive_timeout 没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #重连之后的session级别参数，wait_timeout恢复了172800
+------------------------------+----------+
13 rows in set (0.01 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #重连之后的global级别参数， interactive_timeout 没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   | #重连之后的global级别参数，wait_timeout恢复了172800，即新的连接不受影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 打开第二个会话，第二个会话注意要重连
MySQL [(none)]> show session variables like '%timeout%';show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   | #global级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

# 对于超时断开的连接，错误日志中会报如下错误：
2016-11-07 19:08:24 3391 [Warning] Aborted connection 21 to db: 'unconnected' user: 'qogir_env' host: 'localhost' (Got timeout reading communication packets)
  • 从上面的结果中可以看到：
  • session级别的wait_timeout变量在连接初始化时，继承global的interactive_timeout参数值
  • session级别的wait_timeout对当前交互连接生效（即当前连接的超时使用的是session wait_timeout，session interactive_timeout不生效）
  • 有一点要注意，如果是新的连接（即断开重连的或者新的连接），session级别的wait_timeout会使用global级别的interactive_timeout值覆盖，因为interactive_timeout值是对后续新连接生效(参考1.2.2小节验证过程)
1.3.2.2. global级别修改wait_timeout

# 打开第一个会话，修改global wait_timeout=2
MySQL [(none)]> set global wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select sleep(3);show session variables like '%timeout%';show global variables like '%timeout%';
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 2        |  #global级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)

# 打开第二个会话，注意需要断开重连，再执行show语句
MySQL [(none)]> show session variables like '%timeout%';show global variables like '%timeout%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #session级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 172800   |  #session级别的wait_timeout没有影响，因为前面说过，这里新连接的session的wait_timeout会继承global interactive_timeout的值
+------------------------------+----------+
13 rows in set (0.00 sec)

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 10       |
| delayed_insert_timeout       | 300      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 120      |
| innodb_rollback_on_timeout   | ON       |
| interactive_timeout          | 172800   |  #global级别的interactive_timeout没有影响
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| rpl_semi_sync_master_timeout | 10000    |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 10       |
| wait_timeout                 | 2        |  #global级别的wait_timeout改变了
+------------------------------+----------+
13 rows in set (0.00 sec)
  • 从上面的结果中可以看到：global级别的wait_timeout变量在初始化时，继承global的wait_timeout参数值，默认8小时
1.3.3. interactive_timeout和wait_timeout参数（python MySQL driver）
  • 本小节演示的python代码如下：

#cat test_timeout.py 
#!/bin/env python
# coding=utf8

import MySQLdb
import sys
import time

# 设置wait_timeout的值
wait_timeout=5

# 设置interactive_timeout的侄
interactive_timeout=10

# MySQL帐号
mysql_user='qbench'

# MySQL密码
mysql_password='qbench'

# MySQL ip地址
mysql_ip='10.10.30.68'




rest_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
rest_cur = rest_conn.cursor()
rest_cur.execute("show variables like '%timeout%';")
datas = rest_cur.fetchall()
datas = dict(datas)
    
rest_wait_timeout = datas['wait_timeout']
rest_interactive_timeout = datas['interactive_timeout']

rest_cur.close()
rest_conn.close()


def new_connect(info,timeout):
    new_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    new_cur = new_conn.cursor()
    print '%s \n%s' % ('-' * 50,str(info))
    #sql = "select sleep(%s);" % int(timeout+1)
    #print "执行sleep sql语句：%s" % str(sql)
    new_cur.execute("show variables like '%timeout%';")
    new_datas = new_cur.fetchall()
    new_datas = dict(new_datas)
    
    print 'wait_timeout=%s' % new_datas['wait_timeout']
    print 'interactive_timeout=%s' % new_datas['interactive_timeout']

    print "sleep %s 秒之后再次执行sql---" % int(timeout)
    time.sleep(int(timeout))
    #new_cur.execute("%s" % str(sql))
    new_cur.execute("show variables like '%timeout%';")
    new_datas = new_cur.fetchall()
    new_datas = dict(new_datas)
    
    print 'wait_timeout=%s' % new_datas['wait_timeout']
    print 'interactive_timeout=%s' % new_datas['interactive_timeout']

    new_cur.close()
    new_conn.close()

def current_connect():
    curr_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    curr_cur = curr_conn.cursor()
    print "在第一个连接中修改global wait_timeout为：%s" % wait_timeout
    curr_cur.execute("set global wait_timeout=%s;" % wait_timeout)
    curr_cur.execute("show variables like '%timeout%';")
    curr_datas1 = curr_cur.fetchall()
    curr_datas1 = dict(curr_datas1)
    
    print "%s\n第一个连接保持不断开的session级别的超时信息：" % ('-' * 100) 
    print 'wait_timeout=%s' % curr_datas1['wait_timeout']
    print 'interactive_timeout=%s' % curr_datas1['interactive_timeout']

    new_connect(info='第一个连接修改global wait_timeout为：%s之后，登录新的连接的session级别的超时信息如下：' % wait_timeout,timeout=wait_timeout)

    restore()

    curr_cur.close()
    curr_cur = curr_conn.cursor()
    print "在第一个连接中修改global interactive_timeout为：%s" % interactive_timeout
    curr_cur.execute("set global interactive_timeout=%s;" % interactive_timeout)
    curr_cur.execute("show variables like '%timeout%';")
    curr_datas2 = curr_cur.fetchall()
    curr_datas2 = dict(curr_datas2)
    
    print "%s\n第一个连接保持不断开的session级别的超时信息：" % ('-' * 100) 
    print 'wait_timeout=%s' % curr_datas2['wait_timeout']
    print 'interactive_timeout=%s' % curr_datas2['interactive_timeout']

    new_connect(info='第一个连接修改global interactive_timeout为：%s之后，登录新的连接的session级别的超时信息如下：' % interactive_timeout,timeout=interactive_timeout)

    curr_cur.close()
    curr_conn.close()


def restore():
    print "开启新的连接执行恢复参数初始设置----------------------"
    rest_conn = MySQLdb.connect(user=mysql_user,passwd=mysql_password,host=mysql_ip)
    rest_cur = rest_conn.cursor()
    rest_cur.execute("set global wait_timeout=%s,interactive_timeout=%s;" % (rest_wait_timeout,rest_interactive_timeout))

    rest_cur.close()
    rest_conn.close()
   

print '=' * 100
try:
    current_connect()
except Exception,e:
    print e
else:
    restore()

print '=' * 100
  • 跑一下这个脚本，打印结果如下：

#python test_timeout.py 
====================================================================================================
在第一个连接中修改global wait_timeout为：5
----------------------------------------------------------------------------------------------------
第一个连接保持不断开的session级别的超时信息：
wait_timeout=5
interactive_timeout=172800
-------------------------------------------------- 
第一个连接修改global wait_timeout为：5之后，登录新的连接的session级别的超时信息如下：
wait_timeout=5
interactive_timeout=172800
sleep 5 秒之后再次执行sql---
(2013, 'Lost connection to MySQL server during query')
====================================================================================================

  • 从上面的结果中可以看到，第一个会话中修改global wait_timeout=5之后，新的连接上来，超过5秒没有发送新的数据包，连接就被断开。

  • 综合1.3小节演示结果来看

  • MySQL命令行客户端下：global级别的interactive_timeout修改对当前连接不生效，但能影响新的连接的globa interactive_timeout、session interactive_timeout、session wait_timeout数值

  • MySQL命令行客户端下：session级别的interactive_timeout的修改除了能使session interactive_timeout数值改变之外没有什么作用

  • MySQL命令行客户端下：global级别的wait_timeout的修改除了能使global wait_timeout数值改变之外没有什么作用

  • MySQL命令行客户端下：session级别的wait_timeout能改变session wait_timeout数值其对当前连接生效。

  • python MySQL driver：修改global wait_timeout对当前连接不生效，但能影响新的连接的global wait_timeout、session wait_timeout

  • python MySQL driver：修改session wait_timeout只对当前连接生效

  • python MySQL driver：修改global interactive_timeout对当前连接不生效，能影响新的连接的global interactive_timeout、session interactive_timeout

  • python MySQL driver：修改session interactive_timeout除了能使session interactive_timeout数值改变之外没有什么作用

  • PS：思考？

  • 为什么MySQL命令行客户端中新的连接的session wait_timeout不是使用的global wait_timeout的值，而是使用的interactive_timeout的值？但是，为什么python MySQL driver中，新的连接的session wait_timeout就是按照正常的逻辑使用的是global wait_timeout的值？这里先卖个关子，问题的答案得去源码中找，参考链接：http://dev.mysql.com/doc/refman/5.6/en/mysql-real-connect.html

1.4. net_write_timeout
  • mysql服务端向客户端写(发送)数据时，服务端等待客户端响应的超时时间，当服务端正在写数据到客户端时，net_write_timeout控制何时超时
  • 对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效。默认值是60S
  • 下面使用tc命令模拟网络延迟来进行演示

## 使用sysbench在MySQL server上造数一张500W行数据的表

## tc命令对MySQL客户端的网卡加延迟
tc qdisc add dev eth0 root netem delay 1s

## MySQL 客户端登录server，修改net_write_timeout参数为1S
mysql -uqbench -pqbench -h 10.10.30.68
mysql > set global net_write_timeout=1;
Query OK, 0 rows affected (0.00 sec)

## 在MySQL客户端使用mysqldump备份
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data] # time mysqldump -uqbench -pqbench -h 10.10.30.68 --single-transaction --master-data=2  sbtest  sbtest2 > sbtest2.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `sbtest2` at row: 85  #从这里可以看到，不到一分钟时间，连接就被断开了

real	0m54.049s
user	0m0.009s
sys	0m0.011s

## MySQL客户端登录server，修改net_write_timeout参数为默认的60S
mysql -uqbench -pqbench -h 10.10.30.68
mysql > set global net_write_timeout=60;
Query OK, 0 rows affected (0.00 sec)

## 在MySQL客户端使用mysqldump重试备份
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysqldump -uqbench -pqbench -h 10.10.30.68 --single-transaction --master-data=2  sbtest  sbtest2 > sbtest2.sql
Warning: Using a password on the command line interface can be insecure.

real	14m41.744s
user	0m18.662s
sys	0m7.886s

[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# ls -lh
total 963M
drwxr-xr-x 12 mysql mysql  137 Dec 30 15:04 mysqldata1
drwxr-xr-x  2 mysql mysql    6 Dec 30 15:04 recovery
-rw-r--r--  1 root  root  963M Dec 30 15:30 sbtest2.sql  #这里可以看到，消耗15分钟之后，备份成功，备份文件大小接近1G
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]#
1.5. net_read_timeout
  • mysql服务端从客户端读取（接收）数据时，服务端等待客户端响应的超时时间，当服务端正在从客户端读取数据时，net_read_timeout控制何时超时
  • 对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效。默认值是30S
  • 下面接着1.4小节进行演示，使用1.4小节中的备份结果导入数据库

## MySQL客户端登录server，先查看一下net_read_timeout参数的侄
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15453
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 variables like '%net_read_timeout%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 30    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> 

## 现在，把1.4小节备份出来的sbtest2.sql文件导入server中的sbtest库
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 
Warning: Using a password on the command line interface can be insecure.

real	37m17.831s  #导入成功，耗时38分钟左右
user	0m22.797s
sys	0m3.436s

## 现在，使用MySQL客户端登录server，修改net_read_timeout参数
[root@555f12f7-850d-4f42-867c-2d12890beb40 data]# mysql -uqbench -pqbench -h 10.10.30.68
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17040
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> set global net_read_timeout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> 

## 修改tc模拟规则，模拟丢包10%,损坏包20%，延迟2秒，包乱序20%
tc qdisc del dev eth0 root
tc qdisc add dev eth0 root netem corrupt 20% loss 10% delay 2s reorder 20%

## 使用备份文件再次尝试导入
time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 

## 很囧的一个事情发生了。此时反复查看server端的processlist，只发现客户端连接上来了，但是一直是sleep状态
mysql> show processlist;
+-------+--------+-------------------+--------+---------+------+-------+------------------+
| Id    | User   | Host              | db     | Command | Time | State | Info             |
+-------+--------+-------------------+--------+---------+------+-------+------------------+
| 17129 | qbench | 10.10.30.78:16167 | sbtest | Sleep   |  207 |       | NULL             |
| 17159 | qbench | 10.10.30.68:47148 | NULL   | Query   |    0 | init  | show processlist |
+-------+--------+-------------------+--------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> kill 17129;  ## 尝试kill掉这个连接
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+-------+--------+-------------------+------+---------+------+-------+------------------+
| Id    | User   | Host              | db   | Command | Time | State | Info             |
+-------+--------+-------------------+------+---------+------+-------+------------------+
| 17159 | qbench | 10.10.30.68:47148 | NULL | Query   |    0 | init  | show processlist |
+-------+--------+-------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> use sbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from sbtest2;  ## 然后再查询一下sbtest2表的数据，发现是空的
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

## 此时，查看客户端的导入数据的连接
[root@20bc83fd-1489-4b60-976b-d1823e7dc36e data]# time mysql -uqbench -pqbench -h 10.10.30.68 sbtest < sbtest2.sql 
ERROR 2006 (HY000) at line 47: MySQL server has gone away  ## 发现断开了，囧。。

real	5m42.419s
user	0m0.031s
sys	0m0.017s

  • 从上面的结果中可以看到：修改net_read_timeout=1，并在客户端导入数据到server的时候，并没有如预期的超时断开客户端连接。猜测可能是客户端导入数据到server端的时候，server端接收包超时之后没有发起kill掉客户端的操作，所以不手动执行一把kill的话，客户端一直在那里不动，而server端的连接线程也一直处于sleep状态

  • PS：

  • 1.4和1.5小节演示用数据库帐号权限：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, LOCK TABLES, PROCESS

  • 与net_read_timeout和net_write_timeout相关的还有一个参数，net_retry_count，官方描述如下：
    If a read or write on a communication port is interrupted, retry this many times before giving up.

2、锁类超时

2.1. innodb_lock_wait_timeout
  • 官方描述：
    The length of time in seconds an InnoDB transaction waits for a row lock before giving up
  • innodb使用这个参数能够有效避免在资源有限的情况下产生太多的锁等待；指的是事务等待获取资源时等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(一般不会设置得这么小)，最大可设置1073741824秒（34年，一条语句锁等待超过30分钟估计业务该有反馈了），默认安装时这个值是50s，超过这个时间会报 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

## 第一个会话，创建测试数据，并设置innodb_lock_wait_timeout=1
MySQL [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test]> create table test(id int);
Query OK, 0 rows affected (0.03 sec)

MySQL [test]> insert into test values(1);
Query OK, 1 row affected (0.01 sec)

MySQL [test]> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL [test]> set innodb_lock_wait_timeout=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> 

## 打开第二个会话，注意第二个会要重连，然后打开一个事务，使用select...for update不提交
MySQL [test]> use test
Database changed
MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from test where id=1 for update;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL [test]> 

## 此时 回到第一个会话中，执行相同的select..for update语句，等到1S之后会话超时终止
MySQL [test]> select * from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [test]>
  • 对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效
  • 从上面的结果中可以看到，把innodb_lock_wait_timeout设置为1S之后，对于同一行的操作，锁等待超过1S就被终止事务了
  • PS：
  • 测试结果是在RR隔离级别下基于innodb表的DML操作
2.2. innodb_rollback_on_timeout
  • 官方描述：
    In MySQL 5.6, InnoDB rolls back only the last statement on a transaction timeout by default. If --innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction
  • 默认情况下innodb_lock_wait_timeout 超时后只是超时的sql执行失败，整个事务并不回滚，也不做提交，如需要事务在超时的时候回滚，则需要设置innodb_rollback_on_timeout=ON，该参数默认为OFF

## 先测试一下innodb_rollback_on_timeout为默认值时的情况，打开第一个会话，显式开启一个事务，插入几行测试数据，不提交：
MySQL [test]> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+
2 rows in set (0.00 sec)

MySQL [test]> use test
Database changed
MySQL [test]> show tables;
Empty set (0.00 sec)

MySQL [test]> create table test(id int);
Query OK, 0 rows affected (0.05 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]>insert into test(id) values(1),(2),(3),(4);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

## 现在，打开第二个会话，显式开启一个事务，并插入数据5，不提交
MySQL [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MySQL [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> insert into test values(5);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from test;
+------+
| id   |
+------+
|    5 |
+------+
2 rows in set (0.00 sec)

## 再回到第一个会话中，更新id为5的数据行为6
MySQL [test]> update test set id=6 where id=5;  #因为第二个会话插入第=5这行数据时，对5及其以后的范围加了锁，也没有提交，所以这个这里的操作需要进行锁等待
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [test]> select * from test ;  #这里可以看到，超时之后，第一个会话最开始在显式事务中插入的几行数据并没有回滚
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)


##此时，你需要自行决定会话1中插入的数据是要提交，还是需要回滚，当然，如果断开连接，事务会自动回滚，为了方便后续的测试，先在两个会话中都做rollback操作

  • 从上面的结果中可以看到，默认情况下innodb_rollback_on_timeout为OFF，此时超时终止的会话中的事务DML修改的数据不会自动回滚。

  • 现在，把innodb_rollback_on_timeout参数在my.cnf中加入并改为ON，重启mysql，再次插入相同数据试试看


## 第一个会话中显示开启一个事务，插入几行数据，不提交
MySQL [test]> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+
2 rows in set (0.00 sec)

MySQL [test]> use test
Database changed

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]>insert into test(id) values(1),(2),(3),(4);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

## 现在，打开第二个会话，显式开启一个事务，并插入数据5，不提交
MySQL [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MySQL [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> insert into test values(5);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from test;
+------+
| id   |
+------+
|    5 |
+------+
2 rows in set (0.00 sec)

## 再回到第一个会话中，更新id为5的数据行为6
MySQL [test]> update test set id=6 where id=5;  #因为第二个会话插入第=5这行数据时，对5及其以后的范围加了锁，也没有提交，所以这个这里的操作需要进行锁等待
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [test]> select * from test ;  #这里可以看到，超时之后，第一个会话最开始在显式事务中插入的几行数据已经回滚
Empty set (0.00 sec)
  • 从上面的结果中可以看到，把参数innodb_rollback_on_timeout设置为ON之后（注意，这个变量是只读变量，需要添加到my.cnf中并重启mysql），如果一个事务发生锁等待超时，那么这个事务没有提交的数据都会被回滚掉。
2.3. lock_wait_timeout
  • 官方描述：
    This variable specifies the timeout in seconds for attempts to acquire metadata locks.
  • 这里不得不提一下2.1小节的innodb_lock_wait_timeout超时参数，相信有不少人是没有搞太清楚这两者的区别，从字面上来看，前者是innodb的dml操作的行级锁的等待时间 后面是获取MDL锁的等待时间，默认值是31536000秒=1年。那么，下面来演示一把吧

## 打开第一个会话，显示开启一个会话，执行select...for update语句，不提交事务
MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from test for update;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

## 现在，打开第二个会话，修改session lock_wait_timeout=5，并执行DDL语句
MySQL [test]> set lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> use test
Database changed

MySQL [test]> alter table test add column test varchar(100);  #DDL语句执行被阻塞，5秒之后超时终止
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [test]>
  • 从上面的结果中可以看到，DDL语句的超时时间是受lock_wait_timeout参数控制的
  • PS：注意，凡是需要获取MDL锁的操作都受到这个超时参数的影响，不单单是DDL语句，包含在表上的DML、DDL操作，以及视图、存储过程、存储函数、lock table，flush table with read lock语句等。但不适用于隐式访问系统表的语句，如：grant和revoke等

3、复制类超时

3.1. delayed_insert_timeout
  • 官方描述：
    How many seconds an INSERT DELAYED handler thread should wait for INSERT statements beforeterminating.
  • 为MyISAM INSERT DELAY设计的超时参数，表示INSERT DELAY handler线程在INSERT DELAY语句终止前等待这个INSERT语句的时间，注意是表示insert delay延迟插入的超时时间，不是insert语句。默认值是300S，从5.6.7开始被弃用（因为delayed insert功能被弃用）后续版本将移除。
3.2. rpl_semi_sync_master_timeout
  • 官方描述：
    A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).

This variable is available only if the master-side semisynchronous replication plugin is installed.

  • 为semi-sync复制时，主库在某次事务提交时，如果等待超过rpl_semi_sync_master_timeout多秒之后仍然没有接收到任何从库做回包响应，那么主库自动降级为异步复制模式，当主库探测到有备库恢复回包时，主库自动恢复到semi-sync复制模式。默认值为10000毫秒=10秒
3.3. rpl_stop_slave_timeout
  • 官方描述：
    In MySQL 5.6.13 and later, you can control the length of time (in seconds) that STOP SLAVE waits before timing out by setting this variable. This can be used to avoid deadlocks between STOP SLAVE and other slave SQL statements using different client connections to the slave. The maximum and default value of rpl_stop_slave_timeout is 31536000 seconds (1 year). The minimum is 2 seconds.
  • 5.6.13之后引入的参数，控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能，可以通过rpl_stop_slave_timeout参数控制stop slave 的执行时间。默认值是31536000秒=1年
3.4. slave_net_timeout
  • 官方描述：
    The number of seconds to wait for more data from a master/slave connection before aborting the read.
  • Slave判断主库是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就认为Master已经挂掉了，后续根据超时重连参数设置进行重连主库的操作。默认值：3600S

4、IO类超时

4.1. innodb_flush_log_at_timeout
  • 官方描述：
    Write and flush the logs every N seconds. innodb_flush_log_at_timeout was introduced in MySQL 5.6.6. It allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. Prior to MySQL 5.6.6, flushing frequency was once per second. The default setting for innodb_flush_log_at_timeout is also once per second.
  • 5.6.6引入，参数innodb_flush_log_at_trx_commit=1时，此超时参数不起作用，当innodb_flush_log_at_trx_commit=0/2时才起作用。5.6.6之后表示每innodb_flush_log_at_timeout秒一次的频率刷新redo log（在5.6.6之前是固定每秒一次刷新redo log，5.6.6之后刷新频率可以通过这个参数设置，当然，这个参数本身默认值也是1S）。
posted @ 2016-12-26 16:41  xiaoboluo768  阅读(49239)  评论(3)  编辑  收藏  举报