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_timeoutThe 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_timeoutThe 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_timeoutThe 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_timeoutThe 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=5MySQL [(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 3306Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N5.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 3306Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
N5.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_timeoutMySQL命令行客户端
1.3.1.1. session级别修改interactive_timeout
## 打开第一个会话设置session级别的interactive_timeout=2MySQL [(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=20MySQL [(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_timeoutMySQL命令行客户端
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 21Server 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=2MySQL [(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 awayNo connection. Trying to reconnect...Connection id: 22Current 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_timeoutsession interactive_timeout不生效
有一点要注意如果是新的连接即断开重连的或者新的连接session级别的wait_timeout会使用global级别的interactive_timeout值覆盖因为interactive_timeout值是对后续新连接生效(参考1.2.2小节验证过程)
1.3.2.2. global级别修改wait_timeout
# 打开第一个会话修改global wait_timeout=2MySQL [(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=utf8import MySQLdbimport sysimport 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 '=' * 100try:
current_connect()except Exception,e: print eelse:
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参数为1Smysql -uqbench -pqbench -h 10.10.30.68mysql > 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.sqlWarning: 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参数为默认的60Smysql -uqbench -pqbench -h 10.10.30.68mysql > 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.sqlWarning: 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 -lhtotal 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 15453Server 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.68Warning: 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 17040Server 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 upinnodb使用这个参数能够有效避免在资源有限的情况下产生太多的锁等待指的是事务等待获取资源时等待的最长时间超过这个时间还未分配到资源则会返回应用失败参数的时间单位是秒最小可设置为1s(一般不会设置得这么小)最大可设置1073741824秒34年一条语句锁等待超过30分钟估计业务该有反馈了默认安装时这个值是50s超过这个时间会报 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
## 第一个会话创建测试数据并设置innodb_lock_wait_timeout=1MySQL [(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 testDatabase 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 testDatabase 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 testReading 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的数据行为6MySQL [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 testDatabase 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 testReading 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的数据行为6MySQL [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 testDatabase 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 tableflush 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 log5.6.6之后刷新频率可以通过这个参数设置当然这个参数本身默认值也是1S。
转载于:https://blog.51cto.com/dreamy/1903972
扫一扫
1392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
