本次用的数据库连接池是HikariCP，hikari源自日语，是光的意思。HikariCP具有快速，简单，可靠的特点。
如下是官方给出的JMH基准性能测试的图
目前springboot2默认采用的数据库连接池也是HikariCP
这次主要对HikariCP的五个参数做一次实践验证
- maximumPoolSize
- minimumIdle
- idleTimeout
- maxLifetime
- connectionTimeout
官方文档也对这五个参数给了一些说明解释
- maximumPoolSize
This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out. Please read about pool sizing. Default: 10
- minimumIdle
This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize
- idleTimeout
This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes)
- maxLifetime
This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)
- connectionTimeout
This property controls the maximum number of milliseconds that a client (that‘s you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds)
先来验证一下对于maximumPoolSize的说法，最大的数据库连接数量，包括空闲和使用中的，当有新的连接请求而占用连接已达到maximumPoolSize数量时，调用getConnection()获取连接就会阻塞，直至达到connectionTimeout超时时间
首先创建一张表account，表结构定义如下：
CREATE TABLE `account` (
`id` varchar(36) NOT NULL COMMENT ‘账号ID‘,
`name` varchar(255) DEFAULT NULL COMMENT ‘账户名称‘,
`phone` char(11) DEFAULT NULL COMMENT ‘手机号码‘,
`balance` bigint(64) DEFAULT NULL COMMENT ‘账户余额‘,
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间‘,
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间‘,
`version` int(11) unsigned NOT NULL DEFAULT ‘0‘ COMMENT ‘变更版本号‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘账户表‘;
再这张表里写入600万条数据，再写一条慢SQL，让它执行时可以占用连接一段足够的时间
然后配置yaml文件
spring:
datasource:
jdbc-url: jdbc:mysql://***
driver-class-name: com.mysql.cj.jdbc.Driver
username: ***
password: ***
type: com.zaxxer.hikari.HikariDataSource
maximum-pool-size: 3
minimum-idle: 3
idle-timeout: 240000
max-lifetime: 240000
connection-timeout: 5000
DataSourceConfig.java
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
编写测试类
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.concurrent.*;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
public class DataSourcePoolServiceTest {
private static final ExecutorService executor = Executors.newCachedThreadPool();
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void poolTest() {
long start = System.currentTimeMillis();
int times = 5;
CountDownLatch countDownLatch = new CountDownLatch(times);
for (int i = 0; i < times; i++) {
final int iFin = i;
executor.execute(() -> {
try {
long startTemp = System.currentTimeMillis();
jdbcTemplate.execute("select * from account where name like ‘%KQYtaXIytz%‘ order by balance desc");
System.out.println("task: " + iFin + " cost: " + (System.currentTimeMillis() - startTemp) + "ms");
} finally {
countDownLatch.countDown();
}
});
}
try {
countDownLatch.await();
} catch (InterruptedException ignore) {}
System.out.println("total cost: " + (System.currentTimeMillis() - start) + "ms");
}
}
执行结果如下
Exception in thread "pool-1-thread-1" Exception in thread "pool-1-thread-3" 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 5002ms.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
at com.tj.maplu.service.DataSourcePoolServiceTest.lambda$poolTest$0(DataSourcePoolServiceTest.java:34)
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)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 5002ms.
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)
... 6 more
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 5002ms.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
at com.tj.maplu.service.DataSourcePoolServiceTest.lambda$poolTest$0(DataSourcePoolServiceTest.java:34)
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)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 5002ms.
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)
... 6 more
task: 4 cost: 22297ms
task: 1 cost: 22318ms
task: 3 cost: 22318ms
total cost: 22319ms
五个并发查询的任务，只有三个执行成功，另外两个执行超时，connectionTimeout超时时间设置的5000ms也生效了，也就验证了当连接池占用达到最大连接数时，获取新的连接就会阻塞直至超时。
MySQL监控如下图，最大并发连接数只有3，配置maximum-pool-size= 3生效了
对于minimumIdle的说法，当空闲连接数小于minimumIdle，并且总连接数小于maximumPoolSize时，会自动补充新连接，官方建议出于性能考虑，建议minimumIdle设定与maximumPoolSize一致，变成一个固定连接数的连接池。
idleTimeout表示超出minimumIdle的空闲连接被释放的超时时间
修改配置yaml文件
spring:
datasource:
jdbc-url: jdbc:mysql://***
driver-class-name: com.mysql.cj.jdbc.Driver
username: ***
password: ***
type: com.zaxxer.hikari.HikariDataSource
maximum-pool-size: 20
minimum-idle: 3
idle-timeout: 5000
max-lifetime: 5000
connection-timeout: 5000
设置times=10，运行上述测试类
MySQL监控如下图，10min后数据库连接变成3，由此可见，minimum-idle=3配置生效，维持最小空闲连接数3，idle-timeout=5000ms没有生效，由于小于最小设定值，变成默认值10min
修改配置yaml文件
spring:
datasource:
jdbc-url: jdbc:mysql://***
driver-class-name: com.mysql.cj.jdbc.Driver
username: ***
password: ***
type: com.zaxxer.hikari.HikariDataSource
maximum-pool-size: 20
minimum-idle: 3
idle-timeout: 10000
max-lifetime: 30000
connection-timeout: 5000
MySQL监控如下图，10s后数据库连接变成3，idle-timeout配置生效