官方描述: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)
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()
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)
在MySQL通讯协议建立连接之后,此时客户端连接的超时受wait_timeout和interactive_timeout参数控制 建立连接后无交互:MySQL server ---wait_timeout--- Client 建立连接交互后:MySQL server ---interactive_timeout--- Client
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)
### 回到第一个会话中,设置global interactive_timeout=20MySQL [(none)]> set global interactive_timeout=20;
QueryOK, 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)
MySQL [(none)]> set global interactive_timeout=172800;
QueryOK, 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. YourMySQL connection id is 21 Server version: 5.6.30-log MySQLCommunityServer (GPL)
MySQL [(none)]> select sleep(3);show session variables like ‘%timeout%’;show global variables like ‘%timeout%’; ERROR2006 (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)
# 打开第一个会话,修改global wait_timeout=2MySQL [(none)]> set global wait_timeout=2;
QueryOK, 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)
## MySQL客户端登录server,先查看一下net_read_timeout参数的侄
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands endwith ; or \g.
Your MySQL connection id is15453
Server version: 5.6.30-log MySQL Community Server (GPL)
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 endwith ; or \g. Your MySQL connection id is17040 Server version: 5.6.30-log MySQL Community Server (GPL)
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 ERROR2006 (HY000) at line47: MySQL server has gone away ## 发现断开了,囧。。
与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_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
官方描述: 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
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 inset (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 inset (0.00 sec)
## 再回到第一个会话中,更新id为5的数据行为6 MySQL [test]> update testset 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 inset (0.00 sec)
## 第一个会话中显示开启一个事务,插入几行数据,不提交
MySQL [test]> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
| innodb_rollback_segments | 128 |
+----------------------------+-------+
2 rows inset (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 inset (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 inset (0.00 sec)
## 再回到第一个会话中,更新id为5的数据行为6 MySQL [test]> update testset 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)
## 现在,打开第二个会话,修改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.
官方描述: 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.
官方描述: 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.
官方描述: 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.
MySQL 各种超时参数的含义今日在查看锁超时的设置时,看到show variables like '%timeout%';语句输出结果中的十几种超时参数时突然想整理一下,不知道大家有没有想过,这么多的timeout参数,到底有什么区别,都是做什么用的呢?MySQL [(none)]> show variables like '%timeout%';+------------------------------+----------+| Variable_name ...