数据库连接池Hikari配置及监控

Hikari常用连接参数说明

Hikari 为 SpringBoot2.0默认的连接池，Hikary常用连接参数说明

参数	说明
maxLifeTime	池中连接的最大生存期。使用中的连接永远不会停止使用，只有在关闭连接后才将其删除。默认30min，不能小于30s，若小于，将设置为默认值
connectionTimeout	等待来自池的连接的最大毫秒数。默认30min，不能小于250ms，若小于，将设置为默认值
validationTimeout	测试连接的活动性的最长时间。默认5min，不能小于250ms，若小于，将设置为默认值。此值必须小于connectionTimeout
maxPoolSize	池达到的最大连接，包括空闲和使用中的连接。默认10。需按实际应用运行设置
minIdle	池中的最小空闲连接数。默认=maxPoolSize，若小于0或大于maxPoolSize，则设置为maxPoolSize。为了获得峰值需求响应，可不设置
idleTimeout	允许连接在池中保持空闲状态的最长时间。若idleTimeout>maxLifeTime，则设置为0，若idleTimeout<10s，则设置为10min，若minIdle == maxPoolSize并且idleTimeout<>10s，则设置不起作用
auto-commit	从池返回的连接的默认自动提交行为，默认true。

业务应用配置

连接池配置，以供参考。

hikari:
      pool-name: carpool
      minimum-idle: 3
      maximum-pool-size: 10
      idle-timeout: 30000
      connection-timeout: 30000
      max-lifetime: 60000

如有以下警告信息

Failed to validate connection org.postgresql.jdbc.PgConnection@605c8e77 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.

或抛出以下异常，则可将maxLifetime设的短一点。

2023-12-02 10:56:20.103 [32mERROR[0;39m --- [http-nio-8080-exec-2] [36mo.a.c.c.C.[.[localhost].[/].[dispatcherServlet][0;39m : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: carpool - Connection is not available, request timed out after 20056ms.] with root cause
org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:865)
	at org.postgresql.jdbc.PgConnection.setNetworkTimeout(PgConnection.java:1647)
	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:560)
	at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)

HikariCP监控

若需要监控数据库连接池设置是否合理, 需要能监控到连接池对应的指标, 具体如何操作，可依据以下进行配置。.

1. 增加依赖

<dependency>
	<groupId>io.dropwizard.metrics</groupId>
	<artifactId>metrics-core</artifactId>
	<version>4.0.7</version>
</dependency>

 2. 增加配置类

@Configuration
public class DataSourceConfig {
    private final static Logger LOGGER = LoggerFactory.getLogger(DataSourceConfig.class);

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource dataSource(DataSourceProperties properties){
        HikariDataSource dataSource = properties.initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
        dataSource.setMetricRegistry(initMetricRegistry("carpool"));
        return dataSource;
    }

    public MetricRegistry initMetricRegistry(String poolName) {
        MetricRegistry metricRegistry = new MetricRegistry();
        Slf4jReporter reporter = Slf4jReporter.forRegistry(metricRegistry)
                .outputTo(LOGGER)
                .convertRatesTo(TimeUnit.SECONDS)
                .convertDurationsTo(TimeUnit.MILLISECONDS)
                .build();
        reporter.start(30, TimeUnit.SECONDS);//30秒打印一次
        return metricRegistry;
    }

}

 3. 打印结果

name=carpool.pool.ActiveConnections, value=0  //活跃的连接数
name=carpool.pool.IdleConnections, value=5    //空闲连接数
name=carpool.pool.MaxConnections, value=50    //最大连接数
name=carpool.pool.MinConnections, value=5     //最小连接数
name=carpool.pool.PendingConnections, value=0  //正在等待连接的线程数量。若大于0，则可适当调整最大连接数，即maxPoolSize
name=carpool.pool.TotalConnections, value=5    //总连接数，=ActiveConnections+IdleConnections
//创建连接时间，单位(ms)
name=carpool.pool.ConnectionCreation, count=64, min=20, max=42, mean=25.239365469394595, stddev=4.144925017857287, median=25.0, p75=27.0, p95=35.0, p98=41.0, p99=41.0, p999=41.0
//连接从池中取出到返回的时间，单位(ms)
name=carpool.pool.Usage, count=12, min=1, max=161, mean=16.40175308587677, stddev=37.07048983720169, median=5.0, p75=7.0, p95=90.0, p98=161.0, p99=161.0, p999=161.0
//连接超时比率
name=carpool.pool.ConnectionTimeoutRate, count=0, mean_rate=0.0, m1=0.0, m5=0.0, m15=0.0, rate_unit=events/second
//连接等待时间，单位(ms)
name=carpool.pool.Wait, count=12, min=0.0216, max=3.9868, mean=2.4306451410980117, stddev=1.1203468694566339, median=1.9914, p75=3.8614, p95=3.9868, p98=3.9868, p99=3.9868, p999=3.9868, mean_rate=0.016386862556643685, m1=6.626702430113693E-6, m5=0.05700412699378474, m15=0.2729751908786262, rate_unit=events/second, duration_unit=milliseconds