doris 使用catalog 连接mysql ，一段时间后报错：SQLTransientConnectionException: HikariPool-4 - Connection is not available, request timed out after 10000ms.

Viewed 143
2.1

版本：
doris : 2.1
mysql: 5.7.30
doris 中使用 catalog 连接 mysql，使用一段时间后报以下错误：
SQLTransientConnectionException: HikariPool-4 - Connection is not available, request timed out after 10000ms.

重置一下 connection_pool_max_size 参数后可以正常连接，一段时间后又报相同的错误，又需要重置一下

怀凝是 连接池的问题，但我在 doris 和 mysql 查看 processlist 都没有发现超过连接数的现像。

zengyongchuan21
4 Answers

原因 1：网络不通
原因 2：网络延迟高，导致创建连接超过 5s
原因 3：查询太多导致连接个数超出配置
解决方法

  1. 检查网络
  2. alter catalog <catalog_name> set properties ('connection_pool_max_size' = '100'); 调大连接个数
  3. alter catalog <catalog_name> set properties ('connection_pool_max_wait_time' = '10000'); 调大超时时间

更新一下连接的jar包，升级到8.0.30及更高版本

黄海军@SelectDB4403

CREATE CATALOG xxxxx PROPERTIES (
"user" = "xxxxxx",
"use_meta_cache" = "false",
"type" = "jdbc",
"password" = "*XXX",
"jdbc_url" = "jdbc:mysql://xxx.xxx.xx.xx:3307/user_center?yearIsDateType=false&tinyInt1isBit=false&useUnicode=true&rewriteBatchedStatements=true&characterEncoding=utf-8",
"driver_url" = "mysql-connector-java-8.0.25.jar",
"driver_class" = "com.mysql.cj.jdbc.Driver",
"create_time" = "2024-08-14 17:34:55.015",
"connection_pool_max_wait_time" = "10000",
"connection_pool_max_size" = "20",
"checksum" = "fdf55dcef04b09f2eaf42b75e61ccc9a"
);

zengyongchuan21

