KES 监控与运维自动化实战：性能指标采集、告警体系与智能运维

KES 监控与运维自动化实战：性能指标采集、告警体系与智能运维

前言

数据库系统的稳定运行离不开完善的监控体系和高效的运维管理。随着业务规模的扩大，传统的人工运维方式已经难以应对复杂的监控需求和故障场景。建立自动化的监控告警体系，实现智能运维，成为数据库管理员的必备技能。

本篇内容聚焦KES的监控与运维自动化，详细讲解性能指标采集、告警体系构建、自动化运维脚本以及智能运维实践。全文以实际操作为主，结合大量真实案例。如果你负责数据库运维工作，或者希望提升运维效率，相信这篇内容对你会有帮助。

一、性能指标采集与监控

性能监控是数据库运维的基础。通过持续采集关键性能指标，可以及时发现潜在问题，为性能优化提供数据支撑。

系统级指标监控

-- 查看数据库连接数
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active_connections,
       count(*) FILTER (WHERE state = 'idle') AS idle_connections,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction
FROM sys_stat_activity;

-- 查看数据库大小
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM sys_database
ORDER BY pg_database_size(datname) DESC;

-- 查看表空间使用情况
SELECT 
    spcname,
    pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM sys_tablespace;

查询性能监控

-- 查看慢查询（执行时间超过1秒）
SELECT 
    pid,
    usename,
    now() - query_start AS duration,
    query
FROM sys_stat_activity
WHERE state = 'active'
  AND now() - query_start > INTERVAL '1 second'
ORDER BY duration DESC;

-- 查看锁等待情况
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS wait_duration
FROM sys_stat_activity blocked
JOIN sys_locks l ON blocked.pid = l.pid AND NOT l.granted
JOIN sys_locks granted ON l.locktype = granted.locktype
    AND l.database IS NOT DISTINCT FROM granted.database
    AND l.relation IS NOT DISTINCT FROM granted.relation
    AND granted.granted = true
JOIN sys_stat_activity blocking ON granted.pid = blocking.pid
WHERE blocked.pid != blocking.pid;

-- 查看缓存命中率
SELECT 
    datname,
    blks_read,
    blks_hit,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS hit_ratio
FROM sys_stat_database
WHERE datname = current_database();

资源使用监控

-- 查看表膨胀情况
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS data_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM sys_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM sys_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

二、告警体系构建

完善的告警体系是保障数据库稳定运行的关键。通过设定合理的告警阈值，可以在问题恶化前及时发现并处理。

告警规则设计

-- 创建告警配置表
CREATE TABLE alert_rules (
    rule_id BIGSERIAL PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    threshold_value NUMERIC NOT NULL,
    comparison_operator VARCHAR(10) NOT NULL,
    severity VARCHAR(20) NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT now()
);

-- 初始化告警规则
INSERT INTO alert_rules (rule_name, metric_name, threshold_value, comparison_operator, severity) VALUES
('连接数告警', 'total_connections', 200, '>', 'WARNING'),
('慢查询告警', 'slow_query_duration', 5, '>', 'WARNING'),
('死锁告警', 'deadlock_count', 0, '>', 'CRITICAL'),
('缓存命中率告警', 'cache_hit_ratio', 95, '<', 'WARNING'),
('磁盘空间告警', 'disk_usage_percent', 85, '>', 'WARNING');

告警检查脚本

#!/bin/bash
# check_alerts.sh - 数据库告警检查脚本

# 数据库连接信息
DB_HOST="localhost"
DB_PORT="54321"
DB_NAME="your_db"
DB_USER="kingbase"

# 告警接收人
ALERT_EMAIL="dba@example.com"

# 检查连接数
check_connections() {
    local count=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c \
        "SELECT count(*) FROM sys_stat_activity;")
    
    if [ $count -gt 200 ]; then
        echo "警告：数据库连接数达到 $count，超过阈值200" | \
        mail -s "KES告警：连接数过高" $ALERT_EMAIL
    fi
}

# 检查慢查询
check_slow_queries() {
    local count=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c \
        "SELECT count(*) FROM sys_stat_activity 
         WHERE state = 'active' AND now() - query_start > INTERVAL '5 seconds';")
    
    if [ $count -gt 0 ]; then
        echo "警告：发现 $count 个慢查询（执行时间超过5秒）" | \
        mail -s "KES告警：慢查询" $ALERT_EMAIL
    fi
}

