TDengine连接数满了报错:Connection is not available, request timed out after 30000ms.

背景

使用tdengine时遇到一个情况,应用程序使用连接池将接收到的实时数据插入TD时, 会出现获取不到连接的报错.

重现

起一个模拟程序,发送100条数据到应用程序, 应用程序从批量插入改为逐条插入td, 重现出报错:

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta-data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:363)
	at org.springframework.jdbc.support.SQLErrorCodesFactory.resolveErrorCodes(SQLErrorCodesFactory.java:235)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.lambda$setDataSource$0(SQLErrorCodeSQLExceptionTranslator.java:140)
	at org.springframework.util.function.SingletonSupplier.get(SingletonSupplier.java:97)
	at org.springframework.util.function.SupplierUtils.resolve(SupplierUtils.java:40)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.getSqlErrorCodes(SQLErrorCodeSQLExceptionTranslator.java:171)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:193)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy73.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy77.insertIntoTd(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor72.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy78.insertIntoTd(Unknown Source)
	at com.dgis.k2h.service.DcirDataService.insertIntoTd(DcirDataService.java:68)
	at com.dgis.k2h.redis.DcirStreamListener.onMessage(DcirStreamListener.java:69)
	at com.dgis.k2h.redis.DcirStreamListener.onMessage(DcirStreamListener.java:14)
	at org.springframework.data.redis.stream.StreamPollTask.deserializeAndEmitRecords(StreamPollTask.java:177)
	at org.springframework.data.redis.stream.StreamPollTask.doLoop(StreamPollTask.java:148)
	at org.springframework.data.redis.stream.StreamPollTask.run(StreamPollTask.java:132)
	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)

验证是数据库连接数的问题

我们将应用程序的连接池数量降低到1个,确保少于数据库的最大连接数, 看看是否还有连接获取出错的报错就行了.
应用程序的连接池配置:

      # 初始化连接大小
      initial-size: 1
      # 最小空闲连接数
      min-idle: 0
      max-active: 1
      max-wait: 60000

再次尝试模拟发送100条数据
正常打印日志, 问题消失. 说明就是应用程序的连接池数量高于服务器最大连接数了

dcir获取到数据。messageId=1690771208242-0, stream=dcir
dcir获取到数据。messageId=1690771208252-0, stream=dcir
dcir获取到数据。messageId=1690771208233-0, stream=dcir
dcir获取到数据。messageId=1690771208253-0, stream=dcir
dcir获取到数据。messageId=1690771208254-0, stream=dcir
dcir获取到数据。messageId=1690771208257-0, stream=dcir
dcir获取到数据。messageId=1690771208258-0, stream=dcir
dcir获取到数据。messageId=1690771208259-0, stream=dcir
dcir获取到数据。messageId=1690771208261-0, stream=dcir
dcir获取到数据。messageId=1690771208261-1, stream=dcir

分析:

考虑以下两种方法解决问题:

1. 增加td的最大连接数, 比连接池的最大连接数更大, 这样瓶颈就在连接池而不是td这边了.
2. 考虑使用缓存, 积累到一定数量的数据再处理, Td可以进行批量插入