监控数据库连接池的脚本如何写

wen 实用脚本 2026-07-10 188

本文目录导读：

监控数据库连接池的脚本如何写

HikariCP 连接池监控脚本（Spring Boot）
Shell脚本监控MySQL连接池
Python脚本监控通用数据库连接池
Prometheus + Grafana 监控集成
配置连接池监控的 application.yml
使用建议

我来为您提供几个不同场景下的数据库连接池监控脚本。

HikariCP 连接池监控脚本（Spring Boot）

@Component
public class HikariPoolMonitor {
    private static final Logger log = LoggerFactory.getLogger(HikariPoolMonitor.class);
    @Autowired
    private DataSource dataSource;
    @Scheduled(fixedDelay = 60000) // 每分钟执行一次
    public void monitorConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
            HikariPoolMXBean poolMXBean = hikariDataSource.getHikariPoolMXBean();
            // 获取连接池状态
            int activeConnections = poolMXBean.getActiveConnections();
            int idleConnections = poolMXBean.getIdleConnections();
            int totalConnections = poolMXBean.getTotalConnections();
            int threadsAwaitingConnection = poolMXBean.getThreadsAwaitingConnection();
            log.info("===== HikariCP Connection Pool Status =====");
            log.info("Total Connections: {}", totalConnections);
            log.info("Active Connections: {}", activeConnections);
            log.info("Idle Connections: {}", idleConnections);
            log.info("Threads Awaiting: {}", threadsAwaitingConnection);
            // 告警检查
            if (activeConnections > totalConnections * 0.8) {
                log.warn("Connection pool usage exceeded 80%!");
            }
            if (threadsAwaitingConnection > 10) {
                log.error("Too many threads waiting for connections!");
            }
        }
    }
}

Shell脚本监控MySQL连接池

#!/bin/bash
# MySQL连接池监控脚本
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASS="your_password"
THRESHOLD=80  # 告警阈值百分比
# 获取MySQL连接信息
function get_connection_pool_status() {
    mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS -e "
    SELECT 
        VARIABLE_NAME,
        VARIABLE_VALUE
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME IN (
        'Threads_connected',
        'Threads_running',
        'Max_used_connections',
        'Connections'
    );
    "
}
# 获取当前配置
function get_connection_config() {
    mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS -e "
    SELECT 
        VARIABLE_NAME,
        VARIABLE_VALUE
    FROM performance_schema.global_variables
    WHERE VARIABLE_NAME IN (
        'max_connections',
        'thread_cache_size',
        'connect_timeout'
    );
    "
}
# 监控主函数
function monitor_connections() {
    echo "=== MySQL Connection Pool Monitor ==="
    echo "Time: $(date '+%Y-%m-%d %H:%M:%S')"
    echo ""
    # 获取连接数
    local max_connections=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS -N -e "SHOW VARIABLES LIKE 'max_connections'" 2>/dev/null | awk '{print $2}')
    local current_connections=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS -N -e "SHOW STATUS LIKE 'Threads_connected'" 2>/dev/null | awk '{print $2}')
    local active_connections=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS -N -e "SHOW STATUS LIKE 'Threads_running'" 2>/dev/null | awk '{print $2}')
    if [ -z "$max_connections" ]; then
        echo "Error: Unable to connect to MySQL"
        exit 1
    fi
    # 计算使用率
    local usage_percent=$((current_connections * 100 / max_connections))
    echo "Max Connections: $max_connections"
    echo "Current Connections: $current_connections"
    echo "Active Connections: $active_connections"
    echo "Usage: $usage_percent%"
    # 告警逻辑
    if [ $usage_percent -gt $THRESHOLD ]; then
        echo "WARNING: Connection pool usage exceeded ${THRESHOLD}%!"
        echo "Alert sent to admin"
        # 发送告警邮件或消息
        # mail -s "MySQL Connection Pool Alert" admin@example.com <<< "Connection usage: $usage_percent%"
    fi
    # 记录到日志
    echo "$(date '+%Y-%m-%d %H:%M:%S'),$max_connections,$current_connections,$active_connections,$usage_percent" >> /var/log/mysql_pool_monitor.csv
}
# 持续监控
while true; do
    clear
    monitor_connections
    sleep 5  # 每5秒刷新一次
done

Python脚本监控通用数据库连接池

