HikariCP是一个高性能的JDBC连接池组件,它是基于BoneCP进行改进和优化的开源产品。也是SpringBoot项目自带的连接池。
但是我们项目使用过程经常遇到两个重大问题:
- 连接池耗尽.
一般是慢SQL或连接不释放引起,解决排查代码。 - 连接池被清0.
一般是应用程序与数据库之间网络问题,也有可能是DB问题,无法新建连接,导致池中连接数一直下降到0.
出现以上两个问题都需要手动重启应用程序,非常影响线上项目。需要在应用程序中能够自动或手动重启数据源。(重启数据源一般比重启应用程序快多了)。
新建一个代理数据源对象DataSourceCopy 继承 HikariDataSource
public class DataSourceCopy extends HikariDataSource implements Closeable {
private volatile DataSource ds;
private String driver;
public static DataSourceCopy copy(DataSource ds, String driver) {
return new DataSourceCopy(ds, driver);
}
public DataSourceCopy(DataSource ds, String driver) {
this.ds = ds;
this.driver = driver;
}
public String getDriver() {
return this.driver;
}
public DataSource getRaw() {
return this.ds;
}
public PrintWriter getLogWriter() throws SQLException {
return this.ds.getLogWriter();
}
public void setLogWriter(PrintWriter out) throws SQLException {
this.ds.setLogWriter(out);
}
public void setLoginTimeout(int seconds) throws SQLException {
this.ds.setLoginTimeout(seconds);
}
public int getLoginTimeout() throws SQLException {
return this.ds.getLoginTimeout();
}
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return this.ds.getParentLogger();
}
public <T> T unwrap(Class<T> iface) throws SQLException {
return this.ds.unwrap(iface);
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return this.ds.isWrapperFor(iface);
}
public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}
public Connection getConnection(String username, String password) throws SQLException {
return this.ds.getConnection(username, password);
}
public void close() {
if (this.ds instanceof AutoCloseable) {
IoUtil.close((AutoCloseable) this.ds);
}
}
@Override
public void setJdbcUrl(String jdbcUrl) {
super.setJdbcUrl(jdbcUrl);
((HikariDataSource)ds).setJdbcUrl(jdbcUrl);
}
@Override
public void setUsername(String username) {
super.setUsername(username);
((HikariDataSource)ds).setUsername(username);
}
@Override
public void setPassword(String password) {
super.setPassword(password);
((HikariDataSource)ds).setPassword(password);
}
public synchronized void restartDataSource() {
close();
this.ds = null;
this.ds = new HikariDataSource(this);
}
}
其中调用restartDataSource()方法就可以重启数据源
配置数据源
@Primary
@Bean(name = "adminDataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
DataSource build = DataSourceBuilder.create()
.type(HikariDataSource.class)
.build();
return new DataSourceCopy(build, "spring.datasource");
//return build;
}
//---------------------------------------------------------------------------------------------
针对第二个问题连接池被清 0 原因分析:
HikariCP专门启用一个线程(HouseKeeper)每隔几秒轮询补充连接池的数量,那么如果这个线程被卡住那么连接池很快被耗尽。
项目中HouseKeeper线程在创建数据库连接时,如果应用程序与数据库网络之间有丢包问题、数据库出现短暂问题就会造成该线程一直处于IO等待中。默认情况下没有配置JDBC的 readTimeOut 参数时间,那么这个线程会一直等待。这时只有重启应用才能解决。
经常遇到一个现象DB宕机了,DBA重启数据库之后,连接池有时不会自动恢复,只能重启。
源码:
HikariPool 创建新连接,默认并没有线程超等待时间
private Connection newConnection() throws Exception {
long start = ClockSource.currentTime();
Connection connection = null;
Connection var6;
try {
String username = this.config.getUsername();
String password = this.config.getPassword();
connection = username == null ? this.dataSource.getConnection() : this.dataSource.getConnection(username, password);
if (connection == null) {
throw new SQLTransientConnectionException("DataSource returned null unexpectedly");
}
this.setupConnection(connection);
this.lastConnectionFailure.set((Object)null);
var6 = connection;
} catch (Exception var10) {
if (connection != null) {
this.quietlyCloseConnection(connection, "(Failed to create/setup connection)");
} else if (this.getLastConnectionFailure() == null) {
this.logger.debug("{} - Failed to create/setup connection: {}", this.poolName, var10.getMessage());
}
this.lastConnectionFailure.set(var10);
throw var10;
} finally {
if (this.metricsTracker != null) {
this.metricsTracker.recordConnectionCreated(ClockSource.elapsedMillis(start));
}
}
return var6;
}
在没有设置JDBC的readTimeOut 时间HouseKeeper会一直等待阻塞
设置ReadTimeout 时间
datasource:
data-source-properties:
oracle.net.CONNECT_TIMEOUT: 30000
#oracle.jdbc.ReadTimeout: 30000