简介

今日在查看锁超时的设置时，看到show variables like ‘%timeout%’;语句输出结果中的十几种超时参数时突然想整理一下，不知道大家有没有想过，这么多的timeout参数，到底有什么区别，都是做什么用的呢？

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


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)

根据这些参数的global和session级别分别进行阐述

基于MySQL 5.6.30编写

加载了半同步复制插件，所以才能看到半同步相关的参数

验证演示过程可能会打开两个MySQL会话进行验证，也可能只打开一个MySQL会话进行验证

只针对大家平时容易高混淆的或者说不好理解的超时参数做步骤演示，容易理解的超时参数只做文字描述，不做步骤演示

大部分参数基于MySQL命令行客户端做的演示，但wait_timeout和interactive_timeout这两个比较特殊，为了对比不同客户端的差异，还使用了python演示

连接、网络类超时

共有如下几个：

connect_timeout：默认为10S
wait_timeout：默认是8小时，即28800秒
interactive_timeout：默认是8小时，即28800秒
net_read_timeout：默认是30S
net_write_timeout：默认是60S

handshake流程

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

在TCP三次握手的基础之上，建立MySQL通讯协议的连接，这个连接建立过程受connect_timeout参数控制

1
2
3
4
5


--------------------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参数控制

1
2


建立连接后无交互：MySQL server ---wait_timeout--- Client
建立连接交互后：MySQL server ---interactive_timeout--- Client

在如果客户端有数据包传输，那么这个数据包的传输超时由net_read_timeout和net_write_timeout参数控制

1
2
3


-------------------client与server端有数据传输时-------------------
client ----->MySQL Server(net_read_timeout)
client <-----MySQL Server(net_write_timeout)

参数用途

connect_timeout

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)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36


## 使用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)'&)S9#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控制。

interactive_timeout和wait_timeout

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

根据上述定义，两者的区别显而易见

interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。
在连接启动的时候，根据连接的类型，来确认会话变量wait_timeout的值是继承于全局变量wait_timeout，还是interactive_timeout。

下面来测试一下，确认如下问题:

控制连接最大空闲时长的是哪个参数。
会话变量wait_timeout的继承问题

Q1：控制连接最大空闲时长的是哪个参数

A1：wait_timeout

验证

只修改wait_timeout参数:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.03 sec)

mysql> set session WAIT_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)
-------等待10s后再执行
mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
ERROR 2013 (HY000): Lost connection to MySQL server during query

可以看到，等待10s后再执行操作，连接已经断开。

只修改interactive_timeout参数:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.06 sec)

mysql> set session INTERACTIVE_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)
----------等待10s后执行
mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10             |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.06 sec)

Q2：会话变量wait_timeout的继承问题

A2：如果是交互式连接，则继承全局变量interactive_timeout的值，如果是非交互式连接，则继承全局变量wait_timeout的值。

验证：

只修改全局变量interactive_timeout的值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout'); 
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.13 sec)

mysql> set global INTERACTIVE_TIMEOUT=10;
Query OK, 0 rows affected (0.00 sec)

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10             |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.00 sec)

开启另外一个mysql客户端，查看会话变量的值

1
2
3
4
5
6
7
8


mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10             |
| WAIT_TIMEOUT        | 10             |
+---------------------+----------------+
2 rows in set (0.00 sec)

发现，WAIT_TIMEOUT的值已经变为10了。

但通过jdbc测试，wait_timeout的值依旧是28800

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


public class Jdbc_test {
    @SuppressWarnings("static-access")
    public static void main(String[] args) throws Exception {
         Connection conn = null;
         Statement stmt = null;
         ResultSet rs = null;
         String url = "jdbc:mysql://192.168.244.10:3306/test";
         String user = "root";
         String password = "123456";
         Class.forName("com.mysql.jdbc.Driver");
         conn = DriverManager.getConnection(url, user, password);
         stmt = conn.createStatement();
         String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
         rs = stmt.executeQuery(sql);
         while (rs.next()) {
             System.out
                     .println(rs.getString(1)+":  "+rs.getString(2));
         }
    }
}

结果输出如下：

1
2


INTERACTIVE_TIMEOUT:  10
WAIT_TIMEOUT:  28800

只修改全局变量wait_timeout的值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.17 sec)

mysql> set global WAIT_TIMEOUT=20;
Query OK, 0 rows affected (0.07 sec)

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 20             |
+---------------------+----------------+
2 rows in set (0.00 sec)

