数据库连接池的配置问题-空闲线程的监控和回收. druid 1.8的一个bug

直接原因是 tcp 连接 是因为 Connection reset
分析: 很有可能是 druid 连接池,提供了一个已经失效的连接. 但如果已经失效会使用下一个连接的. 


 源代码:
{
  • MysqlIO.reuseAndReadPacket(Buffer, int) (com.mysql.jdbc) //内部抛 SocketException
} catch (IOException ioEx) {
    throw SQLError.createCommunicationsException(this.connection, this.lastPacketSentTimeMs, this.lastPacketReceivedTimeMs, ioEx,
            getExceptionInterceptor());

TCP连接的状态详解以及故障排查

java 中的connection reset 异常处理分析

 

错误堆栈:

 org.springframework.dao.RecoverableDataAccessException: ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet successfully received from the server was 4,861 milliseconds ago. The last packet sent successfully to the server was 4,799 milliseconds ago.


Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 4,861 milliseconds ago.  The last packet sent successfully to the server was 4,799 milliseconds ago.
        at sun.reflect.GeneratedConstructorAccessor280.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3670)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4110)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:118)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
        at sun.reflect.GeneratedMethodAccessor250.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:46)
       
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:57)
        at com.sun.proxy.$Proxy54.query(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor250.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:46)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:57)
        at com.sun.proxy.$Proxy54.query(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:62)
        at sun.reflect.GeneratedMethodAccessor253.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
        ... 43 more
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:196)
        at java.net.SocketInputStream.read(SocketInputStream.java:122)
        at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
        at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
        at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3570)
        ... 87 more

文章1:

http://www.jb51.net/article/34888.htm http://www.jb51.net/article/34888.htm 

很大的一个原因除了 服务端断开连接外, 还有就是 业务本身请求的连接太多了.

文章2: Connection reset的一种 case. 对方已关闭.

         tcp 服务端FIN_WAIT_2 状态下,客户端操作的抛错实验                     




 * jdbc pool : lhttp://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html  (TheJDBC Connection Poolorg.apache.tomcat.jdbc.pool is a replacement or an alternative to theApache Commons DBCP connection pool. )

  * druid的中文配置说明 (配置项和dbcp是一致的),不如英文来的明了。” https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表 “

 jdbc连接池连接过多且又空闲不使用就需要进行回收。这个不管是数据库连接池还是java线程池都有一样的诉求。

 jdbc连接池不同于线程池的是,连接有可能提前被数据库关闭掉。这个将导致后续的请求使用该连接的时候抛错或者 通过重连导致超时


故为解决该问题需要配置配置。

<property name="testWhileIdle" value="true" />

<!-- 配置限制超时限制,如果超过此时间进行一次检测,如果连接失效(被服务端关闭),那么就驱逐(evict)该连接,单位是毫秒.
c3p0中取名为 idleConnectionTestPeriod. 
druid有DruidDataSourceC3P0Adapter类适配.
--><property name="timeBetweenEvictionRunsMillis" value="${jdbc.idleConnectionTestPeriod}" /><!-- 配置一个连接在池中最小可驱逐时间超过这个时间,就可驱逐. 单位是毫秒 --><property name="minEvictableIdleTimeMillis" value="${jdbc.maxIdleTime}" />
 我们系统中目前设置的是jdbc.maxIdleTime=14400000 ,minIdle=20
14400000/3600/1000 = 4小时
我们的数据库目前设置为3分钟即断开。
故客户端连接池设置时间短一点:2.9分钟,会回收连接直到20.剩下的20个连接即使一直空闲也不回收,符合连接池的意义。但是数据库会把这20个连接关闭。 故每次获取连接时会发现连接失效! 依旧无法解决抛错或者超时的问题。 怎么办?把 testWhileIld打开后,每次从连接池里获取连接,发现超过了idleConnectionTestPeriod,就会进行检测.  
if (isTestWhileIdle()) {
    final long currentTimeMillis = System.currentTimeMillis();
    final long lastActiveTimeMillis = poolableConnection.getConnectionHolder().getLastActiveTimeMillis();
    final long idleMillis = currentTimeMillis - lastActiveTimeMillis;
    long timeBetweenEvictionRunsMillis = this.getTimeBetweenEvictionRunsMillis();
    if (timeBetweenEvictionRunsMillis <= 0) {
        timeBetweenEvictionRunsMillis = DEFAULT_TIME_BETWEEN_EVICTION_RUNS_MILLIS;
    }

    if (idleMillis >= timeBetweenEvictionRunsMillis) {
        boolean validate = testConnectionInternal(poolableConnection.getConnection());
        if (!validate) {
            if (LOG.isDebugEnabled()) {
                LOG.debug("skip not validate connection.");
            }

            discardConnection(realConnection);
            continue;
        }
    }
}
只能查看druid的监控,查看峰值活跃数。不停调整值
{  "url": "jdbc:mysql://xxxx?characterEncoding=UTF-8",  "dbType": "mysql",  "name": "DataSource-4129454",  "activeCount": 0,  "activePeak": 4,  "activePeakTime": "2016-02-16 00:00:03",  "poolingCount": 5,  "poolingPeak": 6,  "poolingPeakTime": "2016-02-15 23:59:25",  "connectCount": 8788,  "closeCount": 8787,  "executeCount": 9757,  "commitCount": 485,  "pstmtCacheHitCount": 9753,  "pstmtCacheMissCount": 4,  "startTransactionCount": 485,  "transactionHistogram": [    0,    485  ],  "connectionHoldTimeHistogram": [    8274,    513  ],  ]}
{  "url": "jdbc:mysql://xxx?characterEncoding=UTF-8",  "dbType": "mysql",  "name": "DataSource-4129454",  "activeCount": 0,  "activePeak": 4,  "activePeakTime": "2016-02-15 22:00:10",  "poolingCount": 4,  "poolingPeak": 4,  "poolingPeakTime": "2016-02-15 21:59:13",  "connectCount": 9354,  "closeCount": 9354,  "executeCount": 9962,  "errorCount": 1,  "commitCount": 305,  "pstmtCacheHitCount": 9956,  "pstmtCacheMissCount": 6,  "startTransactionCount": 305,  "transactionHistogram": [    0,    305  ],  "connectionHoldTimeHistogram": [    8999,    353,    2  ]}
发布了371 篇原创文章 · 获赞 69 · 访问量 87万+
展开阅读全文

