连接池架构：HikariCP与Druid
连接池架构：HikariCP与Druid
HikariCP配置与调优
HikariCP是高性能的JDBC连接池，以轻量和高速著称。
// HikariCP配置
@Configuration
public class HikariCPConfig {
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariDataSource dataSource() {
HikariConfig config = new HikariConfig();
// 基础配置
config.setPoolName("MyAppPool");
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
// 连接池大小
config.setMinimumIdle(10);
config.setMaximumPoolSize(100);
// 连接超时
config.setConnectionTimeout(30000); // 30秒
config.setIdleTimeout(600000); // 10分钟
config.setMaxLifetime(1800000); // 30分钟
// 连接验证
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(5000);
//泄漏检测
config.setLeakDetectionThreshold(60000); // 60秒
// 数据源属性
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("useServerPrepStmts", "true");
config.addDataSourceProperty("useLocalSessionState", "true");
config.addDataSourceProperty("rewriteBatchedStatements", "true");
config.addDataSourceProperty("cacheResultSetMetadata", "true");
config.addDataSourceProperty("cacheServerConfiguration", "true");
config.addDataSourceProperty("elideSetAutoCommits", "true");
config.addDataSourceProperty("maintainTimeStats", "false");
return new HikariDataSource(config);
}
}
// HikariCP监控
@Component
public class HikariCPMonitor {
private final HikariDataSource dataSource;
private final MetricsExporter metrics;
@Scheduled(fixedRate = 5000)
public void monitor() {
HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
if (poolMXBean != null) {
// 连接池状态
int activeConnections = poolMXBean.getActiveConnections();
int idleConnections = poolMXBean.getIdleConnections();
int totalConnections = poolMXBean.getTotalConnections();
int threadsAwaiting = poolMXBean.getThreadsAwaitingConnection();
// 导出指标
metrics.gauge("hikari.active", activeConnections);
metrics.gauge("hikari.idle", idleConnections);
metrics.gauge("hikari.total", totalConnections);
metrics.gauge("hikari.waiting", threadsAwaiting);
// 计算使用率
double utilization = (double) activeConnections /
dataSource.getMaximumPoolSize() * 100;
metrics.gauge("hikari.utilization", utilization);
// 告警检测
checkAlerts(activeConnections, idleConnections, threadsAwaiting, utilization);
}
}
private void checkAlerts(int active, int idle, int waiting, double utilization) {
if (waiting > 0) {
alertService.send(new Alert(
"连接池等待",
"有线程在等待获取连接，等待数: " + waiting,
AlertLevel.WARNING
));
}
if (utilization > 90) {
alertService.send(new Alert(
"连接池使用率过高",
"当前使用率: " + utilization + "%",
AlertLevel.CRITICAL
));
}
if (idle == 0 && active == dataSource.getMaximumPoolSize()) {
alertService.send(new Alert(
"连接池已满",
"所有连接都在使用中",
AlertLevel.CRITICAL
));
}
}
}
Druid配置与监控
Druid是阿里巴巴开源的数据库连接池，提供强大的监控和SQL分析功能。
// Druid配置
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties("spring.datasource.druid")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
// 基础配置
dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUsername("user");
dataSource.setPassword("password");
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
// 连接池配置
dataSource.setInitialSize(10);
dataSource.setMinIdle(10);
dataSource.setMaxActive(100);
dataSource.setMaxWait(60000);
// 连接检测
dataSource.setValidationQuery("SELECT 1");
dataSource.setTestOnBorrow(true);
dataSource.setTestWhileIdle(true);
dataSource.setTimeBetweenEvictionRunsMillis(60000);
dataSource.setMinEvictableIdleTimeMillis(300000);
// PSCache
dataSource.setPoolPreparedStatements(true);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
// 过滤器
dataSource.setFilters("stat,wall");
// 监控配置
StatViewServlet servlet = new StatViewServlet();
servlet.setLoginUsername("admin");
servlet.setLoginPassword("admin");
return dataSource;
}
}
// Druid监控
@Component
public class DruidMonitor {
private final DruidDataSource dataSource;
public DruidStats getStats() {
return DruidStats.builder()
.activeCount(dataSource.getActiveCount())
.PoolingCount(dataSource.getPoolingCount())
.PoolingPeak(dataSource.getPoolingPeakCount())
.CreateCount(dataSource.getCreateCount())
.DestroyCount(dataSource.getDestroyCount())
.WaitThreadCount(dataSource.getWaitThreadCount())
.LogicConnectCount(dataSource.getConnectErrorCount())
.Build();
}
// SQL监控
public List<SQLStat> getTopSQL(int limit) {
List<JdbcSqlStat> sqlStats = new ArrayList<>(dataSource.getStatData().values());
return sqlStats.stream()
.sorted(Comparator.comparingLong(JdbcSqlStat::getExecuteCount).reversed())
.limit(limit)
.map(stat -> SQLStat.builder()
.sql(stat.getSql())
.executeCount(stat.getExecuteCount())
.ExecuteTimeMax(stat.getExecuteMax())
.ExecuteTimeTotal(stat.getExecuteTimeTotal())
.build())
.collect(Collectors.toList());
}
}
连接池调优策略
# 连接池调优配置
connection_pool:
hikari:
# 小型应用（<100并发）
small:
minimum_idle: 5
maximum_pool_size: 20
connection_timeout: 30000
idle_timeout: 600000
max_lifetime: 1800000
# 中型应用（100-500并发）
medium:
minimum_idle: 10
maximum_pool_size: 50
connection_timeout: 30000
idle_timeout: 600000
max_lifetime: 1800000
# 大型应用（500-1000并发）
large:
minimum_idle: 20
maximum_pool_size: 100
connection_timeout: 30000
idle_timeout: 600000
max_lifetime: 1800000
# 超大型应用（>1000并发）
xlarge:
minimum_idle: 30
maximum_pool_size: 200
connection_timeout: 30000
idle_timeout: 600000
max_lifetime: 1800000
druid:
# 监控配置
monitoring:
stat_view_servlet:
enabled: true
url_pattern: "/druid/*"
login_username: "admin"
login_password: "admin"
web_stat_filter:
enabled: true
url_pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
# SQL监控
stat:
enabled: true
slow_sql_millis: 1000
log_slow_sql: true
merge_sql: true
# 防火墙
wall:
enabled: true
config:
select_allow: true
select_into_allow: false
delete_allow: true
update_allow: true
insert_allow: true
drop_table_allow: false
alter_table_allow: false
连接池问题排查
// 连接池问题诊断
@Component
public class ConnectionPoolDiagnostics {
public ConnectionPoolReport diagnose(DataSource dataSource) {
ConnectionPoolReport report = new ConnectionPoolReport();
if (dataSource instanceof HikariDataSource) {
diagnoseHikariCP((HikariDataSource) dataSource, report);
} else if (dataSource instanceof DruidDataSource) {
diagnoseDruid((DruidDataSource) dataSource, report);
}
return report;
}
private void diagnoseHikariCP(HikariDataSource dataSource,
ConnectionPoolReport report) {
HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
// 检查连接泄漏
long leakDetectionThreshold = dataSource.getLeakDetectionThreshold();
if (leakDetectionThreshold > 0) {
report.addIssue("连接泄漏检测已启用，阈值: " +
leakDetectionThreshold + "ms");
}
// 检查连接等待
int threadsAwaiting = poolMXBean.getThreadsAwaitingConnection();
if (threadsAwaiting > 0) {
report.addIssue("有线程在等待连接，可能需要增加连接池大小");
}
// 检查连接使用率
int activeConnections = poolMXBean.getActiveConnections();
int maxConnections = dataSource.getMaximumPoolSize();
double utilization = (double) activeConnections / maxConnections * 100;
if (utilization > 80) {
report.addIssue("连接池使用率过高: " + utilization + "%");
}
}
}
连接池配置需要根据应用的实际负载和特点进行调优，HikariCP适合追求性能的场景，Druid适合需要强大监控功能的场景。