springboot 集成impala连接池问题

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 com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@34b395a2: (connection
 has passed maxLifetime)
2020-06-23 19:43:49,069 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@273fe02c
2020-06-23 19:43:49,569 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@6602ea17
2020-06-23 19:47:50,578 DEBUG (PoolBase.java:147)[HikariPool-1 connection closer]- HikariPool-1 - Closing connection com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@166d2158 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 com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@7af13fae: (conn
ection has passed maxLifetime)
2020-06-23 19:59:36,114 DEBUG (PoolBase.java:147)[HikariPool-1 housekeeper]- HikariPool-1 - Closing connection com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@34b395a2 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 com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@1db905ff: (connection
 has passed maxLifetime)
2020-06-23 19:59:36,446 DEBUG (HikariPool.java:728)[HikariPool-1 connection adder]- HikariPool-1 - Added connection com.cloudera.impala.impala.jdbc41.ImpalaJDBC41Connection@4bcd29fe

连接达到最大生命周期时间，关闭连接超时导致线程池阻塞。此时访问数据库也会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(<generated>)
    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(<generated>)
    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，没有使用连接池，功能暂时是没有问题，性能和保持长连接的访问要差一些。

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。