HikariPool数据库连接池报错request timed

系统版本 & kernel 版本 】 Linux version 3.10.0-957.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org)

  • TiDB 版本 】5.7.25-TiDB-v2.1.15 MySQL Community Server (Apache License 2.0)
  • 磁盘型号 】 普通机械盘
  • 集群节点分布
  • 数据量 & region 数量 & 副本数 】5G & 1222 & 3
  • 问题描述(我做了什么) 】使用spring boot 的web应用集成tidb做数据库 使用HikariPool数据库连接池过一阵就会连接超时,重启应用web就可以了,是什么原因?
    报错信息:
2019-11-04 10:24:41 http-nio-19088-exec-7- INFO  c.h.h.r.s.s.impl.DataShowServiceImpl - tidb
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:169)
	at com.hydee.h3.report.service.show.impl.DataShowServiceImpl.queryDataBySQL(DataShowServiceImpl.java:139)
	at com.hydee.h3.report.service.show.impl.DataShowServiceImpl$$FastClassBySpringCGLIB$$78801d59.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:174)
	at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:47)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:174)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.hydee.h3.report.service.show.impl.DataShowServiceImpl$$EnhancerBySpringCGLIB$$532ed756.queryDataBySQL(<generated>)
	at com.hydee.h3.report.manager.show.DataShowManager.queryDataByView(DataShowManager.java:190)
	at com.hydee.h3.report.manager.show.DataShowManager.returnReport(DataShowManager.java:124)
	at com.hydee.h3.report.controller.forpublic.show.DataShowController.report(DataShowController.java:70)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:866)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:158)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:126)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:111)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:90)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

注意到日志有报 30000ms 这个参数,建议查看下连接池的空闲超时配置。tidb 这边是没有链接超时控制的

跟空闲超时配置没有关系,不是长时间未连接导致的,通常是正在使用时候出现的。如果空闲一夜不会出现这个问题,一个周末来后直接使用也没问题。中午使用一段时间后出现的,上周五也出现两次

是阿里云的服务器吗?可以尝试下长 ping 检查下网络

不是阿里服务器,现在测试使用,我们内网服务器,偶尔出现,不重启服务器连不上,我也看看网络是不是有问题

网络没啥问题,数据库连接池设置

hikariDataSource.setMaxLifetime(0);
 hikariDataSource.setIdleTimeout(0);

还是会出现这种问题

刚才查看了一下tidb.log

[INFO] [gc_worker.go:304] ["[gc worker] gc interval haven't past since last run, no need to gc"] ["leaderTick on"=5b51c703f740009] [interval=10m0s] ["last run"=2019/11/04 15:40:24.000 +08:00]
[2019/11/04 15:49:46.279 +08:00] [ERROR] [conn.go:490] ["read packet failed, close this connection"] [conn=142873] [error="read tcp 192.168.10.201:4000->192.168.10.161:65034: read: connection reset by peer"] [errorVerbose="read tcp 192.168.10.201:4000->192.168.10.161:65034: read: connection reset by peer
github.com/pingcap/errors.AddStack
	/home/jenkins/workspace/release_tidb_2.1/go/pkg/mod/github.com/pingcap/errors@v0.11.1/errors.go:174
github.com/pingcap/errors.Trace
	/home/jenkins/workspace/release_tidb_2.1/go/pkg/mod/github.com/pingcap/errors@v0.11.1/juju_adaptor.go:15
github.com/pingcap/tidb/server.(*packetIO).readOnePacket
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/packetio.go:70
github.com/pingcap/tidb/server.(*packetIO).readPacket
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/packetio.go:90
github.com/pingcap/tidb/server.(*clientConn).readPacket
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/conn.go:219
github.com/pingcap/tidb/server.(*clientConn).Run
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/conn.go:485
github.com/pingcap/tidb/server.(*Server).onConn
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/server.go:385
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"] [stack="github.com/pingcap/tidb/server.(*clientConn).Run
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/conn.go:490
github.com/pingcap/tidb/server.(*Server).onConn
	/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/server/server.go:385"]
[2019/11/04 15:49:46.280 +08:00] [INFO] [server.go:361] ["close connection"] [conn=142873]
[2019/11/04 15:49:46.279 +08:00] [INFO] [server.go:361] ["close connection"] [conn=142875]

看报错还是网络的问题

好的,我一直ping,看下次连不上时候是不是网络也有问题。 连接池也开自动重连了,为什应用重连连不上,重启可以连上

你好,请问这个问题解决了吗?

抱歉最近有点忙,没有看tidb这块。 解决了,是数据库连接池的问题。手动维护的connect,用完后没有回收导致的。用完归还就可以了

1赞

:+1: 点个赞