开启另外一个mysql客户端，查看会话变量的值

1
2
3
4
5
6
7
8


mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name       | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800          |
| WAIT_TIMEOUT        | 28800          |
+---------------------+----------------+
2 rows in set (0.03 sec)

WAIT_TIMEOUT的值依旧是28800.

查看jdbc的结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


public class Jdbc_test {
    @SuppressWarnings("static-access")
    public static void main(String[] args) throws Exception {
         Connection conn = null;
         Statement stmt = null;
         ResultSet rs = null;
         String url = "jdbc:mysql://192.168.244.10:3306/test";
         String user = "root";
         String password = "123456";
         Class.forName("com.mysql.jdbc.Driver");
         conn = DriverManager.getConnection(url, user, password);
         stmt = conn.createStatement();
         String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
         rs = stmt.executeQuery(sql);
         while (rs.next()) {
             System.out
                     .println(rs.getString(1)+":  "+rs.getString(2));
         }
         Thread.currentThread().sleep(21000);
         sql = "select 1 from dual";
         rs = stmt.executeQuery(sql);
         while (rs.next()) {
             System.out
                     .println(rs.getInt(1));
         }

    }
}

查看jdbc的结果

1
2


INTERACTIVE_TIMEOUT:  28800
WAIT_TIMEOUT:  20

同时，新增了一段程序，等待20s后，再次执行查询，报如下错误：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 12 ms ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
    at com.victor_01.Jdbc_test.main(Jdbc_test.java:29)
Caused by: java.net.SocketException: Software caused connection abort: recv failed
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
    ... 8 more

总结

控制连接最大空闲时长的wait_timeout参数。
对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。

对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。
判断一个连接的空闲时间，可通过show processlist输出中Sleep状态的时间

1
2
3
4
5
6
7
8


mysql> show processlist;
+----+------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host                 | db   | Command | Time | State | Info             |
+----+------+----------------------+------+---------+------+-------+------------------+
|  2 | root | localhost            | NULL | Query   |    0 | init  | show processlist |
|  6 | repl | 192.168.244.20:44641 | NULL | Sleep   | 1154 |       | NULL             |
+----+------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.03 sec)

net_write_timeout

mysql服务端向客户端写(发送)数据时，服务端等待客户端响应的超时时间，当服务端正在写数据到客户端时，net_write_timeout控制何时超时对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效。默认值是60S

下面使用tc命令模拟网络延迟来进行演示

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


## 使用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]# 

net_read_timeout

mysql服务端从客户端读取（接收）数据时，服务端等待客户端响应的超时时间，当服务端正在从客户端读取数据时，net_read_timeout控制何时超时

对于这个参数，session和global级别并没有什么特别，session级别只对当前连接生效，global级别只对新的连接生效。默认值是30S

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100


## 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状态

锁类超时

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


## 第一个会话，创建测试数据，并设置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操作

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69


## 先测试一下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，再次插入相同数据试试看

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53


## 第一个会话中显示开启一个事务，插入几行数据，不提交
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），如果一个事务发生锁等待超时，那么这个事务没有提交的数据都会被回滚掉。

lock_wait_timeout

官方描述：

This variable specifies the timeout in seconds for attempts to acquire metadata locks.

这里不得不提一下innodb_lock_wait_timeout超时参数，相信有不少人是没有搞太清楚这两者的区别，从字面上来看，前者是innodb的dml操作(建表操作)的行级锁的等待时间后面是获取MDL锁的等待时间，默认值是31536000秒=1年。那么，下面来演示一把吧

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


## 打开第一个会话，显示开启一个会话，执行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等

复制类超时

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功能被弃用）后续版本将移除。

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秒

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年

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

IO类超时

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）。

参考:
https://www.cnblogs.com/ivictor/p/5979731.html https://www.cnblogs.com/xiaoboluo768/p/6222862.html

MySQL各种超时参数的含义

文章目录

简介

连接、网络类超时

handshake流程

参数用途

connect_timeout

interactive_timeout和wait_timeout

net_write_timeout

net_read_timeout

锁类超时

innodb_lock_wait_timeout

innodb_rollback_on_timeout

lock_wait_timeout

复制类超时

delayed_insert_timeout

rpl_semi_sync_master_timeout

rpl_stop_slave_timeout

slave_net_timeout

IO类超时

innodb_flush_log_at_timeout