MySQL wait_timeout参数设置与网上常见错误小纠

vbfh

应用遇到异常报警：
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure  

The last packet successfully received from the server was 23,579 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.  
        at sun.reflect.GeneratedConstructorAccessor40.newInstance(Unknown Source)  
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)  
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)  
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)  
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)  
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)  
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)  
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)  
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)  
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1364)  
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)  
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)  
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)  
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)  
        ... 36 more  
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.  
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502)  
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2952)  
        ... 47 more  


问题比较明显，应用连接已经失效。

23.579s之前创建了一个连接，之后再次使用此数据库连接就超时了。


查看数据库配置：
mysql> show  global  variables like  'wait_timeout';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| wait_timeout  | 20    |  
+---------------+-------+  
1 row in set (0.00 sec)  

服务器设置的时间为20s.从而说明上面超时的原因。

解决这个方法：


一：从应用层解决问题：修改连接池参数  缩短连接池内的连接超时时间。


二：从DB层解决问题：增大MySQL的连接失效时间。
mysql> show  global  variables like  'wait_timeout';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| wait_timeout  | 20    |  
+---------------+-------+  
1 row in set (0.00 sec)  


动态修改global参数，wait_timeout;

set global wait_timeout=30;


再次查看参数：
mysql> show  global  variables like  'wait_timeout';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| wait_timeout  | 30    |  
+---------------+-------+  
1 row in set (0.00 sec)  

设置完毕。

时间的大小设置不能随便，时间过长，导致过多的connection Sleep,占用较多系统资源。时间设置过小也不行。从业务出发，结合查询的耗时与业务吞吐量来进行设置比较合适吧。





常见误解：



网上有很多文章 没有清晰的认识 会话变量与全局变量的不同，查看了会话变量的超时时间，最终可能会导致将全局变量 wait_timeout设置成了31536000s！！


[mysqld]

wait_timeout=31536000



my.cnf里设置的参数是global级别。不是会话级别。



然而：

mysql> show variables like  'wait_timeout';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| wait_timeout  | 28800 |  
+---------------+-------+  
1 row in set (0.00 sec)  

是会话级别。有些人会把会话参数误认为全局参数。


正确的参数初始化设置应该为：

[mysqld]

wait_timeout=30







建议大家今后查询变量的时候不要使用缺省。

查询会话变量：
mysql> show session variables like  '%wait%';   
+--------------------------+----------+  
| Variable_name            | Value    |  
+--------------------------+----------+  
| innodb_lock_wait_timeout | 50       |  
| innodb_spin_wait_delay   | 6        |  
| lock_wait_timeout        | 31536000 |  
| wait_timeout             | 28800    |  
+--------------------------+----------+  
4 rows in set (0.00 sec)  


查询全局变量：
mysql> show global variables like  '%wait%';         
+--------------------------+----------+  
| Variable_name            | Value    |  
+--------------------------+----------+  
| innodb_lock_wait_timeout | 50       |  
| innodb_spin_wait_delay   | 6        |  
| lock_wait_timeout        | 31536000 |  
| wait_timeout             | 30       |  
+--------------------------+----------+  
4 rows in set (0.00 sec)  

ahyz

执子之手、与子偕老？你演给我看吧．