Spring Boot 数据库连接池 HikariPool 优化

小渣渣

Spring-Boot-2.0.0-M1 版本将默认的数据库连接池从 tomcat jdbc pool 改为了hikari，运行 spring boot 应用的时候，控制台会输出如下：

2021-11-25 14:48:09.429  INFO 22236 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-11-25 14:48:09.813  INFO 22236 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.

Hikari默认配置的默认值如下：

{"connection-timeout":30000,"maximum-pool-size":10,"max-lifetime":1800000,"minimum-idle":10,"validation-timeout":5000,"idle-timeout":600000}

name	构造器默认值	默认配置validate之后的值	validate重置
minIdle	-1	10	minIdle<0或者minIdle>maxPoolSize,则被重置为maxPoolSize
maxPoolSize	-1	10	如果maxPoolSize小于1，则会被重置。当minIdle<=0被重置为DEFAULT_POOL_SIZE则为10;如果minIdle>0则重置为minIdle的值
maxLifetime	MINUTES.toMillis(30) = 1800000	1800000	如果不等于0且小于30秒则会被重置回30分钟
connectionTimeout	SECONDS.toMillis(30) = 30000	30000	如果小于250毫秒，则被重置回30秒
validationTimeout	SECONDS.toMillis(5) = 5000	5000	如果小于250毫秒，则会被重置回5秒
loginTimeout	10	30	Math.max(1, (int) MILLISECONDS.toSeconds(500L + connectionTimeout))，为connectionTimeout+500ms转为秒数取整 与 1 取最大者
idleTimeout	MINUTES.toMillis(10) = 600000	600000	如果idleTimeout+1秒>maxLifetime 且 maxLifetime>0，则会被重置为0；如果idleTimeout!=0且小于10秒，则会被重置为10秒
leakDetectionThreshold	0	0	如果大于0且不是单元测试，则进一步判断：(leakDetectionThreshold < SECONDS.toMillis(2) or (leakDetectionThreshold > maxLifetime && maxLifetime > 0)，会被重置为0 . 即如果要生效则必须>0，而且不能小于2秒，而且当maxLifetime > 0时不能大于maxLifetime
initializationFailTimeout	1	1	-
isAutoCommit	true	true	-
isReadOnly	false	fasle	-
isAllowPoolSuspension	false	false	-
isIsolateInternalQueries	false	false	-
isRegisterMbeans	false	false	-
sealed	false	true	运行启动后这个标志为true，表示不再运行修改
poolName	null	HikariPool-1	-
catalog	null	null	-
connectionInitSql	null	null	-
connectionTestQuery	null	null	-
dataSourceClassName	null	null	-
schema	null	null	-
transactionIsolationName	null	null	-
dataSource	null	null	-
dataSourceProperties	{}	{}	-
threadFactory	null	null	-
scheduledExecutor	null	null	-
metricsTrackerFactory	null	null	-
metricRegistry	null	null	-
healthCheckRegistry	null	null	-
healthCheckProperties	{}	{}	-

在进行压测的时候，应用可能会报错如下：

Caused by: 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.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
        at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80)
        at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67)
        at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:337)
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
        at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy224.query(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
        ... 114 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
        at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:695)
        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.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
        ... 126 more

错误：Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.

Controller 输出 HikariDataSource 配置接口代码如下：

登录可见。

优化方案，增加最大线程数和增加空闲保持的连接数，修改 application.yml 配置，如下：

登录可见。

（完）

terolin126

学习学习