如何监控CentOS上PostgreSQL的性能

监控目标与总体思路

• 在 CentOS 上，建议从四个层面建立可观测性：
  1. PostgreSQL 内置统计视图（会话、语句、库、表、复制等）；
  2. 操作系统资源（CPU、内存、I/O、网络）；
  3. 可视化与告警平台（本地 GUI 与开源/商业平台）；
  4. 日志与 WAL 分析（慢查询、检查点、回放延迟）。
• 目标是持续观察：连接数、TPS/QPS、缓存命中率、临时文件/排序、检查点、复制延迟、慢查询，并在阈值触发时告警与定位根因。

PostgreSQL 内置视图与关键 SQL

• 启用语句级统计（首次需执行一次）：
  • 在数据库中执行：CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  • 在 postgresql.conf 中确保：shared_preload_libraries = 'pg_stat_statements'，然后重启实例。
• 常用查询（按需加入监控或报表）：
  • 活跃会话与等待事件：
    • SELECT pid, usename, client_addr, state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state = 'active';
  • 库级吞吐与缓存命中率：
    • SELECT datname, numbackends, tps, blk_read_time, blk_write_time, (blks_hit::float/(blks_hit+blks_read+1)) AS hit_ratio FROM pg_stat_database;
  • 复制延迟（备库）：
    • SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;
  • 表级热点与膨胀线索：
    • SELECT schemaname, tablename, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;
  • 索引使用率：
    • SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;
  • 最慢 SQL（需 pg_stat_statements）：
    • SELECT query, calls, total_time/calls AS avg_ms, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
• 建议将以上查询纳入定时任务或监控采集器，形成趋势与阈值告警基线。

命令行与轻量级工具

• pgCenter（类 top 的实时与历史分析）
  • 特点：整合 pg_stat_activity、pg_stat_database、pg_stat_replication 等视图与系统指标，支持会话级等待事件采样、记录回放与报表导出。
  • 快速上手：
    • 创建只读监控账号：CREATE USER monitor WITH PASSWORD '***'; GRANT pg_monitor TO monitor;（PostgreSQL 9.6+ 提供 pg_monitor 角色）
    • 实时查看：pgcenter top -h 127.0.0.1 -p 5432 -U monitor -d postgres
    • 历史记录与分析：pgcenter record -f /tmp/pg.dump -d postgres -U monitor 与 pgcenter report -f /tmp/pg.dump --format=csv -o /tmp/pg.csv
• 系统观测配合：
  • top/vmstat/iostat/free/netstat 观察 CPU、内存、I/O、网络与连接状态，与数据库指标交叉验证瓶颈所在。

图形化与平台化监控

• pgAdmin（桌面/Web GUI）
  • 提供 Dashboard、Server Sessions、Transactions per second、Tuples I/O、Block I/O、Server Activity 等面板，便于直观查看与定位问题。
• Prometheus + Grafana（开源可定制）
  • 使用 PostgreSQL Exporter 采集指标，Grafana 构建仪表盘与告警规则，适合长期趋势与容量规划。
• 企业级与云监控
  • Datadog、SolarWinds、ManageEngine、Dynatrace、AppDynamics、PRTG 等提供与基础设施/APM 联动、异常检测与丰富告警能力。
• Zabbix
  • 通过 libzbxpgsql 插件与模板实现对 PostgreSQL 的监控与告警集成。

告警阈值与优化方向

• 建议优先关注的阈值与动作
  • 连接数接近 max_connections：及时排查连接泄漏与业务连接池配置。
  • 复制延迟持续增长：检查网络、备库负载与 WAL 传输/回放瓶颈。
  • 缓存命中率偏低：结合 pg_stat_database 评估 shared_buffers 与工作负载匹配度。
  • 检查点频繁或写入抖动：pg_stat_bgwriter 观察检查点与后台写入统计，必要时调整 checkpoint_timeout、checkpoint_completion_target。
  • 临时文件/排序偏高：结合 work_mem 与 SQL 写法优化（避免大排序/哈希聚合溢出）。
  • 死锁与长事务：pg_stat_activity 与锁视图定位持有/等待会话，优化事务边界与隔离级别。
• 常用优化参数（需结合实际与压测微调）
  • shared_buffers（常见为内存的 1/4 左右）、work_mem、maintenance_work_mem、effective_cache_size、checkpoint_timeout/checkpoint_completion_target。
• 空间与 WAL 分析
  • 空间：pg_size_pretty(pg_database_size('db'))、pg_total_relation_size('schema.tbl')；
  • WAL：pg_waldump 分析异常写入与回放行为。
• 例行维护
  • 保持 VACUUM/ ANALYZE 及时执行（必要时使用 autovacuum 调优），避免表膨胀与统计信息过期导致执行计划劣化。