# 检查死锁
check_deadlocks() {
    local count=$(psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c \
        "SELECT count(*) FROM sys_stat_activity 
         WHERE wait_event_type = 'Lock';")
    
    if [ $count -gt 0 ]; then
        echo "严重：检测到 $count 个死锁等待" | \
        mail -s "KES告警：死锁" $ALERT_EMAIL
    fi
}

# 检查磁盘空间
check_disk_space() {
    local usage=$(df -h /data/kingbase | tail -1 | awk '{print $5}' | sed 's/%//')
    
    if [ $usage -gt 85 ]; then
        echo "警告：磁盘使用率达到 ${usage}%，超过阈值85%" | \
        mail -s "KES告警：磁盘空间不足" $ALERT_EMAIL
    fi
}

# 执行所有检查
check_connections
check_slow_queries
check_deadlocks
check_disk_space

echo "告警检查完成：$(date)"

告警通知集成

-- 创建告警历史表
CREATE TABLE alert_history (
    alert_id BIGSERIAL PRIMARY KEY,
    rule_id BIGINT REFERENCES alert_rules(rule_id),
    metric_value NUMERIC NOT NULL,
    alert_time TIMESTAMP DEFAULT now(),
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at TIMESTAMP,
    notes TEXT
);

-- 告警确认函数
CREATE OR REPLACE FUNCTION acknowledge_alert(
    p_alert_id BIGINT,
    p_user VARCHAR,
    p_notes TEXT DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
    UPDATE alert_history
    SET acknowledged = TRUE,
        acknowledged_by = p_user,
        acknowledged_at = now(),
        notes = p_notes
    WHERE alert_id = p_alert_id;
END;
$$ LANGUAGE plpgsql;

三、自动化运维脚本

自动化运维能够显著提升工作效率，减少人为失误。通过编写标准化的运维脚本，可以实现日常运维任务的自动化执行。

自动VACUUM脚本

#!/bin/bash
# auto_vacuum.sh - 自动VACUUM脚本

DB_NAME="your_db"
DB_USER="kingbase"
LOG_FILE="/var/log/kes/vacuum_$(date +%Y%m%d).log"

echo "开始执行VACUUM：$(date)" >> $LOG_FILE

# 获取需要VACUUM的表
psql -d $DB_NAME -U $DB_USER -t -c "
SELECT schemaname || '.' || tablename
FROM sys_stat_user_tables
WHERE n_dead_tup > 10000
   OR (n_dead_tup > 0 AND last_vacuum IS NULL)
   OR last_vacuum < now() - INTERVAL '7 days'
ORDER BY n_dead_tup DESC;" | while read table_name; do
    echo "正在VACUUM表：$table_name" >> $LOG_FILE
    psql -d $DB_NAME -U $DB_USER -c "VACUUM ANALYZE $table_name;" >> $LOG_FILE 2>&1
    echo "完成时间：$(date)" >> $LOG_FILE
    echo "---" >> $LOG_FILE
done

echo "VACUUM执行完成：$(date)" >> $LOG_FILE

自动备份脚本

#!/bin/bash
# auto_backup.sh - 自动备份脚本

BACKUP_DIR="/backup/kes"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="your_db"
DB_USER="kingbase"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行逻辑备份
echo "开始备份：$(date)"
sys_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_DIR/dump_${DB_NAME}_${DATE}.dump

# 压缩备份文件
gzip $BACKUP_DIR/dump_${DB_NAME}_${DATE}.dump

# 验证备份文件
if [ -f $BACKUP_DIR/dump_${DB_NAME}_${DATE}.dump.gz ]; then
    echo "备份成功：dump_${DB_NAME}_${DATE}.dump.gz"
    # 计算文件大小
    SIZE=$(du -sh $BACKUP_DIR/dump_${DB_NAME}_${DATE}.dump.gz | awk '{print $1}')
    echo "文件大小：$SIZE"
else
    echo "备份失败！" | mail -s "KES备份告警" dba@example.com
    exit 1
fi

# 清理过期备份
find $BACKUP_DIR -name "dump_*.dump.gz" -mtime +$RETENTION_DAYS -delete
echo "已清理 $RETENTION_DAYS 天前的备份文件"

echo "备份完成：$(date)"

自动索引优化脚本

#!/bin/bash
# auto_index_optimize.sh - 自动索引优化脚本

DB_NAME="your_db"
DB_USER="kingbase"
LOG_FILE="/var/log/kes/index_optimize_$(date +%Y%m%d).log"

echo "开始索引优化分析：$(date)" >> $LOG_FILE

# 查找未使用的索引
psql -d $DB_NAME -U $DB_USER -t -c "
SELECT schemaname || '.' || indexname
FROM sys_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('sys_catalog', 'pg_catalog')
  AND indexrelid NOT IN (
      SELECT conindid FROM sys_constraint WHERE contype IN ('p', 'u')
  );" | while read index_name; do
    echo "发现未使用索引：$index_name" >> $LOG_FILE
    # 可选：删除未使用索引
    # psql -d $DB_NAME -U $DB_USER -c "DROP INDEX $index_name;" >> $LOG_FILE 2>&1
done

# 查找缺失索引的表
psql -d $DB_NAME -U $DB_USER -t -c "
SELECT schemaname || '.' || relname
FROM sys_stat_user_tables
WHERE seq_scan > 1000
  AND n_live_tup > 10000
  AND schemaname NOT IN ('sys_catalog', 'pg_catalog');" | while read table_name; do
    echo "表 $table_name 可能存在缺失索引（全表扫描次数：1000+）" >> $LOG_FILE
done

echo "索引优化分析完成：$(date)" >> $LOG_FILE

四、智能运维实践

智能运维通过数据分析和自动化决策，进一步提升运维效率。通过建立运维知识库和自动化处理流程，可以实现常见问题的自动诊断和修复。

智能诊断脚本

#!/bin/bash
# smart_diagnosis.sh - 智能诊断脚本

DB_NAME="your_db"
DB_USER="kingbase"
REPORT_FILE="/tmp/diagnosis_report_$(date +%Y%m%d_%H%M%S).txt"

echo "========== KES数据库诊断报告 ==========" > $REPORT_FILE
echo "诊断时间：$(date)" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# 1. 连接数分析
echo "【连接数分析】" >> $REPORT_FILE
psql -d $DB_NAME -U $DB_USER -t -c "
SELECT 
    '总连接数：' || count(*),
    '活跃连接：' || count(*) FILTER (WHERE state = 'active'),
    '空闲连接：' || count(*) FILTER (WHERE state = 'idle'),
    '事务空闲：' || count(*) FILTER (WHERE state = 'idle in transaction')
FROM sys_stat_activity;" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# 2. 性能分析
echo "【性能分析】" >> $REPORT_FILE
psql -d $DB_NAME -U $DB_USER -t -c "
SELECT 
    '缓存命中率：' || round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) || '%',
    '事务提交数：' || xact_commit,
    '事务回滚数：' || xact_rollback
FROM sys_stat_database
WHERE datname = current_database();" >> $REPORT_FILE
echo "" >> $REPORT_FILE

# 3. 锁等待分析
echo "【锁等待分析】" >> $REPORT_FILE
LOCK_COUNT=$(psql -d $DB_NAME -U $DB_USER -t -c "
SELECT count(*) FROM sys_stat_activity WHERE wait_event_type = 'Lock';")
if [ $LOCK_COUNT -gt 0 ]; then
    echo "发现 $LOCK_COUNT 个锁等待" >> $REPORT_FILE
    psql -d $DB_NAME -U $DB_USER -c "
    SELECT blocked.pid, blocked.query, now() - blocked.query_start AS wait_time
    FROM sys_stat_activity blocked
    WHERE wait_event_type = 'Lock'
    ORDER BY wait_time DESC
    LIMIT 5;" >> $REPORT_FILE
else
    echo "无锁等待" >> $REPORT_FILE
fi
echo "" >> $REPORT_FILE

# 4. 表膨胀分析
echo "【表膨胀分析】" >> $REPORT_FILE
psql -d $DB_NAME -U $DB_USER -c "
SELECT 
    schemaname || '.' || tablename AS table_name,
    n_dead_tup AS dead_tuples,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM sys_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;" >> $REPORT_FILE

echo "" >> $REPORT_FILE
echo "========== 诊断完成 ==========" >> $REPORT_FILE

# 发送报告
mail -s "KES数据库诊断报告" dba@example.com < $REPORT_FILE
rm -f $REPORT_FILE

自动化修复流程

-- 创建自动化修复任务表
CREATE TABLE auto_repair_tasks (
    task_id BIGSERIAL PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,
    target_object VARCHAR(200) NOT NULL,
    task_status VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT now(),
    executed_at TIMESTAMP,
    result TEXT,
    executed_by VARCHAR(100)
);

-- 自动VACUUM任务
CREATE OR REPLACE FUNCTION schedule_vacuum_task(p_table_name VARCHAR)
RETURNS VOID AS $$
BEGIN
    INSERT INTO auto_repair_tasks (task_type, target_object)
    VALUES ('VACUUM', p_table_name);
END;
$$ LANGUAGE plpgsql;

-- 执行待处理的VACUUM任务
CREATE OR REPLACE FUNCTION execute_pending_vacuum_tasks()
RETURNS INT AS $$
DECLARE
    v_task RECORD;
    v_count INT := 0;
BEGIN
    FOR v_task IN
        SELECT * FROM auto_repair_tasks
        WHERE task_type = 'VACUUM'
          AND task_status = 'PENDING'
        ORDER BY created_at
        LIMIT 10
    LOOP
        BEGIN
            EXECUTE format('VACUUM ANALYZE %s', v_task.target_object);
            
            UPDATE auto_repair_tasks
            SET task_status = 'COMPLETED',
                executed_at = now(),
                result = 'SUCCESS',
                executed_by = 'auto_repair'
            WHERE task_id = v_task.task_id;
            
            v_count := v_count + 1;
        EXCEPTION WHEN OTHERS THEN
            UPDATE auto_repair_tasks
            SET task_status = 'FAILED',
                executed_at = now(),
                result = SQLERRM,
                executed_by = 'auto_repair'
            WHERE task_id = v_task.task_id;
        END;
    END LOOP;
    
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

运维知识库建设

-- 创建运维知识库表
CREATE TABLE ops_knowledge_base (
    kb_id BIGSERIAL PRIMARY KEY,
    issue_type VARCHAR(100) NOT NULL,
    symptoms TEXT NOT NULL,
    root_cause TEXT,
    solution TEXT NOT NULL,
    prevention TEXT,
    related_metrics TEXT[],
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 初始化知识库
INSERT INTO ops_knowledge_base (issue_type, symptoms, root_cause, solution, prevention, related_metrics) VALUES
('连接数过高', 
 'total_connections > 200, 应用报错连接池耗尽',
 '应用未正确释放连接，或连接池配置过大',
 '1. 检查idle in transaction连接\n2. 设置idle_in_transaction_session_timeout\n3. 优化连接池配置',
 '1. 应用层确保事务及时提交\n2. 设置合理的连接超时\n3. 定期监控连接数',
 ARRAY['total_connections', 'idle_in_transaction_count']),

('慢查询', 
 'query_duration > 5s, 用户反馈响应慢',
 '缺少索引、查询条件不当、数据量过大',
 '1. 分析执行计划\n2. 创建合适索引\n3. 优化查询语句',
 '1. 定期分析慢查询日志\n2. 建立索引优化流程\n3. 代码审查关注SQL性能',
 ARRAY['slow_query_count', 'query_duration']),

('死锁', 
 'deadlock_count > 0, 应用报错死锁',
 '多事务并发更新顺序不一致',
 '1. 统一更新顺序\n2. 缩短事务长度\n3. 使用锁超时',
 '1. 代码审查关注并发逻辑\n2. 建立死锁监控\n3. 定期分析死锁日志',
 ARRAY['deadlock_count', 'lock_wait_time']);

总结与展望

监控与运维自动化是数据库管理的必然趋势。通过建立完善的监控体系、构建智能告警机制、实现运维任务自动化，可以显著提升数据库的稳定性和运维效率。

核心原则：

1. 监控指标要全面，覆盖系统、查询、资源各层面
2. 告警阈值要合理，避免误报和漏报
3. 运维脚本要标准化，确保可重复执行
4. 建立运维知识库，积累问题和解决方案
5. 定期回顾监控数据，持续优化运维策略

KES提供了丰富的系统视图和监控接口，为自动化运维提供了良好的基础。在实际应用中，建议逐步建立和完善自动化运维体系，从简单的监控告警开始，逐步扩展到智能诊断和自动修复。

期望本篇内容能够帮助你建立系统化的数据库监控与运维体系。通过自动化手段，让数据库运维工作更加高效、可靠。