mysql 出现大量sleep进程处理
一般是默认连接时间的设置,可以先查询一下 参数
show global variables like 'wait_timeout'; 默认值是28800 差不多是8个小时有效时间
网上推荐设置是10
set global wait_timeout=10;
以上为全局变量
show variables like 'wait_timeout';
set wait_timeout=10;
以上为session 环境变理
虽然能解决sleep进程问题 但是也有后遗症, 会出现以下错误
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 4,301,488 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_111]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_111]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_111]
at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[na:1.8.0_111]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3030) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2916) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3459) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2237) ~[mysql-connector-5.1.8.jar:na]
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) ~[c3p0-0.9.1.2.jar:0.9.1.2]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
... 103 common frames omitted
Caused by: java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_111]
at java.net.SocketInputStream.socketRead(Unknown Source) ~[na:1.8.0_111]
at java.net.SocketInputStream.read(Unknown Source) ~[na:1.8.0_111]
at java.net.SocketInputStream.read(Unknown Source) ~[na:1.8.0_111]
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2472) ~[mysql-connector-5.1.8.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2927) ~[mysql-connector-5.1.8.jar:na]
... 112 common frames omitted
这表示wait_timeout 时间太小 导致的 需要调大一点就能解决.
如果还报此错误 还可以设置此参数
执行set global interactive_timeout=1814400;
关注"都市百货" 了解南陵
微信咨询wanglf2r(不拉群 发广告者勿加)
热门评论