Druid DataSource discard connection

04-19

RT ! SpringBoot + Druid + Hibernate configuration: ``` spring: datasource: type: com.alibaba.druid.pool.DruidDataSource url: **** username: root password: **** driverClassName: com.mysql.jdbc.Driver initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 removeAbandoned: false removeAbandonedTimeout: 300 logAbandoned: true timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000作用 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: true testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 filters: stat ``` console: ``` 15:43:03.306 [http-nio-8080-exec-5] ERROR com.alibaba.druid.pool.DruidDataSource - discard connection com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 74,907 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:988) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3552) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3452) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3893) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2714) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:145) at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:434) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:186) ... ... ... na.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:107) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:784) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:802) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1410) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) 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:3004) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462) ... 194 common frames omitted 15:43:03.313 [http-nio-8080-exec-5] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08S01 15:43:03.319 [http-nio-8080-exec-5] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Communications link failure The last packet successfully received from the server was 74,907 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago. ... ``` 问答

oracle(rac)+druid 连接异常,connect reset

10-26

各位好!最近在线上遇到一个问题,重启应用服务后第一次访问以及闲置一段时间都会出现下面的错误,已经排除了防火墙、驱动包,且加上druid提供的testOnBorrow也不能解决第一次的问题! [DruidDataSource.java:1111] discard connection java.sql.SQLRecoverableException: IO 错误: Connection reset at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1067) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1207) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495) at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:676) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:731) at com.csii.ccbs.aplt.tran.ThreadTranArea.queryForObject(ThreadTranArea.java:937) at com.csii.ccbs.aplt.TranContext.queryForObject(TranContext.java:1135) at com.csii.ccbs.aplt.service.trs.impl.CtranctServiceImpl.getTranControl(CtranctServiceImpl.java:66) at com.csii.ccbs.aplt.tran.ThreadTranArea.getCtranct(ThreadTranArea.java:579) at com.csii.ccbs.aplt.TranContext.getCtranct(TranContext.java:718) at com.csii.ccbs.aplt.oltp.base.template.CcbsTranTemplate.execute(CcbsTranTemplate.java:110) at com.csii.pe.chain.command.DelegateCommand.execute(DelegateCommand.java:39) at com.csii.pe.chain.ChainImpl.execute(ChainImpl.java:114) at com.csii.pe.core.CoreControllerImpl.execute(CoreControllerImpl.java:113) at com.csii.ccbs.aplt.monitor.MoniteredCoreController.execute(MoniteredCoreController.java:195) at com.csii.pe.channel.stream.AbstractBaseHandler.handleInternal(AbstractBaseHandler.java:211) at com.csii.pe.channel.stream.AbstractBaseHandler.handle(AbstractBaseHandler.java:107) at com.csii.pe.service.comm.tcp.TcpServer.internalHandle(TcpServer.java:281) at com.csii.pe.service.comm.tcp.TcpServer.access$7(TcpServer.java:263) at com.csii.pe.service.comm.tcp.TcpServer$3.run(TcpServer.java:256) at com.csii.pe.service.executor.PooledExecutor$InnerRunnable.run(PooledExecutor.java:227) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:196) at java.net.SocketInputStream.read(SocketInputStream.java:122) at oracle.net.ns.Packet.receive(Packet.java:308) at oracle.net.ns.DataPacket.receive(DataPacket.java:106) at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:324) at oracle.net.ns.NetInputStream.read(NetInputStream.java:268) at oracle.net.ns.NetInputStream.read(NetInputStream.java:190) at oracle.net.ns.NetInputStream.read(NetInputStream.java:107) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124) at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:350) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046) ... 31 common frames omitted 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览