澳门金沙vipmysql连接超时的参数设置

mysqltimeout知多少

mysql连接超时的参数设置

最近系统因为数据库连接超时的问题,出现了几次故障。排查了下my.cnf的配置问题,最后是代码设计有问题。正好就把所有的timeout参数都理一遍,首先数据库里查一下看有哪些超时:

 

mysql> 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  | OFF      |

| interactive_timeout         | 28800    |

| lock_wait_timeout           | 31536000 |

| net_read_timeout            | 30       |

| net_write_timeout           | 60       |

| rpl_stop_slave_timeout      | 31536000 |

| slave_net_timeout           | 3600     |

| wait_timeout                | 28800    |

+—————————–+———-+

12 rows in set (0.00 sec)

 

我们来分析下各个参数的意义:

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 as
of MySQL 5.1.23 and 5 seconds before that.

Increasing the connect_timeout value might help if clients frequently
encounter errors of the form Lost connection to MySQL server at ‘XXX’,
system error: errno.

 

解释:在获取链接时,等待握手的超时时间,只在登录时有效,登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快,一般默认即可。

 

delayed_insert_timeout

 

手册描述:

How many seconds an INSERT DELAYED handler thread should wait for INSERT
statements before terminating.

解释:这是为MyISAM INSERT DELAY设计的超时参数,在INSERT
DELAY中止前等待INSERT语句的时间。

 

innodb_lock_wait_timeout

 

手册描述:

The timeout in seconds an InnoDB transaction may wait for a row lock
before giving up. The default value is 50 seconds. A transaction that
tries to access a row that is locked by another InnoDB transaction will
hang for at most this many seconds before issuing the following error:

 

1

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

When a lock wait timeout occurs, the current statement is not executed.
The current transaction is not rolled back. (To have the entire
transaction roll back, start the server with the
–innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See
also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL
table lock does not happen inside InnoDB and this timeout does not apply
to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table
immediately and rolls back one transaction. The lock wait timeout value
does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server
startup. For InnoDB Plugin, it can be set at startup or changed at
runtime, and has both global and session values.

 

解释:描述很长,简而言之,就是事务遇到锁等待时的Query超时时间。跟死锁不一样,InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务,锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源,被回滚的肯定是请求锁的那个Query。

innodb_rollback_on_timeout

 

手册描述:

In MySQL 5.1, 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 (the same behavior as in MySQL 4.1). This variable was added
in MySQL 5.1.15.

 

解释:这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query,打开的话事务遇到超时就回滚整个事务。

 

interactive_timeout/wait_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(). See also

解释:一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态,执行完Query后成为interactive状态,重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程,意义是一样的。一般设置是8小时,一般网站白天都有人访问,从夜里到早上一般都会超过8小时,所以再来访问就会这个问题。

 

net_read_timeout / net_write_timeout

手册描述:

The number of seconds to wait for more data from a connection before
aborting the read. Before MySQL 5.1.41, this timeout applies only to
TCP/IP connections, not to connections made through Unix socket files,
named pipes, or shared memory. 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. See also
slave_net_timeout.

On Linux, the NO_ALARM build flag affects timeout behavior as indicated
in the description of the net_retry_count system variable.

解释:这个参数只对TCP/IP链接有效,分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间,这是在Activity状态下的线程才有效的参数

 

slave_net_timeout

手册描述:

The number of seconds to wait for more data from the master before the
slave considers the connection broken, aborts the read, and tries to
reconnect. The first retry occurs immediately after the timeout. The
interval between retries is controlled by the MASTER_CONNECT_RETRY
option for the CHANGE MASTER TO statement or –master-connect-retry
option, and the number of reconnection attempts is limited by the
–master-retry-count option. The default is 3600 seconds (one hour).

解释:这是Slave判断主机是否挂掉的超时设置,在设定时间内依然没有获取到Master的回应就人为Master挂掉了

最近系统因为数据库连接超时的问题,出现了几次故障。排查了下my.cnf的配置问题,最后是代码设计有问题。正好…

mysql timeout知多少

——by 石锅拌饭

 

1.timeout变量知多少

打开mysql,用show variables like
‘%timeout%’命令一看,不看不知道,一看吓一跳,结果如下面所示,这么多timeout相关变量,一下就吓尿了。。原来对mysql的了解原来是如此的不够,好了,这么些timeout究竟各自是什么意思,花了一下午去学习,做了几个小实验,总算明白了一二,如有错误,请不吝赐教啊。

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

很多时候我们连接mysql会在timeout这里跌倒,这里明确下mysql的timeout:

2.分析

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

下面是获取timeout的变量:

2.1 connect_timeout

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

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 as of MySQL 5.0.52 and 5 seconds before that

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

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

telnet localhost 3306

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

256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL 
mysql> 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    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.04 sec)

2.2 interactive_timeout & wait_timeout

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

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_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).

测试如下:

mysql> set global interactive_timeout=3; ##设置交互超时为3秒

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

mysql> show variables like '%timeout%'; ##wait_timeout已经被设置为3秒
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

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

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away  ##超时重连
No connection. Trying to reconnect...
Connection id:    50
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

如果看session的timeout,也是这些参数设置。

2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout

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

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;

首先插入三条测试数据

mysql> select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |

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

##事务1 加行锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where a=2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.01 sec)

##事务2,请求行锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where a=1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test where a=2; ##请求行锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test;
+---+
| a |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)

mysql> begin; ##这里我们直接开启另外的事务,则原来的事务只会回滚第二条语句,最终结果就是test表中只剩下2和3.如果这里我们显示的rollback,则会回滚整个事务,保持1,2,3不变。

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

官方文档

2.4 lock_wait_timeout

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

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements

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

##第一个session,获取metadata lock
mysql> show create table myisam_test;
-----------------------------------------------------------+
| Table       | Create Table                                                                                                                                |
+-----------------------------------------------------------
| myisam_test | CREATE TABLE `myisam_test` (
  `i` int(11) NOT NULL,
  `j` int(11) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_test;
+---+------+
| i | j    |
+---+------+
| 2 |    1 |
+---+------+
1 row in set (0.00 sec)

##另一个session,删除表提示超时
mysql> drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

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

  • connect_timeout

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类似。

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

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

mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1

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

iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

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

当一个连接上来,在三次握手的时候出现错误,mysql服务器会等待一段时间客户端进行重新连接,connect_timeout就是服务端等待重连的时间了。

3.总结

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

mysql timeout知多少 ——by
石锅拌饭 1.timeout变量知多少 打开mysql,用show variables like
%timeout%命令一看,不看不知道,一看吓一…

  • delayed_insert_timeout