KES 监控与运维自动化实战性能指标采集、告警体系与智能运维前言数据库系统的稳定运行离不开完善的监控体系和高效的运维管理。随着业务规模的扩大传统的人工运维方式已经难以应对复杂的监控需求和故障场景。建立自动化的监控告警体系实现智能运维成为数据库管理员的必备技能。本篇内容聚焦KES的监控与运维自动化详细讲解性能指标采集、告警体系构建、自动化运维脚本以及智能运维实践。全文以实际操作为主结合大量真实案例。如果你负责数据库运维工作或者希望提升运维效率相信这篇内容对你会有帮助。一、性能指标采集与监控性能监控是数据库运维的基础。通过持续采集关键性能指标可以及时发现潜在问题为性能优化提供数据支撑。系统级指标监控-- 查看数据库连接数SELECTcount(*)AStotal_connections,count(*)FILTER(WHEREstateactive)ASactive_connections,count(*)FILTER(WHEREstateidle)ASidle_connections,count(*)FILTER(WHEREstateidle in transaction)ASidle_in_transactionFROMsys_stat_activity;-- 查看数据库大小SELECTdatname,pg_size_pretty(pg_database_size(datname))ASsizeFROMsys_databaseORDERBYpg_database_size(datname)DESC;-- 查看表空间使用情况SELECTspcname,pg_size_pretty(pg_tablespace_size(spcname))ASsizeFROMsys_tablespace;查询性能监控-- 查看慢查询执行时间超过1秒SELECTpid,usename,now()-query_startASduration,queryFROMsys_stat_activityWHEREstateactiveANDnow()-query_startINTERVAL1 secondORDERBYdurationDESC;-- 查看锁等待情况SELECTblocked.pidASblocked_pid,blocked.queryASblocked_query,blocking.pidASblocking_pid,blocking.queryASblocking_query,now()-blocked.query_startASwait_durationFROMsys_stat_activity blockedJOINsys_locks lONblocked.pidl.pidANDNOTl.grantedJOINsys_locks grantedONl.locktypegranted.locktypeANDl.databaseISNOTDISTINCTFROMgranted.databaseANDl.relationISNOTDISTINCTFROMgranted.relationANDgranted.grantedtrueJOINsys_stat_activity blockingONgranted.pidblocking.pidWHEREblocked.pid!blocking.pid;-- 查看缓存命中率SELECTdatname,blks_read,blks_hit,round(100.0*blks_hit/NULLIF(blks_readblks_hit,0),2)AShit_ratioFROMsys_stat_databaseWHEREdatnamecurrent_database();资源使用监控-- 查看表膨胀情况SELECTschemaname,tablename,pg_size_pretty(pg_total_relation_size(schemaname||.||tablename))AStotal_size,pg_size_pretty(pg_relation_size(schemaname||.||tablename))ASdata_size,pg_size_pretty(pg_total_relation_size(schemaname||.||tablename)-pg_relation_size(schemaname||.||tablename))ASindex_size,n_dead_tup,n_live_tup,round(100.0*n_dead_tup/NULLIF(n_live_tupn_dead_tup,0),2)ASdead_ratioFROMsys_stat_user_tablesWHEREn_dead_tup1000ORDERBYn_dead_tupDESCLIMIT20;-- 查看索引使用情况SELECTschemaname,tablename,indexname,idx_scan,idx_tup_read,idx_tup_fetchFROMsys_stat_user_indexesORDERBYidx_scanASCLIMIT20;二、告警体系构建完善的告警体系是保障数据库稳定运行的关键。通过设定合理的告警阈值可以在问题恶化前及时发现并处理。告警规则设计-- 创建告警配置表CREATETABLEalert_rules(rule_id BIGSERIALPRIMARYKEY,rule_nameVARCHAR(100)NOTNULL,metric_nameVARCHAR(100)NOTNULL,threshold_valueNUMERICNOTNULL,comparison_operatorVARCHAR(10)NOTNULL,severityVARCHAR(20)NOTNULL,enabledBOOLEANDEFAULTTRUE,created_atTIMESTAMPDEFAULTnow());-- 初始化告警规则INSERTINTOalert_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_HOSTlocalhostDB_PORT54321DB_NAMEyour_dbDB_USERkingbase# 告警接收人ALERT_EMAILdbaexample.com# 检查连接数check_connections(){localcount$(psql-h$DB_HOST-p$DB_PORT-d$DB_NAME-U$DB_USER-t-c\SELECT count(*) FROM sys_stat_activity;)if[$count-gt200];thenecho警告数据库连接数达到$count超过阈值200|\mail-sKES告警连接数过高$ALERT_EMAILfi}# 检查慢查询check_slow_queries(){localcount$(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-gt0];thenecho警告发现$count个慢查询执行时间超过5秒|\mail-sKES告警慢查询$ALERT_EMAILfi}# 检查死锁check_deadlocks(){localcount$(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-gt0];thenecho严重检测到$count个死锁等待|\mail-sKES告警死锁$ALERT_EMAILfi}# 检查磁盘空间check_disk_space(){localusage$(df-h/data/kingbase|tail-1|awk{print $5}|seds/%//)if[$usage-gt85];thenecho警告磁盘使用率达到${usage}%超过阈值85%|\mail-sKES告警磁盘空间不足$ALERT_EMAILfi}# 执行所有检查check_connections check_slow_queries check_deadlocks check_disk_spaceecho告警检查完成$(date)告警通知集成-- 创建告警历史表CREATETABLEalert_history(alert_id BIGSERIALPRIMARYKEY,rule_idBIGINTREFERENCESalert_rules(rule_id),metric_valueNUMERICNOTNULL,alert_timeTIMESTAMPDEFAULTnow(),acknowledgedBOOLEANDEFAULTFALSE,acknowledged_byVARCHAR(100),acknowledged_atTIMESTAMP,notesTEXT);-- 告警确认函数CREATEORREPLACEFUNCTIONacknowledge_alert(p_alert_idBIGINT,p_userVARCHAR,p_notesTEXTDEFAULTNULL)RETURNSVOIDAS$$BEGINUPDATEalert_historySETacknowledgedTRUE,acknowledged_byp_user,acknowledged_atnow(),notesp_notesWHEREalert_idp_alert_id;END;$$LANGUAGEplpgsql;三、自动化运维脚本自动化运维能够显著提升工作效率减少人为失误。通过编写标准化的运维脚本可以实现日常运维任务的自动化执行。自动VACUUM脚本#!/bin/bash# auto_vacuum.sh - 自动VACUUM脚本DB_NAMEyour_dbDB_USERkingbaseLOG_FILE/var/log/kes/vacuum_$(date%Y%m%d).logecho开始执行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;|whilereadtable_name;doecho正在VACUUM表$table_name$LOG_FILEpsql-d$DB_NAME-U$DB_USER-cVACUUM ANALYZE$table_name;$LOG_FILE21echo完成时间$(date)$LOG_FILEecho---$LOG_FILEdoneechoVACUUM执行完成$(date)$LOG_FILE自动备份脚本#!/bin/bash# auto_backup.sh - 自动备份脚本BACKUP_DIR/backup/kesRETENTION_DAYS7DATE$(date%Y%m%d_%H%M%S)DB_NAMEyour_dbDB_USERkingbase# 创建备份目录mkdir-p$BACKUP_DIR# 执行逻辑备份echo开始备份$(date)sys_dump-U$DB_USER-d$DB_NAME-Fc-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];thenecho备份成功dump_${DB_NAME}_${DATE}.dump.gz# 计算文件大小SIZE$(du-sh$BACKUP_DIR/dump_${DB_NAME}_${DATE}.dump.gz|awk{print $1})echo文件大小$SIZEelseecho备份失败|mail-sKES备份告警dbaexample.comexit1fi# 清理过期备份find$BACKUP_DIR-namedump_*.dump.gz-mtime$RETENTION_DAYS-deleteecho已清理$RETENTION_DAYS天前的备份文件echo备份完成$(date)自动索引优化脚本#!/bin/bash# auto_index_optimize.sh - 自动索引优化脚本DB_NAMEyour_dbDB_USERkingbaseLOG_FILE/var/log/kes/index_optimize_$(date%Y%m%d).logecho开始索引优化分析$(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) );|whilereadindex_name;doecho发现未使用索引$index_name$LOG_FILE# 可选删除未使用索引# psql -d $DB_NAME -U $DB_USER -c DROP INDEX $index_name; $LOG_FILE 21done# 查找缺失索引的表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);|whilereadtable_name;doecho表$table_name可能存在缺失索引全表扫描次数1000$LOG_FILEdoneecho索引优化分析完成$(date)$LOG_FILE四、智能运维实践智能运维通过数据分析和自动化决策进一步提升运维效率。通过建立运维知识库和自动化处理流程可以实现常见问题的自动诊断和修复。智能诊断脚本#!/bin/bash# smart_diagnosis.sh - 智能诊断脚本DB_NAMEyour_dbDB_USERkingbaseREPORT_FILE/tmp/diagnosis_report_$(date%Y%m%d_%H%M%S).txtecho KES数据库诊断报告 $REPORT_FILEecho诊断时间$(date)$REPORT_FILEecho$REPORT_FILE# 1. 连接数分析echo【连接数分析】$REPORT_FILEpsql-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_FILEecho$REPORT_FILE# 2. 性能分析echo【性能分析】$REPORT_FILEpsql-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_FILEecho$REPORT_FILE# 3. 锁等待分析echo【锁等待分析】$REPORT_FILELOCK_COUNT$(psql-d$DB_NAME-U$DB_USER-t-c SELECT count(*) FROM sys_stat_activity WHERE wait_event_type Lock;)if[$LOCK_COUNT-gt0];thenecho发现$LOCK_COUNT个锁等待$REPORT_FILEpsql-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_FILEelseecho无锁等待$REPORT_FILEfiecho$REPORT_FILE# 4. 表膨胀分析echo【表膨胀分析】$REPORT_FILEpsql-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_FILEecho$REPORT_FILEecho 诊断完成 $REPORT_FILE# 发送报告mail-sKES数据库诊断报告dbaexample.com$REPORT_FILErm-f$REPORT_FILE自动化修复流程-- 创建自动化修复任务表CREATETABLEauto_repair_tasks(task_id BIGSERIALPRIMARYKEY,task_typeVARCHAR(50)NOTNULL,target_objectVARCHAR(200)NOTNULL,task_statusVARCHAR(20)DEFAULTPENDING,created_atTIMESTAMPDEFAULTnow(),executed_atTIMESTAMP,resultTEXT,executed_byVARCHAR(100));-- 自动VACUUM任务CREATEORREPLACEFUNCTIONschedule_vacuum_task(p_table_nameVARCHAR)RETURNSVOIDAS$$BEGININSERTINTOauto_repair_tasks(task_type,target_object)VALUES(VACUUM,p_table_name);END;$$LANGUAGEplpgsql;-- 执行待处理的VACUUM任务CREATEORREPLACEFUNCTIONexecute_pending_vacuum_tasks()RETURNSINTAS$$DECLAREv_task RECORD;v_countINT:0;BEGINFORv_taskINSELECT*FROMauto_repair_tasksWHEREtask_typeVACUUMANDtask_statusPENDINGORDERBYcreated_atLIMIT10LOOPBEGINEXECUTEformat(VACUUM ANALYZE %s,v_task.target_object);UPDATEauto_repair_tasksSETtask_statusCOMPLETED,executed_atnow(),resultSUCCESS,executed_byauto_repairWHEREtask_idv_task.task_id;v_count :v_count1;EXCEPTIONWHENOTHERSTHENUPDATEauto_repair_tasksSETtask_statusFAILED,executed_atnow(),resultSQLERRM,executed_byauto_repairWHEREtask_idv_task.task_id;END;ENDLOOP;RETURNv_count;END;$$LANGUAGEplpgsql;运维知识库建设-- 创建运维知识库表CREATETABLEops_knowledge_base(kb_id BIGSERIALPRIMARYKEY,issue_typeVARCHAR(100)NOTNULL,symptomsTEXTNOTNULL,root_causeTEXT,solutionTEXTNOTNULL,preventionTEXT,related_metricsTEXT[],created_atTIMESTAMPDEFAULTnow(),updated_atTIMESTAMPDEFAULTnow());-- 初始化知识库INSERTINTOops_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]);总结与展望监控与运维自动化是数据库管理的必然趋势。通过建立完善的监控体系、构建智能告警机制、实现运维任务自动化可以显著提升数据库的稳定性和运维效率。核心原则监控指标要全面覆盖系统、查询、资源各层面告警阈值要合理避免误报和漏报运维脚本要标准化确保可重复执行建立运维知识库积累问题和解决方案定期回顾监控数据持续优化运维策略KES提供了丰富的系统视图和监控接口为自动化运维提供了良好的基础。在实际应用中建议逐步建立和完善自动化运维体系从简单的监控告警开始逐步扩展到智能诊断和自动修复。期望本篇内容能够帮助你建立系统化的数据库监控与运维体系。通过自动化手段让数据库运维工作更加高效、可靠。