当出现 以上错误时，我在日志中发现以下信息：
W20240905 09:15:24.959260 917146 mem_tracker_limiter.cpp:389]
Process Memory Summary:
os physical memory 7.32 GB. process memory used 3.99 GB(= 4.27 GB[vm/rss] - 292.11 MB[tc/jemalloc_cache] + 0[reserved] + 0B[waiting_refresh]), limit 4.39 GB, soft limit 3.95 GB. sys available memory 367.47 MB(= 367.47 MB[proc/available] - 0[reserved] - 0B[waiting_refresh]), low water mark 374.95 MB, warning water mark 749.89 MB.
Memory Tracker Summary:
MemTrackerLimiter Label=other, Type=overview, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=schema_change, Type=overview, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=compaction, Type=overview, Limit=-1.00 B(-1 B), Used=1.79 GB(1919304482 B), Peak=2.45 GB(2631410904 B)
MemTrackerLimiter Label=load, Type=overview, Limit=-1.00 B(-1 B), Used=185.56 KB(190016 B), Peak=1.86 GB(1993315104 B)
MemTrackerLimiter Label=query, Type=overview, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=765.01 MB(802166357 B)
MemTrackerLimiter Label=global, Type=overview, Limit=-1.00 B(-1 B), Used=128.15 MB(134370393 B), Peak=995.97 MB(1044351392 B)
MemTrackerLimiter Label=tc/jemalloc_cache, Type=overview, Limit=-1.00 B(-1 B), Used=292.11 MB(306304560 B), Peak=-1.00 B(-1 B)
MemTrackerLimiter Label=tc/jemalloc_metadata, Type=overview, Limit=-1.00 B(-1 B), Used=267.45 MB(280437696 B), Peak=-1.00 B(-1 B)
MemTrackerLimiter Label=sum of all trackers, Type=overview, Limit=-1.00 B(-1 B), Used=2.46 GB(2640607147 B), Peak=-1.00 B(-1 B)
MemTrackerLimiter Label=process resident memory, Type=overview, Limit=-1.00 B(-1 B), Used=4.27 GB(4586926080 B), Peak=4.75 GB(5096165376 B)
MemTrackerLimiter Label=reserve_memory, Type=overview, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=-1.00 B(-1 B)
MemTrackerLimiter Label=process virtual memory, Type=overview, Limit=-1.00 B(-1 B), Used=38.53 GB(41375506432 B), Peak=38.53 GB(41375506432 B)
MemTrackerLimiter Label=Orphan, Type=global, Limit=-1.00 B(-1 B), Used=27.68 MB(29027423 B), Peak=27.78 MB(29127375 B)
MemTrackerLimiter Label=DetailsTrackerSet, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTracker Label=PageNoCache, Parent Label=DetailsTrackerSet, Used=2.38 MB(2492866 B), Peak=80.76 MB(84684646 B)
MemTracker Label=IOBufBlockMemory, Parent Label=DetailsTrackerSet, Used=53.70 MB(56303616 B), Peak=131.58 MB(137969664 B)
MemTracker Label=SegmentCache[size], Parent Label=DetailsTrackerSet, Used=54.29 MB(56930911 B), Peak=306.58 MB(321474555 B)
MemTracker Label=SchemaCache[number], Parent Label=DetailsTrackerSet, Used=122.00 KB(124926 B), Peak=122.70 KB(125642 B)
MemTracker Label=InvertedIndexSearcherCache[size], Parent Label=DetailsTrackerSet, Used=439.68 KB(450235 B), Peak=3.15 MB(3302989 B)
MemTracker Label=InvertedIndexQueryCache[size], Parent Label=DetailsTrackerSet, Used=7.21 KB(7383 B), Peak=7.43 KB(7606 B)
MemTracker Label=AllMemTableMemory, Parent Label=DetailsTrackerSet, Used=0(0 B), Peak=1.17 GB(1258694830 B)
MemTracker Label=TabletSchemaCache[number], Parent Label=DetailsTrackerSet, Used=48.20 KB(49357 B), Peak=49.33 KB(50518 B)
MemTracker Label=TabletMeta(experimental), Parent Label=DetailsTrackerSet, Used=622.27 KB(637200 B), Peak=624.38 KB(639360 B)
MemTracker Label=MowTabletVersionCache[number], Parent Label=DetailsTrackerSet, Used=1.67 MB(1756202 B), Peak=1.67 MB(1756202 B)
MemTracker Label=CreateTabletRRIdxCache[number], Parent Label=DetailsTrackerSet, Used=3.55 KB(3638 B), Peak=3.66 KB(3745 B)
MemTracker Label=MowDeleteBitmapAggCache[size], Parent Label=DetailsTrackerSet, Used=34.99 MB(36694285 B), Peak=57.22 MB(59997556 B)
MemTrackerLimiter Label=SegCompaction, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=RowIdStorageReader, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=SubcolumnsTree, Type=global, Limit=-1.00 B(-1 B), Used=4.31 MB(4521984 B), Peak=36.62 MB(38395904 B)
MemTrackerLimiter Label=S3FileBuffer, Type=global, Limit=-1.00 B(-1 B), Used=0(0 B), Peak=0(0 B)
MemTrackerLimiter Label=DataPageCachesize, Type=global, Limit=-1.00 B(-1 B), Used=35.77 MB(37505663 B), Peak=540.09 MB(566327603 B)
MemTrackerLimiter Label=IndexPageCachesize, Type=global, Limit=-1.00 B(-1 B), Used=39.58 MB(41501389 B), Peak=86.79 MB(91002966 B)
MemTrackerLimiter Label=PKIndexPageCachesize, Type=global, Limit=-1.00 B(-1 B), Used=20.80 MB(21813934 B), Peak=478.20 MB(501430450 B)
MemTrackerLimiter Label=BaseCompaction:263718, Type=compaction, Limit=-1.00 B(-1 B), Used=1.79 GB(1919304482 B), Peak=1.79 GB(1919421407 B)
MemTrackerLimiter Label=Load#Id=fecbc4c4423f47ef-9c6ca6d3c5122c97, Type=load, Limit=2.00 GB(2147483648 B), Used=52.50 KB(53760 B), Peak=52.50 KB(53760 B)
MemTrackerLimiter Label=Load#Id=9e1a0b1aed3346b8-95567a545ce5c056, Type=load, Limit=2.00 GB(2147483648 B), Used=52.50 KB(53760 B), Peak=52.50 KB(53760 B)
MemTrackerLimiter Label=Load#Id=768753ce62bc4044-a10eb7cdf47381c6, Type=load, Limit=2.00 GB(2147483648 B), Used=40.28 KB(41248 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=ecff34e89ace45a2-a1070446dc4365d6, Type=load, Limit=2.00 GB(2147483648 B), Used=40.28 KB(41248 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=86235bf68f404c0a-b45038258aef0cd4, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=35c20c484aa54b88-a4ea20c55187b893, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=3769598d00524f97-b0cda16661e69fe0, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=52.50 KB(53760 B)
MemTrackerLimiter Label=Load#Id=bd250a62783c42e0-a2a75a551a5cdb18, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=ee7d9d0d3e91447b-994f24ccd4e5b641, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=52.50 KB(53760 B)
MemTrackerLimiter Label=Load#Id=6b21926fceb24bb6-a6167716463e9bac, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=e3eb7c1fb5854729-a8d557852cea0698, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=d60c0c1cef494082-9009269815ee53c5, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=68dd54e198324806-aa686035e076d841, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=40.28 KB(41248 B)
MemTrackerLimiter Label=Load#Id=ca224f3eb29848c8-a5b5caf392ec505e, Type=load, Limit=2.00 GB(2147483648 B), Used=0(0 B), Peak=52.50 KB(53760 B)
MemTracker Label=AllMemTableMemory, Parent Label=DetailsTrackerSet, Used=0(0 B), Peak=1.17 GB(1258694830 B)
W20240905 09:15:24.966629 917146 merger.cpp:277] failed to write block when merging rowsets of tablet 263718, error: [MEM_LIMIT_EXCEEDED]PreCatch error code:11, [E11] Allocator sys memory check failed: Cannot alloc:20979, consuming tracker:, peak used 1919421407, current used 1919304482, exec node:<>, process memory used 3.99 GB exceed limit 4.39 GB or sys available memory 367.47 MB less than low water mark 374.95 MB.
....
....
....
W20240905 09:15:27.951030 917960 pipeline_x_fragment_context.cpp:154] PipelineXFragmentContext cancel instance: fecbc4c4423f47ef-9c6ca6d3c5122c99
W20240905 09:15:27.951045 917960 pipeline_x_fragment_context.cpp:154] PipelineXFragmentContext cancel instance: fecbc4c4423f47ef-9c6ca6d3c5122c98
W20240905 09:15:27.951092 916887 fragment_mgr.cpp:433] report error status: JdbcExecutorException: Initialize datasource failed:
CAUSED BY: SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 10000ms. to coordinator: TNetworkAddress(hostname=172.21.70.46, port=9020), query id: fecbc4c4423f47ef-9c6ca6d3c5122c97, instance id: 0-0
W20240905 09:15:27.951107 916862 fragment_mgr.cpp:433] report error status: JdbcExecutorException: Initialize datasource failed:
CAUSED BY: SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 10000ms. to coordinator: TNetworkAddress(hostname=172.21.70.46, port=9020), query id: fecbc4c4423f47ef-9c6ca6d3c5122c97, instance id: 0-0
W20240905 09:15:27.951169 916834 load_stream_stub.cpp:340] LoadStreamStub load_id=fecbc4c4423f47ef-9c6ca6d3c5122c97, src_id=10108, dst_id=10108, stream_id=60129557274 is cancelled because of [CANCELLED]JdbcExecutorException: Initialize datasource failed:
CAUSED BY: SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 10000ms.
W20240905 09:15:27.951184 916834 load_stream_stub.cpp:340] LoadStreamStub load_id=fecbc4c4423f47ef-9c6ca6d3c5122c97, src_id=10108, dst_id=10108, stream_id=77309577415 is cancelled because of [CANCELLED]JdbcExecutorException: Initialize datasource failed:
CAUSED BY: SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 10000ms.
W20240905 09:15:27.992295 1548107 jni-util.cpp:259] org.apache.doris.jdbc.JdbcExecutorException: Initialize datasource failed:
at org.apache.doris.jdbc.BaseJdbcExecutor.init(BaseJdbcExecutor.java:345)
at org.apache.doris.jdbc.BaseJdbcExecutor.(BaseJdbcExecutor.java:93)
at org.apache.doris.jdbc.MySQLJdbcExecutor.(MySQLJdbcExecutor.java:50)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 10000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
at org.apache.doris.jdbc.BaseJdbcExecutor.init(BaseJdbcExecutor.java:334)

不知是不是因内存不足的原因导致不能创建catalog 的连接池？

zengyongchuan21

内存太小了吧 感觉 建议加点内存看看

daidai1