如何监控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 调优），避免表膨胀与统计信息过期导致执行计划劣化。

最新问答