#!/usr/bin/env python3
import psutil
import time
import logging
import smtplib
from datetime import datetime
from email.mime.text import MIMEText
import mysql.connector
from mysql.connector import pooling
# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('db_pool_monitor.log'),
        logging.StreamHandler()
    ]
)
class DatabasePoolMonitor:
    def __init__(self, db_config, pool_name="mypool"):
        self.db_config = db_config
        self.pool_name = pool_name
        self.alert_threshold = 80  # 告警阈值
    def create_connection_pool(self):
        """创建连接池"""
        try:
            pool = pooling.MySQLConnectionPool(
                pool_name=self.pool_name,
                pool_size=10,
                pool_reset_session=True,
                **self.db_config
            )
            return pool
        except Exception as e:
            logging.error(f"Failed to create connection pool: {e}")
            return None
    def get_pool_status(self):
        """获取连接池状态"""
        try:
            # 获取数据库连接状态
            conn = mysql.connector.connect(**self.db_config)
            cursor = conn.cursor()
            # 获取连接信息
            cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
            threads_connected = cursor.fetchone()[1]
            cursor.execute("SHOW STATUS LIKE 'Threads_running'")
            threads_running = cursor.fetchone()[1]
            cursor.execute("SHOW VARIABLES LIKE 'max_connections'")
            max_connections = cursor.fetchone()[1]
            cursor.close()
            conn.close()
            return {
                'max_connections': int(max_connections),
                'threads_connected': int(threads_connected),
                'threads_running': int(threads_running),
                'usage_percent': (int(threads_connected) / int(max_connections)) * 100
            }
        except Exception as e:
            logging.error(f"Failed to get pool status: {e}")
            return None
    def monitor_process_pool(self, process_name=None):
        """监控进程级别的连接池"""
        if process_name:
            for proc in psutil.process_iter(['pid', 'name']):
                if process_name in proc.info['name']:
                    logging.info(f"Process {proc.info['name']} (PID: {proc.info['pid']})")
                    # 监控文件描述符（可能包含数据库连接）
                    connections = proc.num_fds()
                    logging.info(f"Open file descriptors: {connections}")
    def send_alert(self, message):
        """发送告警通知"""
        # 这里可以实现邮件、短信、Slack等告警方式
        logging.warning(f"ALERT: {message}")
        # 示例：发送邮件告警
        # msg = MIMEText(message)
        # msg['Subject'] = 'Database Pool Alert'
        # msg['From'] = 'monitor@example.com'
        # msg['To'] = 'admin@example.com'
        # 
        # with smtplib.SMTP('smtp.example.com') as server:
        #     server.send_message(msg)
    def run_monitoring(self, interval=30):
        """运行监控"""
        logging.info("Starting database pool monitoring...")
        while True:
            status = self.get_pool_status()
            if status:
                current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                logging.info(f"=== Pool Status at {current_time} ===")
                logging.info(f"Max Connections: {status['max_connections']}")
                logging.info(f"Connected: {status['threads_connected']}")
                logging.info(f"Active: {status['threads_running']}")
                logging.info(f"Usage: {status['usage_percent']:.2f}%")
                # 检查是否需要告警
                if status['usage_percent'] > self.alert_threshold:
                    self.send_alert(
                        f"Connection pool usage is {status['usage_percent']:.2f}% "
                        f"(Threshold: {self.alert_threshold}%)"
                    )
                # 监控特定进程
                self.monitor_process_pool('mysqld')
                # 检查是否有太多等待的连接
                if status['threads_connected'] > status['max_connections'] * 0.9:
                    logging.warning("High connection usage detected!")
            else:
                logging.error("Failed to get pool status")
            time.sleep(interval)
# 使用示例
if __name__ == "__main__":
    db_config = {
        'host': 'localhost',
        'port': 3306,
        'user': 'monitor_user',
        'password': 'monitor_pass',
        'database': 'mysql'
    }
    monitor = DatabasePoolMonitor(db_config)
    monitor.run_monitoring(interval=30)  # 每30秒检查一次

Prometheus + Grafana 监控集成

# prometheus.yml 配置
scrape_configs:
  - job_name: 'mysql_connection_pool'
    static_configs:
      - targets: ['localhost:9104']  # MySQL Exporter
    metrics_path: /metrics
    params:
      collect[]:
        - global_status
        - global_variables
        - performance_schema

#!/bin/bash
# 使用mysqld_exporter导出指标
# 启动MySQL Exporter
docker run -d \
  --name mysql_exporter \
  -e DATA_SOURCE_NAME="user:password@(localhost:3306)/" \
  -p 9104:9104 \
  prom/mysqld-exporter
# 检查连接池指标
curl -s http://localhost:9104/metrics | grep -E "mysql_global_status_threads|mysql_global_variables_max_connections"

配置连接池监控的 application.yml

spring:
  datasource:
    hikari:
      pool-name: MyPool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
# Actuator监控配置
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,info
  metrics:
    export:
      prometheus:
        enabled: true

使用建议

选择合适的监控工具：
- 小型应用：使用内置监控或简单脚本
- 中型应用：使用JMX或Actuator
- 大型分布式：使用Prometheus + Grafana
设置合理的告警阈值：
- 连接使用率 > 80%：警告
- 连接使用率 > 90%：严重告警
- 连接泄漏检测阈值
定期检查和优化：
- 监控连接使用趋势
- 调整连接池大小
- 优化慢查询
安全考虑：
- 使用专用监控账号
- 加密敏感信息
- 限制监控访问权限

根据您的具体需求选择合适的监控方案,并可根据实际情况进行调整。

本文地址： https://www.bmjup.com/post/18965.html