在Linux系统中对PostgreSQL性能进行监控

一、内置统计视图与即时诊断功能

• 使用以下核心视图快速定位问题：
  • pg_stat_activity：查看当前连接、状态、正在执行的SQL、客户端地址与应用名，配合筛选如 state=‘active’ 或 query_start 可定位长事务/阻塞会话。
  • pg_stat_statements：按SQL文本聚合调用次数、总/平均耗时、行数等，用于发现最耗时的SQL与异常执行计划。
  • pg_stat_database：数据库级吞吐与命中率，如 tup_returned/tup_fetched（逻辑读/返回行）、blks_hit/(blks_hit+blks_read)（缓存命中率）、事务提交/回滚数。
  • pg_stat_bgwriter / pg_stat_wal：观察检查点频率、后台写与WAL写入，判断是否因刷脏或WAL瓶颈导致延迟。
  • pg_stat_user_tables / pg_statio_user_tables：表的扫描方式（顺序/索引）、读写次数与命中率，辅助索引与访问路径优化。
  • pg_stat_replication：主从复制的延迟与状态，用于保障备库可用性与一致性。
• 典型SQL示例（按需调整过滤条件）：
  • 活跃会话与长查询
    • SELECT pid, usename, application_name, client_addr, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC LIMIT 20;
  • 数据库级命中率与吞吐
    • SELECT datname, blks_hit::float/(blks_hit+blks_read) AS hit_ratio, tup_returned, tup_fetched FROM pg_stat_database;
  • 最慢SQL（需启用 pg_stat_statements）
    • SELECT query, calls, total_time/calls AS a vg_ms, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
  • 复制延迟（单位：字节，主库执行）
    • SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS byte_lag FROM pg_stat_replication;
• 提示：为获得完整SQL统计，需在数据库中创建扩展并加载：CREATE EXTENSION IF NOT EXISTS pg_stat_statements;。

二 日志与慢查询分析

• 启用与配置慢查询日志（postgresql.conf 或对应 include 目录）：
  • 开启日志收集：logging_collector = on
  • 记录超过阈值的语句：log_min_duration_statement = 1000（单位毫秒，示例为1秒）
  • 记录语句与参数：log_statement = ‘all’（或根据需要设为 ‘mod’）
  • 建议开启：log_duration = on、log_lock_waits = on、log_temp_files = 0
• 使用 pgBadger 分析日志并生成HTML报告，快速洞察慢查询Top N、错误与等待事件等，适合周期性巡检与容量评估。

三 操作系统与资源监控

• 结合系统工具定位瓶颈（配合数据库视图交叉验证）：
  • CPU/内存/负载：top / htop、free -m
  • 磁盘I/O：iostat -x 1（关注 await、r/s、w/s、util%）
  • 虚拟内存与调度：vmstat 1
  • 网络与连接：ss -s、netstat -anp | grep :5432
• 经验法则：当 iostat util% 持续接近 100% 多为磁盘瓶颈；load a verage 高于 CPU 核数 且 r 队列长多为CPU饱和；pg_stat_bgwriter 检查点频繁且 blks_written 高企常伴随I/O压力。

四 可视化与长期监控方案

• 开源可观测性栈：
  • Prometheus + Grafana + PostgreSQL Exporter：暴露连接数、事务、缓存命中率、复制延迟、WAL/检查点等时序指标，Grafana 提供可定制仪表盘与告警。
  • pgAdmin：提供图形化性能监控面板、查询执行计划、表空间与服务器状态等，适合日常运维与临时排查。
• 企业/托管与多数据库场景：
  • Zabbix（配合 libzbxpgsql）、Nagios、DataDog、Dynatrace、SolarWinds、ManageEngine、AppDynamics 等，具备仪表盘、阈值告警、历史趋势与APM关联能力，适合大规模与合规要求环境。
• 轻量替代与专用工具：
  • pgwatch2（轻量、可定制仪表盘、历史数据）、PGObserver、ClusterControl、Pganalyze（查询性能洞察与自动分析）、Sematext、Paessler PRTG 等，可按规模与预算选择。

五 落地步骤与关键告警阈值

• 快速落地路径
  1. 启用扩展与基线采集：CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 按需设置 log_min_duration_statement 并部署 pgBadger 做周报。
  2. 建立“数据库+系统”一体化看板：PostgreSQL 指标（连接、事务、命中率、复制延迟、检查点）与系统指标（CPU、内存、I/O、网络）同屏展示。
  3. 配置告警：连接数接近 max_connections；慢查询突增；复制延迟超过业务容忍阈值；缓存命中率低于 95%；磁盘 util% 持续 > 80%；检查点间隔过短且 checkpoints_timed + checkpoints_req 频繁。
  4. 例行巡检：每周分析 pg_stat_statements Top SQL，核查缺失/膨胀索引，评估 VACUUM/ ANALYZE 是否及时；结合 pg_stat_bgwriter 与 WAL 指标评估I/O压力并优化参数或硬件。
• 常用参数与扩展清单
  • 扩展：pg_stat_statements
  • 参数：shared_buffers、work_mem、effective_cache_size、log_min_duration_statement、log_statement、log_duration、log_lock_waits、log_temp_files、logging_collector
• 风险提示：修改参数与执行维护操作前请在测试环境验证并做好备份；变更 shared_buffers / work_mem 等会影响内存占用与查询计划，需结合实例规格与负载谨慎调整。