问题描述系统上线一段时间后经常会报查询数据表总数失败HikariPool-2 - Connection is not available, request timed out after 120000ms.系统背景系统中有定时任务可能定期需要统计数据库下面表的数据信息代码方案1Component public class DorisPoolConfig { private static final MapString, DataSource dataSourceMap new ConcurrentHashMap(); public static final String DATABASE_PARAM ?allowPublicKeyRetrievaltrueserverTimezoneUTCuseUnicodetruecharacterEncodingutf8useSSLfalse; public static final String JDBC_DRIVER com.mysql.cj.jdbc.Driver; private static final int maxPoolSize 10; private static final int minIdle 4; //2分钟 private static final long connectionTimeout 2 * 60 * 1000; // 5分钟 private static final long idleTimeout 5 * 60 * 1000; // 30分钟 private static final long maxLifetime 30 * 60 * 1000; /** * 获取默认引擎连接不同引擎拥有不同的引擎配置 * * param engine Doris引擎配置 * return Doris连接信息 */ public synchronized DataSource getEngineDataSource (Engine engine) { return getDataSourceByPrefix(engine, LjPoolEnum.PAGE.getCode()); } /** * 获取默认引擎连接不同引擎拥有不同的引擎配置 * * param engine Doris引擎配置 * return Doris连接信息 */ public synchronized DataSource getDataSourceByPrefix (Engine engine,String prefix) { String key prefix engine.getId(); //获取数据库类型对应的DataSource DataSource dataSource dataSourceMap.get(key); if (Objects.isNull(dataSource)) { //获取doris连接信息 dataSource getInitDataSource(engine); dataSourceMap.put(key, dataSource); } return dataSource; } /** * 获取Doris引擎连接 * * param engine Doris引擎配置 * return Doris连接信息 */ private DataSource getInitDataSource(Engine engine) { //配置连接池 HikariConfig config new HikariConfig(); config.setJdbcUrl(engine.getUrl() DATABASE_PARAM); config.setDriverClassName(JDBC_DRIVER); config.setUsername(engine.getUsername()); config.setPassword(engine.getPassword()); config.setMaximumPoolSize(maxPoolSize); config.setMinimumIdle(minIdle); config.setConnectionTimeout(connectionTimeout); config.setIdleTimeout(idleTimeout); config.setMaxLifetime(maxLifetime); return new HikariDataSource(config); } }调用代码public long executeQueryCount(DataSource dataSource, String countSql, String exceptionMsg) { // 验证SQL是否安全防止SQL注入 SqlParserUtil.validateSqlBasic(countSql); try (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(countSql); ResultSet rs stmt.executeQuery()) { if (rs.next()) { return rs.getLong(CommonConstant.ONE); } return CommonConstant.ZERO; } catch (Exception e) { exceptionMsg StringUtils.defaultIfBlank(exceptionMsg, 执行executeQueryCount方法失败); log.error(exceptionMsg sql【{}】, countSql); throw new CestcBusinessException(HttpCode.BAD_REQUIRED_ERROR,String.format(%s%s, exceptionMsg, e.getMessage())); } }分析代码发现连接池的参数设置有问题修改后代码方案2package cn.cestc.servicequality.config; import cn.cestc.bigdata.common.bean.Engine; import cn.cestc.servicequality.enums.LjPoolEnum; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.util.Map; import java.util.Objects; import java.util.concurrent.ConcurrentHashMap; /** * 数据库连接池 * */ Component public class DorisPoolConfig { private static final MapString, DataSource dataSourceMap new ConcurrentHashMap(); public static final String DATABASE_PARAM ?allowPublicKeyRetrievaltrueserverTimezoneUTCuseUnicodetruecharacterEncodingutf8useSSLfalse; public static final String JDBC_DRIVER com.mysql.cj.jdbc.Driver; static final int CONNECT_TIMEOUT_MS 10 * 1000; static final int SOCKET_TIMEOUT_MS 10 * 60 * 1000; static final long LEAK_DETECTION_THRESHOLD 60 * 1000; //连接池数量增加也可以改成nacos动态配置 private static final int maxPoolSize 30; private static final int minIdle 4; //2分钟 private static final long connectionTimeout 2 * 60 * 1000; // 5分钟 private static final long idleTimeout 5 * 60 * 1000; // 30分钟 private static final long maxLifetime 30 * 60 * 1000; /** * 获取默认引擎连接不同引擎拥有不同的引擎配置 * * param engine Doris引擎配置 * return Doris连接信息 */ public synchronized DataSource getEngineDataSource (Engine engine) { return getDataSourceByPrefix(engine, LjPoolEnum.PAGE.getCode()); } /** * 获取默认引擎连接不同引擎拥有不同的引擎配置 * * param engine Doris引擎配置 * return Doris连接信息 */ public synchronized DataSource getDataSourceByPrefix (Engine engine,String prefix) { String key prefix engine.getId(); //获取数据库类型对应的DataSource DataSource dataSource dataSourceMap.get(key); if (Objects.isNull(dataSource)) { //获取doris连接信息 dataSource getInitDataSource(engine, prefix); dataSourceMap.put(key, dataSource); } return dataSource; } /** * 获取Doris引擎连接 * * param engine Doris引擎配置 * return Doris连接信息 */ private DataSource getInitDataSource(Engine engine, String prefix) { //配置连接池 HikariConfig config new HikariConfig(); config.setJdbcUrl(buildJdbcUrl(engine.getUrl())); config.setDriverClassName(JDBC_DRIVER); config.setUsername(engine.getUsername()); config.setPassword(engine.getPassword()); applyPoolSettings(config, engine, prefix); return new HikariDataSource(config); } static String buildJdbcUrl(String jdbcUrl) { //设置超时时间 String timeoutParam connectTimeout CONNECT_TIMEOUT_MS socketTimeout SOCKET_TIMEOUT_MS; if (jdbcUrl.contains(?)) { return jdbcUrl DATABASE_PARAM.substring(1) timeoutParam; } return jdbcUrl DATABASE_PARAM timeoutParam; } static void applyPoolSettings(HikariConfig config, Engine engine, String prefix) { //新增poolName便于排查问题 config.setPoolName(prefix engine.getId()); config.setMaximumPoolSize(maxPoolSize); config.setMinimumIdle(minIdle); config.setConnectionTimeout(connectionTimeout); config.setIdleTimeout(idleTimeout); config.setMaxLifetime(maxLifetime); config.setLeakDetectionThreshold(LEAK_DETECTION_THRESHOLD); config.setRegisterMbeans(true); // 关键检测死连接 config.setConnectionTestQuery(SELECT 1); config.setValidationTimeout(50000); // 可选定期保活每30秒 config.setKeepaliveTime(30000); } }// 关键检测死连接config.setConnectionTestQuery(SELECT 1);config.setValidationTimeout(5000);// 可选定期保活每30秒config.setKeepaliveTime(30000);方案对比方面实现1实现2对“找不到连接”的影响连接超时10s connect 600s socket无显式设置依赖驱动默认值可能改善网络不稳定场景泄漏检测60s无有助于发现连接未关闭的bugJMX监控开启关闭便于运维排查最大连接数2010并发压力大时可能减少等待URL参数动态追加参数固定拼接无本质区别连接池命名有无便于日志追踪