本文目录导读：
通用监控脚本（以Druid为例）
#!/usr/bin/env python3
import requests
import json
import time
from datetime import datetime
class ConnectionPoolMonitor:
def __init__(self, monitor_url, interval=60):
self.monitor_url = monitor_url
self.interval = interval
def get_pool_status(self):
"""获取连接池状态"""
try:
response = requests.get(self.monitor_url, timeout=5)
return response.json()
except Exception as e:
print(f"获取连接池状态失败: {e}")
return None
def analyze_status(self, status):
"""分析连接池状态"""
if not status:
return []
alerts = []
# 检查关键指标
metrics = {
'active_count': status.get('activeCount', 0),
'pooling_count': status.get('poolingCount', 0),
'max_active': status.get('maxActive', 20),
'wait_count': status.get('waitCount', 0),
'initial_size': status.get('initialSize', 5),
'min_idle': status.get('minIdle', 5),
'max_idle': status.get('maxIdle', 10),
}
# 规则判定
if metrics['active_count'] > metrics['max_active'] * 0.8:
alerts.append({
'level': 'WARNING',
'message': f"连接使用率超过80%, 当前活跃: {metrics['active_count']}"
})
if metrics['wait_count'] > 10:
alerts.append({
'level': 'CRITICAL',
'message': f"等待连接数超过10, 当前等待: {metrics['wait_count']}"
})
if metrics['active_count'] == metrics['max_active']:
alerts.append({
'level': 'CRITICAL',
'message': "连接池已满，可能导致请求阻塞"
})
return alerts
def log_status(self, status, alerts):
"""记录状态和告警"""
timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# 记录到文件
with open('pool_monitor.log', 'a') as f:
f.write(f"[{timestamp}] Active: {status.get('activeCount')}, "
f"Idle: {status.get('poolingCount')}, "
f"Wait: {status.get('waitCount')}\n")
# 告警输出
if alerts:
for alert in alerts:
log_level = alert['level']
message = alert['message']
print(f"[{timestamp}] [{log_level}] {message}")
def run(self):
"""运行监控循环"""
print(f"开始监控连接池，间隔{self.interval}秒")
while True:
status = self.get_pool_status()
if status:
alerts = self.analyze_status(status)
self.log_status(status, alerts)
else:
print("无法获取连接池状态")
time.sleep(self.interval)
# 使用示例
if __name__ == "__main__":
# Druid监控URL示例
monitor_url = "http://localhost:8080/druid/datasource.json"
monitor = ConnectionPoolMonitor(monitor_url, interval=30)
try:
monitor.run()
except KeyboardInterrupt:
print("监控已停止")
Java Spring Boot监控脚本
@Component
public class DataSourceMonitor {
private static final Logger logger = LoggerFactory.getLogger(DataSourceMonitor.class);
@Autowired
private DataSource dataSource;
@Scheduled(fixedRate = 30000) // 每30秒执行一次
public void monitorDataSource() {
if (dataSource instanceof DruidDataSource) {
DruidDataSource druidDataSource = (DruidDataSource) dataSource;
Map<String, Object> metrics = new HashMap<>();
metrics.put("activeCount", druidDataSource.getActiveCount());
metrics.put("poolingCount", druidDataSource.getPoolingCount());
metrics.put("maxActive", druidDataSource.getMaxActive());
metrics.put("waitCount", druidDataSource.getWaitCount());
metrics.put("createCount", druidDataSource.getCreateCount());
metrics.put("destroyCount", druidDataSource.getDestroyCount());
metrics.put("connectCount", druidDataSource.getConnectCount());
metrics.put("closeCount", druidDataSource.getCloseCount());
// 发送到监控系统
sendToMonitoringSystem(metrics);
// 告警逻辑
if (druidDataSource.getActiveCount() > druidDataSource.getMaxActive() * 0.8) {
logger.warn("连接池使用率超过80%");
}
}
}
}
Shell脚本监控
#!/bin/bash
# 数据库连接池监控脚本
POOL_NAME="myapp_datasource"
MONITOR_INTERVAL=60
ALERT_THRESHOLD=80
while true; do
# 获取连接池状态（通过JMX或API）
if command -v jstat &> /dev/null; then
# 通过JMX获取数据
ACTIVE_COUNT=$(curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active | jq '.measurements[0].value')
IDLE_COUNT=$(curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.idle | jq '.measurements[0].value')
MAX_COUNT=$(curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.max | jq '.measurements[0].value')
# 计算使用率
USAGE_PERCENT=$(echo "scale=2; $ACTIVE_COUNT / $MAX_COUNT * 100" | bc)
# 记录监控数据
TIMESTAMP=$(date '+%Y-%m-%d %H:%M:%S')
echo "$TIMESTAMP | Active: $ACTIVE_COUNT | Idle: $IDLE_COUNT | Usage: ${USAGE_PERCENT}%" >> pool_monitor.log
# 告警检查
if (( $(echo "$USAGE_PERCENT > $ALERT_THRESHOLD" | bc -l) )); then
echo "WARNING: 连接池使用率超过${ALERT_THRESHOLD}%" | mail -s "连接池告警" admin@example.com
fi
fi
sleep $MONITOR_INTERVAL
done
Prometheus指标暴露
# prometheus_client 暴露连接池指标
from prometheus_client import start_http_server, Gauge
import time
# 定义指标
pool_active = Gauge('db_pool_active_connections', 'Active connections')
pool_idle = Gauge('db_pool_idle_connections', 'Idle connections')
pool_wait = Gauge('db_pool_wait_connections', 'Waiting connections')
pool_usage = Gauge('db_pool_usage_percent', 'Pool usage percentage')
def collect_metrics():
while True:
# 获取连接池状态
status = get_pool_status()
pool_active.set(status['activeCount'])
pool_idle.set(status['poolingCount'])
pool_wait.set(status['waitCount'])
usage = (status['activeCount'] / status['maxActive']) * 100
pool_usage.set(usage)
time.sleep(15)
if __name__ == '__main__':
start_http_server(8000)
collect_metrics()
关键监控指标
| 指标 | 说明 | 告警阈值 |
|---|---|---|
| Active | 活跃连接数 | > maxActive * 80% |
| Idle | 空闲连接数 | < minIdle |
| Wait | 等待连接数 | > 10 |
| Create | 创建连接数 | 异常增长 |
| Destroy | 销毁连接数 | 异常增长 |
| MaxActive | 最大连接数 | |
| InitialSize | 初始连接数 |
配置示例 (application.yml)
spring:
datasource:
druid:
# 监控配置
web-stat-filter:
enabled: true
url-pattern: /*
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: false
login-username: admin
login-password: admin
# 慢SQL监控
filter:
stat:
log-slow-sql: true
slow-sql-millis: 2000
告警通知集成
def send_alert(message, level='WARNING'):
"""集成多种告警方式"""
# 邮件告警
send_email_alert(message, level)
# 企业微信/钉钉
send_webhook_alert(message)
# 短信告警（重要级别）
if level == 'CRITICAL':
send_sms_alert(message)
# 记录到告警系统
log_to_alert_center(message, level)
注意事项
- 性能影响：监控频率不要太高，建议30-60秒一次
- 数据持久化：记录历史数据用于趋势分析
- 分级告警：不同级别采用不同的通知方式
- 自动恢复：遇到问题尝试自动重置连接池
- 安全考虑：监控接口需要适当的安全认证
选择适合你技术栈的方案,建议在开发环境充分测试后再部署到生产环境。
