金仓数据库监控脚本结合ZABBIX

金仓数据库监控脚本本文介绍如何结合 Shell 脚本与 Zabbix 构建金仓Kingbase数据库监控方案。由于 Zabbix 自带的 PostgreSQL 模板无法直接采集金仓数据库的数据因此需要编写自定义监控脚本进行适配。#!/bin/bash # Kingbase 监控采集脚本扁平 JSON适配 Zabbix JSONPath / LLD # 建议以 kingbase 用户执行 export KES_HOME/data/kingbase/kingbaseES/V9/KESRealPro/V009R003C015/Server export KES_DATA/data/kingbase/kingbaseES/V9/kes_instance export LD_LIBRARY_PATH$KES_HOME/lib export PATH$KES_HOME/bin:$PATH:$HOME/bin DB_HOST127.0.0.1 DB_PORT54321 DB_NAMEkingbase DB_USERzbxmoni DB_PASSZbx#moni2026 export PGPASSWORD$DB_PASS KSQL$KES_HOME/bin/ksql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -q -X # 单值查询空返回 null run_sql() { local out out$($KSQL -c $1 2/dev/null) [ -z $out ] echo null || echo $out } # 数值查询非数字返回 0 run_num() { local out out$($KSQL -c $1 2/dev/null) if [[ $out ~ ^-?[0-9](\.[0-9])?$ ]]; then echo $out else echo 0 fi } # JSON 数组查询空返回 [] run_json() { local out out$($KSQL -c $1 2/dev/null) [ -z $out ] echo [] || echo $out } # 1-4 全局基础 active_connections$(run_num SELECT count(*) FROM pg_stat_activity WHERE stateactive;) # 1 total_connections$(run_num SELECT count(*) FROM pg_stat_activity;) # 2 db_start_time$(run_sql SELECT pg_postmaster_start_time();) # 3 db_version$(run_sql SELECT version();) # 4 # 6-7 连接 idle_connections$(run_num SELECT count(*) FROM pg_stat_activity WHERE stateidle;) # 6 connection_usage_pct$(run_num SELECT round(100*count(*)::numeric/(SELECT setting::numeric FROM pg_settings WHERE namemax_connections),2) FROM pg_stat_activity;) # 7 # 9 等待/阻塞连接 waiting_connections$(run_num SELECT count(*) FROM pg_stat_activity WHERE wait_event_typeLock;) # 9 # 15-19 缓存/扫描/行数 table_cache_hit_ratio$(run_num SELECT COALESCE(round(100*sum(heap_blks_hit)::numeric/nullif(sum(heap_blks_hit)sum(heap_blks_read),0),2),0) FROM pg_statio_user_tables;) # 15 index_cache_hit_ratio$(run_num SELECT COALESCE(round(100*sum(idx_blks_hit)::numeric/nullif(sum(idx_blks_hit)sum(idx_blks_read),0),2),0) FROM pg_statio_user_tables;) # 16 seq_scan_total$(run_num SELECT COALESCE(sum(seq_scan),0) FROM pg_stat_user_tables;) # 17 idx_scan_total$(run_num SELECT COALESCE(sum(idx_scan),0) FROM pg_stat_user_tables;) # 18 live_rows$(run_num SELECT COALESCE(sum(n_live_tup),0) FROM pg_stat_user_tables;) # 19 # 23 行级锁等待时间 max_lock_wait_duration$(run_num SELECT coalesce(round(max(extract(epoch FROM clock_timestamp()-query_start))),0) FROM pg_stat_activity WHERE wait_event_typeLock;) # 23 # 24 WAL 发送进程数 wal_sender_count$(run_num SELECT count(*) FROM pg_stat_replication;) # 24 # 25 主从复制字节延迟 delay_bytes$(run_num SELECT COALESCE(max(sent_lsn - replay_lsn),0) FROM pg_stat_replication;) # 25 # 26 复制槽状态任一 active 即为 1 repl_slot_active$(run_num SELECT COALESCE(bool_or(active::int),0) FROM pg_replication_slots;) # 26 # 27 从库复制时间延迟 replay_dely$(run_num SELECT COALESCE(max(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)),0) FROM pg_stat_replication;) # 27 # 5/10/13/14/20/21/22 按库聚合 db_list db_list$(run_json SELECT COALESCE(json_agg(row_to_json(t)), []::json) FROM ( SELECT d.datname AS db_name, pg_size_pretty(pg_database_size(d.datname)) AS db_size, pg_database_size(d.datname) AS db_size_bytes, age(d.datfrozenxid) AS xid_age, round(100.0 * age(d.datfrozenxid) / 2000000000, 2) AS xid_usage_pct, CASE WHEN age(d.datfrozenxid) 2000000000 THEN 紧急需要立即冻结 WHEN age(d.datfrozenxid) 1500000000 THEN 警告接近事务ID极限 WHEN age(d.datfrozenxid) 1000000000 THEN 注意需要监控 ELSE 正常 END AS xid_status, COALESCE(s.xact_commit, 0) AS commit_total, COALESCE(s.xact_rollback, 0) AS rollback_total, COALESCE(s.deadlocks, 0) AS deadlock_total, COALESCE(s.temp_files, 0) AS temp_files_total, COALESCE(s.temp_bytes/1024/1024, 0) AS temp_MB_total FROM pg_database d LEFT JOIN pg_stat_database s ON s.datname d.datname WHERE d.datistemplate false ORDER BY age(d.datfrozenxid) DESC ) t; ) # 11 高危表 high_risk_tables$(run_json SELECT COALESCE(json_agg(row_to_json(t)), []::json) FROM ( SELECT c.relname AS table_name, age(c.relfrozenxid) AS xid_age, CASE WHEN age(c.relfrozenxid) 200000000 THEN 危险 WHEN age(c.relfrozenxid) 100000000 THEN 警告 ELSE 正常 END AS status, pg_size_pretty(pg_total_relation_size(c.oid)) AS size, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum FROM pg_class c JOIN pg_namespace n ON c.relnamespace n.oid WHERE c.relkind r AND n.nspname NOT IN (pg_catalog,information_schema) AND age(c.relfrozenxid) 100000000 ORDER BY age(c.relfrozenxid) DESC ) t; ) # 输出 cat EOF { active_connections: $active_connections, total_connections: $total_connections, db_start_time: $db_start_time, db_version: $db_version, idle_connections: $idle_connections, connection_usage_pct: $connection_usage_pct, waiting_connections: $waiting_connections, table_cache_hit_ratio: $table_cache_hit_ratio, index_cache_hit_ratio: $index_cache_hit_ratio, seq_scan_total: $seq_scan_total, idx_scan_total: $idx_scan_total, live_rows: $live_rows, max_lock_wait_duration: $max_lock_wait_duration, wal_sender_count: $wal_sender_count, delay_bytes: $delay_bytes, repl_slot_active: $repl_slot_active, replay_dely: $replay_dely, db_list: $db_list, high_risk_tables: $high_risk_tables } EOF unset PGPASSWORDUserParameterkingbase.monitor,sudo -u kingbase /opt/zabbix-agent/scripts/kingbase_monitor.sh结果集原编号SQL 内容JSON 字段类型1活跃连接数active_connections数字2总连接数total_connections数字3数据库启动时间db_start_time字符串4数据库版本db_version字符串5数据库大小db_list[].db_size / db_size_bytes数组6空闲连接数idle_connections数字7连接数使用率connection_usage_pct数字9等待/阻塞连接waiting_connections数字10数据库年龄db_list[].xid_age / xid_status数组11高危表high_risk_tables数组13事务提交数db_list[].commit_total数组14事务回滚数db_list[].rollback_total数组15表缓存命中率table_cache_hit_ratio数字16索引缓存命中率index_cache_hit_ratio数字17全表扫描次数seq_scan_total数字18索引扫描次数idx_scan_total数字19活数据行数live_rows数字20死锁次数db_list[].deadlock_total数组21临时文件数db_list[].temp_files_total数组22临时文件大小db_list[].temp_MB_total数组23行级锁等待时间max_lock_wait_duration数字24WAL 发送进程数wal_sender_count数字25主从复制字节延迟delay_bytes数字26复制槽状态repl_slot_active数字0/127从库复制时间延迟replay_dely数字zabbix 配置主要说明LLD自动发现规则配置Item Prototype Key JSONPathPreprocessing kingbase.db.db_size_bytes[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].db_size_bytes.first() kingbase.db.xid_usage_pct[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].xid_usage_pct.first() kingbase.db.commit_total[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].commit_total.first() kingbase.db.rollback_total[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].rollback_total.first() kingbase.db.deadlock_total[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].deadlock_total.first() kingbase.db.temp_files_total[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].temp_files_total.first() kingbase.db.temp_mb_total[{#DB_NAME}] $.db_list[?(.db_name{#DB_NAME})].temp_MB_total.first()字段 值 Name Table {#TABLE_NAME} xid age Type Dependent item Key kingbase.table.xid_age[{#TABLE_NAME}] Master item Kingbase Monitor Raw JSON Type of information Numeric (unsigned) Preprocessing Name Parameters JSONPath $.high_risk_tables[?(.table_name{#TABLE_NAME})].xid_age.first()五、全局单值指标不需要 LLD顶层的那些单值直接建普通 Item类型选 Dependent itemMaster item 选 Kingbase Monitor Raw JSONPreprocessing 用简单 JSONPath-------- -------------Item KeyJSONPathkingbase.active_connections$.active_connectionskingbase.total_connections$.total_connectionskingbase.connection_usage_pct$.connection_usage_pctkingbase.waiting_connections$.waiting_connectionskingbase.max_lock_wait_duration$.max_lock_wait_durationkingbase.table_cache_hit_ratio$.table_cache_hit_ratiokingbase.index_cache_hit_ratio$.index_cache_hit_ratiokingbase.seq_scan_total$.seq_scan_totalkingbase.idx_scan_total$.idx_scan_totalkingbase.live_rows$.live_rowskingbase.wal_sender_count$.wal_sender_countkingbase.delay_bytes$.delay_byteskingbase.replay_dely$.replay_delykingbase.repl_slot_active$.repl_slot_activedb_start_time 和 db_version 是字符串Type of information 选 Character 或 Text六、验证 LLD 是否正常工作配置保存后等一个更新周期或手动执行 Execute now然后进入 Monitoring → Latest data筛选你的主机看是否出现了 kingbase.db.xid_age[test]、kingbase.db.xid_age[kingbase] 等自动创建的 item。或者进入 Data collection → Hosts → 你的主机 → Discovery点进 LLD 规则看 Discovered items 列表。如果某个 item 变成 NOT SUPPORTED检查JSONPath 表达式中的宏名是否正确{#DB_NAME} 大小写敏感该库的字段在 JSON 中是否存在比如 temp_MB_total 拼写数值字段是否误配成了 Text 类型