HikariDataSource連接配接池
配置
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.cloudera.impala.jdbc41.Driver
url: jdbc:impala://118.31.173.39:25001/bill_gypoc_0802
hikari:
minimumIdle: 5
maximumPoolSize: 5
問題
2020-06-23 19:43:48,404 DEBUG (PoolBase.java:134)[HikariPool-1 housekeeper]- HikariPool-1 - Closing connection [email protected]2: (connection
has passed maxLifetime)
2020-06-23 19:43:49,069 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection [email protected]c
2020-06-23 19:43:49,569 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection [email protected]7
2020-06-23 19:47:50,578 DEBUG (PoolBase.java:147)[HikariPool-1 connection closer]- HikariPool-1 - Closing connection [email protected]8 failed
java.sql.SQLNonTransientConnectionException: [Cloudera][JDBC](10060) Connection has been closed.
at com.cloudera.impala.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.closeConnection(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.abortInternal(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.close(Unknown Source)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2020-06-23 19:47:50,579 DEBUG (HikariPool.java:517)[HikariPool-1 connection closer]- HikariPool-1 - Fill pool skipped, pool is at sufficient level.
2020-06-23 19:47:50,579 DEBUG (PoolBase.java:134)[HikariPool-1 connection closer]- HikariPool-1 - Closing connection [email protected]e: (conn
ection has passed maxLifetime)
2020-06-23 19:59:36,114 DEBUG (PoolBase.java:147)[HikariPool-1 housekeeper]- HikariPool-1 - Closing connection [email protected]2 failed
java.sql.SQLNonTransientConnectionException: [Cloudera][JDBC](10060) Connection has been closed.
at com.cloudera.impala.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.closeConnection(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.abortInternal(Unknown Source)
at com.cloudera.impala.jdbc.common.SConnection.close(Unknown Source)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:143)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:451)
at java.util.concurrent.ThreadPoolExecutor$CallerRunsPolicy.rejectedExecution(ThreadPoolExecutor.java:2038)
at java.util.concurrent.ThreadPoolExecutor.reject(ThreadPoolExecutor.java:830)
at java.util.concurrent.ThreadPoolExecutor.execute(ThreadPoolExecutor.java:1379)
at com.zaxxer.hikari.pool.HikariPool.closeConnection(HikariPool.java:450)
at com.zaxxer.hikari.pool.HikariPool.softEvictConnection(HikariPool.java:615)
at com.zaxxer.hikari.pool.HikariPool.lambda$createPoolEntry$2(HikariPool.java:486)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2020-06-23 19:59:36,115 DEBUG (HikariPool.java:517)[HikariPool-1 housekeeper]- HikariPool-1 - Fill pool skipped, pool is at sufficient level.
2020-06-23 19:59:36,115 WARN (HikariPool.java:787)[HikariPool-1 housekeeper]- HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=15m47s712ms92µs886ns).
2020-06-23 19:59:36,116 DEBUG (HikariPool.java:421)[HikariPool-1 housekeeper]- HikariPool-1 - Pool stats (total=4, active=0, idle=4, waiting=0)
2020-06-23 19:59:36,116 DEBUG (HikariPool.java:517)[HikariPool-1 housekeeper]- HikariPool-1 - Fill pool skipped, pool is at sufficient level.
2020-06-23 19:59:36,116 DEBUG (PoolBase.java:134)[HikariPool-1 housekeeper]- HikariPool-1 - Closing connection [email protected]f: (connection
has passed maxLifetime)
2020-06-23 19:59:36,446 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection [email protected]e
連接配接達到最大生命周期時間，關閉連接配接逾時導緻線程池阻塞。此時通路資料庫也會hold不傳回。
解決方法
添加參數：
maxLifetime: 0
代表每個連接配接的生命周期無限長，目前測試沒有發現問題。
druid連接配接池
配置
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.cloudera.impala.jdbc41.Driver
url: jdbc:impala://118.31.173.39:25001/bill_gypoc_0802
問題
java.util.concurrent.ExecutionException: org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [SELECT cust_code FROM `bill_gypoc_0802`.`m_diag_tag_cust` WHERE busi_date=? AND tag_code=? AND tag_val='4']; [Cloudera][ImpalaJDBCDriver](500593) Communication link failure. Failed to connect to server. Reason: java.net.SocketException: Broken pipe (Write failed).; nested exception is java.sql.SQLException: [Cloudera][ImpalaJDBCDriver](500593) Communication link failure. Failed to connect to server. Reason: java.net.SocketException: Broken pipe (Write failed).
at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357)
at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1895)
at com.dengtacj.personasapi.manager.CustomTagManager.listCustomCode(CustomTagManager.java:75)
at com.dengtacj.personasapi.service.CustomTagServiceImpl.listCustomWithTagQry(CustomTagServiceImpl.java:68)
at com.dengtacj.personasapi.controller.CustomTagController.listCustomWithTag(CustomTagController.java:31)
at com.dengtacj.personasapi.controller.CustomTagController$$FastClassBySpringCGLIB$$cff60dc1.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at com.dengtacj.personasapi.aop.WebLogAspect.doAround(WebLogAspect.java:75)
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.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:47)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
at com.dengtacj.personasapi.controller.CustomTagController$$EnhancerBySpringCGLIB$$56c3335a.listCustomWithTag()
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:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
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:53)
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.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
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)
com.cloudera.impala.jdbc41.DataSource
配置
type: com.cloudera.impala.jdbc41.DataSource
driver-class-name: com.cloudera.impala.jdbc41.Driver
url: jdbc:impala://118.31.173.39:25001/bill_gypoc_0802
使用cloudera驅動一起提供的官方datasource，這個其實并沒有使用連接配接池，每次通路時候建立連接配接，完成後立即關閉連接配接。
結論
com.cloudera.impala.jdbc41.Driver 可能是存在一些問題，例如連接配接關閉長時間不釋放。導緻主流的連接配接池使用這個驅動會遇到問題。目前從有限測試看HikariDataSource保持連接配接一直不斷，暫時比較穩定。使用cloudera提供的Datasource，沒有使用連接配接池，功能暫時是沒有問題，性能和保持長連接配接的通路要差一些。