MySQL 超时时间timeout介绍

1、timeout变量

打开mysql,用show variables like ‘%timeout%’命令查看。

 2、分析

下面从timeout里面找些比较常用的出来逐个分析下。

2.1 connect_timeout

connect_timeout指的是连接过程中握手的超时时间,在5.0.52以后默认为10秒,之前版本默认是5秒。官方文档是这样说的:

 mysql的基本原理应该是有个监听线程循环接收请求,当有请求来时,创建线程(或者从线程池中取)来处理这个请求。由于mysql连接采用TCP协议,那么之前势必是需要进行TCP三次握手的。TCP三次握手成功之后,客户端会进入阻塞,等待服务端的消息。服务端这个时候会创建一个线程(或者从线程池中取一个线程)来处理请求,主要验证部分包括host和用户名密码验证。host验证我们比较熟悉,因为在用grant命令授权用户的时候是有指定host的。用户名密码认证则是服务端先生成一个随机数发送给客户端,客户端用该随机数和密码进行多次sha1加密后发送给服务端验证。如果通过,整个连接握手过程完成。(具体握手过程后续找到资料再分析)

由此可见,整个连接握手可能会有各种可能出错。所以这个connect_timeout值就是指这个超时时间了。可以简单测试下,运行下面的telnet命令会发现客户端会在10秒后超时返回。

 在超时之前mysql中该连接状态如下:

2.2 interactive_timeout & wait_timeout

还是先看官方文档,从文档上来看wait_timeout和interactive_timeout都是指不活跃的连接超时时间,连接线程启动的时候wait_timeout会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行mysql -uroot -p命令登陆到mysql,wait_timeout就会被设置为interactive_timeout的值。如果我们在wait_timeout时间内没有进行任何操作,那么再次操作的时候就会提示超时,这是mysql client会重新连接。

 测试如下:

 重新进入mysql,这时候可以看到:

 可以看到wait_timeout被设置为了interactive_timeout的值,这样,我们3秒后再执行其他命令,会提示如下:

2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout

还是先祭出官方文档,从文档中看,这个值是针对innodb引擎的,是innodb中行锁的等待超时时间,默认为50秒。如果超时,则当前语句会回滚。如果设置了innodb_rollback_on_timeout,则会回滚整个事务,否则,只回滚事务等待行锁的这个语句。

 同样来测试下(先创建一个innodb引擎的表test,只有一列,列名为a):

 首先插入三条测试数据

 当前innodb_rollback_on_timeout=OFF,设置innodb_lock_wait_timeout=1,我们开启两个事务

 那么如果innodb_rollback_on_timeout=ON,同样事务2会超时,但是这个时候如果我们begin开启新的事务,那么会回滚请求锁超时的整个事务,而不是像前面那样只回滚了超时的那条语句。

 

2.4 lock_wait_timeout

文档中描述如下,简单说来lock_wait_timeout是元数据锁等待超时,任意锁元数据的语句都会用到这个超时参数,默认为一年。元数据锁可以参加mysql metadata lock,为了保证事务可串行化,不管是myisam还是innodb引擎的表,只要是开始一个事务,就会获取操作表的元数据锁,这时候如果另一个事务要对表的元数据进行修改,则会阻塞直到超时。

 测试例子:我们用一个myisam引擎的表myisam_test来测试。其中有一条记录(1,1),现在我们先开启一个事务,然后执行一个select语句。另外打开一个session,然后执行表的元数据操作,如删除表,会发现操作阻塞直到lock_wait_timeout秒后提示超时。

其中更改表结构的元数据操作指令有如下这些:

 当然,多说一句,对于myisam表的加锁以及并发插入等,这篇博客myisam表锁非常详细,有兴趣的可以看看。

 

2.5 net_read_timeout & net_write_timeout

文档中描述如下,就是说这两个参数在网络条件不好的情况下起作用。比如我在客户端用load data infile的方式导入很大的一个文件到数据库中,然后中途用iptables禁用掉mysql的3306端口,这个时候服务器端该连接状态是reading from net,在等待net_read_timeout后关闭该连接。同理,在程序里面查询一个很大的表时,在查询过程中同样禁用掉端口,制造网络不通的情况,这样该连接状态是writing to net,然后在net_write_timeout后关闭该连接。slave_net_timeout类似。

测试:我创建一个120M的数据文件data.txt。然后登陆到mysql。

 导入过程设置iptables禁用3306端口。

 可以看到连接状态为reading from net,然后经过net_read_timeout秒后关闭。

 

3、总结

经过几个实验可以发现,connect_timeout在握手认证阶段(authenticate)起作用,interactive_timeout 和wait_timeout在连接空闲阶段(sleep)起作用,而net_read_timeout和net_write_timeout则是在连接繁忙阶段(query)或者网络出现问题时起作用。

Leave a Reply

Your email address will not be published.