脚本如何监控连接池运行状态

本文目录导读：

1. 通用监控脚本（以Druid为例）
2. Java Spring Boot监控脚本
3. Shell脚本监控
4. Prometheus指标暴露
5. 关键监控指标
6. 配置示例 (application.yml)
7. 告警通知集成
8. 注意事项

通用监控脚本（以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()

关键监控指标

配置示例 (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)

注意事项

1. 性能影响：监控频率不要太高，建议30-60秒一次
2. 数据持久化：记录历史数据用于趋势分析
3. 分级告警：不同级别采用不同的通知方式
4. 自动恢复：遇到问题尝试自动重置连接池
5. 安全考虑：监控接口需要适当的安全认证

选择适合你技术栈的方案,建议在开发环境充分测试后再部署到生产环境。