如何在Debian上监控PostgreSQL性能

在 Debian 上监控 PostgreSQL 性能

一 内置视图与快速检查

• 启用并查询语句级统计：在 postgresql.conf 中设置
  • shared_preload_libraries = ‘pg_stat_statements’
  • pg_stat_statements.max = 10000
  • pg_stat_statements.track = all
  • 执行：CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
• 常用查询
  • 当前连接数：SELECT count(*) AS now_conn_cnt FROM pg_stat_activity;
  • 运行超过 5 分钟 的查询：
    • SELECT * FROM pg_stat_activity WHERE state = ‘active’ AND now() - query_start > INTERVAL ‘5 minutes’;
  • 库级吞吐与事务：SELECT datname, numbackends, xact_commit, xact_rollback, tup_returned, tup_fetched FROM pg_stat_database;
  • 复制状态（流复制）：SELECT * FROM pg_stat_replication;
  • 表访问模式（是否走索引）：SELECT schemaname, tablename, seq_scan, idx_scan FROM pg_stat_all_tables ORDER BY seq_scan DESC LIMIT 10;
  • 检查长事务与空闲事务（可能持有快照、阻塞 vacuum）：
    • SELECT pid, datname, state, query, now() - xact_start AS xact_age, now() - query_start AS query_age FROM pg_stat_activity WHERE state IN (‘idle in transaction’, ‘active’) ORDER BY GREATEST(xact_age, query_age) DESC LIMIT 20;
• 执行计划与热点定位
  • EXPLAIN (ANALYZE, BUFFERS) SELECT …; 用于识别慢因（扫描方式、成本、缓冲命中）。

二 日志与慢查询分析

• 打开慢查询日志（示例阈值 1 秒）：
  • log_min_duration_statement = 1000
  • logging_collector = on
  • log_directory = ‘pg_log’
  • log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’
• 使用 pgBadger 生成 HTML 报告（在日志目录执行）：
  • perl pgBadger postgresql-*.log
  • 报告可直观查看：慢查询 Top N、总耗时、错误与警告、按小时/日趋势等，便于定位性能瓶颈与异常时段。

三 第三方监控与可视化

• Prometheus + Grafana
  • 部署 PostgreSQL Exporter 暴露指标，Prometheus 抓取，Grafana 配置面板展示连接、事务、缓存命中、复制延迟、表/索引统计等；适合 持续观测与告警。
• Zabbix / Nagios
  • 通过 Zabbix Agent 或插件采集数据库与系统指标，配置阈值告警，适合 企业级监控与值班告警。
• pgAdmin
  • 提供图形化的 仪表盘、查询工具、备份/还原与监控，便于日常运维与临时排查。

四 系统层与维护任务监控

• 系统资源
  • 使用 pg_top（类似 Linux top）实时查看数据库进程与资源占用；结合 Linux 的 sar/vmstat/iostat 观察 CPU、I/O、内存 压力。
• 维护任务
  • 用 BPFtrace 跟踪 VACUUM/自动清理执行时间与调用频次，及时发现清理滞后：
    • bpftrace -e ‘tracepoint:postgresql:postgresql__vacuum__start { @[comm] = count(); }’
  • 定期执行 VACUUM / ANALYZE（或启用 autovacuum）以回收空间、更新统计信息，避免表膨胀与计划退化。

五 告警阈值与优化方向

• 关键告警建议
  • 连接数接近 max_connections；复制延迟持续增长；慢查询数量突增；长事务/空闲事务堆积；检查点频繁；磁盘空间接近阈值。
• 快速优化抓手
  • 依据 EXPLAIN 结果优化 SQL、增加合适索引、避免 SELECT *、减少函数/计算在 WHERE 中的使用；
  • 适度调整 work_mem、shared_buffers、effective_cache_size 等参数；对大表考虑 分区；持续用监控数据验证调优成效。