mysql python 查询超时_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

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.

real0m5.022s #这里可以看到5S之后连接断开

user0m0.000s

sys0m0.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.

real0m10.012s

user0m0.000s

sys0m0.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%&