PostgreSQL 17+ 关键基础监控指标详解

目录

1.核心点

1.1.致命级（☆☆☆☆☆）—— 立即导致实例不可用，需设置紧急告警，SLA响应目标：

1.2.严重级（☆☆☆☆）—— 性能严重劣化，间接导致不可用，SLA响应目标：

1.3.警告级（☆☆☆）—— 需关注并分析根因，防止演变为严重问题，SLA响应目标：

2.操作系统层指标（OS-Level）

2.1.CPU使用率

2.2.内存使用率

2.3.磁盘使用率

2.4.磁盘 IOPS

2.5.磁盘吞吐量

3.PostgreSQL 连接层指标

3.1.连接数使用率

3.2.空闲连接占比

3.3.idle in transaction 连接

4.事务与 MVCC 指标（致命级）

4.1 事务 ID 年龄（Transaction ID Wraparound）

4.2 Dead Tuples 比例（表膨胀）

4.3 Autovacuum 运行状态

5.WAL 与复制指标

5.1 WAL 生成速率

5.2 复制延迟

5.3 复制槽状态

5.4 Checkpoint 频率与耗时

6.锁与等待事件指标

6.1 锁等待

6.2 等待事件分布

7.查询性能指标

7.1 长事务

7.2 慢查询

7.3 临时文件使用

8.缓存与 Buffer 指标

8.1 Buffer Cache 命中率

8.2 索引命中率

1.核心点

一文详解postgres数据库需要关注的指标，将指标按致命→严重→警告三级分类重新组织，便于分层制定告警策略和响应SLA。

PostgreSQL 监控指标三级分类体系：

1.1.致命级（☆☆☆☆☆）—— 立即导致实例不可用，需设置紧急告警，SLA响应目标：

1.2.严重级（☆☆☆☆）—— 性能严重劣化，间接导致不可用，SLA响应目标：

1.3.警告级（☆☆☆）—— 需关注并分析根因，防止演变为严重问题，SLA响应目标：

2.操作系统层指标（OS-Level）

2.1.CPU使用率

• 指标：CPU 总使用率
• 风险：CPU 持续高位会导致查询超时、连接堆积、新连接被拒、实例无响应。
• 建议告警阈值：
  • 警告：CPU > 80% 且持续 3 分钟
  • 严重：CPU > 95% 且持续 1 分钟
• PG 快速排查指令：
  • 查活动会话与高耗 SQL：

    SELECT pid, usename, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

  • 检查锁等待

    SELECT * FROM pg_locks WHERE NOT granted;

  • 查看慢查询日志 / 使用 EXPLAIN ANALYZE 优化高耗查询。

2.2.内存使用率

• 指标：实例可用内存占总内存的比例
• 风险：可用内存耗尽会触发 Linux OOM Killer 杀掉 PostgreSQL 进程，导致实例 crash 并重启，影响可用性和数据连接稳定性。
• 建议告警阈值：
  • 警告：内存使用率 > 85%
  • 严重：内存使用率 > 95%
• 关键 PostgreSQL 参数（需与实例内存容量对齐）：
  • shared_buffers（建议约等于总内存的 25%）
  • work_mem（按并发排序/复杂查询设置，过大累加会耗尽内存）
  • maintenance_work_mem（VACUUM/CREATE INDEX 等操作使用）
  • effective_cache_size（用于估算查询计划中的可用文件系统缓存）
• PG 快速排查命令：
  • 查看配置：

    SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW effective_cache_size;

  • bgwriter/缓存命中：

    SELECT * FROM pg_stat_bgwriter;

  • 检查活动会话内存消耗（重点并发大查询/排序/哈希）：参照 pg_stat_activity + 查询执行计划（EXPLAIN ANALYZE）。
  • 系统层面：free -m / vmstat / ps aux --sort=-rss 查占用进程。

2.3.磁盘使用率

• 指标：PGDATA 与 WAL 所在磁盘的使用率
• 风险：磁盘使用率 100% 时，PostgreSQL 无法写 WAL → 触发 PANIC: could not write to file → 实例立即 crash，不可用。
• 建议告警阈值：
  • 警告：> 80%
  • 严重：> 90%
  • 紧急：> 95%
• 常见“磁盘杀手”：
  • WAL 堆积：逻辑复制槽/物理复制未推进，WAL 无法回收
  • 临时文件未清理：复杂排序 / 哈希 / 大查询产生大量 temp file
  • 表膨胀：dead tuples 未及时 VACUUM / AUTOVACUUM 不充分
  • 大事务：长事务产生大量 WAL 且阻止 VACUUM
• PG 内部排查：
  • 库级体积：

    SELECT pg_size_pretty(pg_database_size('dbname'));

  • 表 / 索引体积（示例）：

    SELECT relname, pg_size_pretty(pg_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_relation_size(relid) DESC LIMIT 20;

  • WAL 目录大小（服务器上）：

    du -sh $PGDATA/pg_wal

    2.4.磁盘 IOPS

  • 指标：磁盘每秒读写操作次数
  • 异常影响：
    • IOPS 达上限 → I/O 等待升高 → 查询延迟激增 → checkpoint 超时 → 连接积压 → 实例不可用
  • 告警参考：使用率 > 80%（基于预置 IOPS 或 gp3 基线 3000 IOPS）
  • 关联指标：
    • ReadLatency / WriteLatency（秒）
    • DiskQueueDepth> 10 → 队列堆积风险

      -- 查看各 backend 类型的 I/O 统计 SELECT * FROM pg_stat_io; -- 查看等待 checkpoint 的进程 SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'IO';

2.5.磁盘吞吐量

• 指标：磁盘每秒读写数据量（ReadThroughput / WriteThroughput，单位 MB/s 或 Bytes/Second）
• 异常影响：吞吐量饱和 → 大查询 / VACUUM / 备份阻塞 → 性能严重下降
• 告警参考：使用率 > 80%（基于存储类型上限，如 gp3 基线 125 MB/s）

3.PostgreSQL 连接层指标

3.1.连接数使用率

• 指标定义：当前活跃会话数占max_connections配置值的百分比。
• 故障机制：连接数达到上限后，PostgreSQL拒绝新建连接并返回FATAL: too many connections错误，导致应用服务不可用。
• 告警阈值：建议分级告警，70%警告、85%严重、95%紧急。
• 诊断方法：
  • 获取当前连接数

    SELECT count(*) FROM pg_stat_activity;

  • 查看最大连接数限制

    SHOW max_connections;

• 资源消耗基线：PostgreSQL 17默认max_connections=100，每个空闲连接约消耗5-10MB共享内存。

3.2.空闲连接占比

• 指标定义：pg_stat_activity中state = 'idle'的连接数占总连接数的比例。
• 故障机制：大量空闲连接持续占用内存与连接槽位，间接导致有效连接数耗尽，触发too many connections错误。
• 告警阈值：建议复合条件告警——空闲连接占比 > 60% 且总连接数超过max_connections的50%。
• 诊断方法：

  SELECT state, count(*) FROM pg_stat_activity GROUP BY state;按状态统计连接分布。

• 治理建议：
  • 部署PgBouncer连接池，复用连接，降低空闲开销
  • 设置idle_in_transaction_session_timeout参数，自动终止事务内空闲连接

3.3.idle in transaction 连接

• 指标定义：pg_stat_activity中state = 'idle in transaction'的连接数，即已开启事务但未提交或回滚的会话。
• 故障机制：此类连接长期悬空会持有锁资源，阻塞并发DDL/DML操作；同时阻碍VACUUM回收死元组，导致表膨胀，最终可能耗尽磁盘空间。
• 告警阈值：建议基于持续时间分级告警——超过5分钟警告，超过30分钟严重。
• 诊断方法：执行以下SQL定位长事务悬空连接：

  SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '5 minutes';

• 治理参数（PG 17）：设置idle_in_transaction_session_timeout = 300000（5分钟），自动终止超过阈值的悬空事务连接。

4.事务与 MVCC 指标（致命级）

4.1 事务 ID 年龄（Transaction ID Wraparound）

• 指标定义：数据库中最老未冻结事务ID（datfrozenxid）与当前事务ID的差值。
• 故障机制：XID年龄接近2^31（约21亿）时，PostgreSQL强制进入只读保护模式，拒绝所有写操作，需手动执行VACUUM FREEZE恢复。
• 告警阈值：建议分级告警——超过5亿警告、10亿严重、15亿紧急。
• 诊断方法：
  • 库级：

    SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

  • 表级：

    SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind='r' ORDER BY 2 DESC LIMIT 20;

• 根因分析：VACUUM被长事务阻塞、autovacuum配置不当、大表VACUUM超时。
• PG 17增强：改进VACUUM并行处理能力，pg_stat_progress_vacuum提供更细粒度进度信息。

4.2 Dead Tuples 比例（表膨胀）

• 指标定义：死元组数占总元组数（活元组+死元组）的百分比。
• 故障机制：死元组堆积导致表膨胀，造成全表扫描性能下降、磁盘空间浪费，严重时耗尽存储。
• 告警阈值：死元组占比超过20%警告、超过50%严重（同时过滤死元组绝对量>10000）。
• 诊断方法：查询pg_stat_user_tables计算n_dead_tup / (n_live_tup+n_dead_tup)，按占比降序输出。
• 根因分析：autovacuum清理速度跟不上更新频率，或被长事务阻塞。

4.3 Autovacuum 运行状态

• 指标定义：当前正在运行的autovacuum worker进程数量及队列积压情况。
• 故障机制：autovacuum worker持续满载时，清理能力达到上限，导致死元组堆积、表膨胀，并增加XID回卷风险。
• 告警阈值：autovacuum worker数量持续30分钟达到autovacuum_max_workers上限。
• 诊断方法：执行监控活跃worker数。

  SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

• 关键参数：
  • autovacuum_max_workers（默认3）：最大并发worker数
  • autovacuum_naptime（默认1min）：扫描库的间隔
  • autovacuum_vacuum_threshold（默认50）：触发VACUUM的最小死元组数
  • autovacuum_vacuum_scale_factor（默认0.2）：触发VACUUM的死元组比例系数

5.WAL 与复制指标

5.1 WAL 生成速率

• 指标定义：单位时间内生成的预写日志（WAL）数据量。
• 故障机制：WAL生成速率突增会导致磁盘快速耗尽，若归档速度跟不上，将触发磁盘满并导致实例崩溃。
• 告警阈值：WAL生成速率较基线突增3倍以上触发告警。
• 诊断方法：
  • 传统方式：定期采样pg_current_wal_lsn()计算差值
  • PG 17+：直接查询pg_stat_wal视图获取累计统计
• 根因分析：大事务批量写入、高频DML操作、临时表大量使用。

5.2 复制延迟

• 指标定义：主库与从库之间WAL复制的延迟量（字节或时间）。
• 故障机制：延迟过大导致从库数据滞后，故障切换时可能丢失数据；同时复制槽会阻止WAL清理，引发主库磁盘满。
• 告警阈值：建议复合告警——延迟超过100MB或60秒警告，超过1GB或300秒严重。
• 诊断方法：查询pg_stat_replication计算sent_lsn与replay_lsn差值：

  SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication;

5.3 复制槽状态

• 指标定义：复制槽活跃状态及保留的WAL数据量。
• 故障机制：非活跃复制槽会无限期保留WAL文件，导致磁盘空间耗尽、实例崩溃。
• 告警阈值：非活跃复制槽存在超过1小时警告，WAL保留量超过10GB严重。
• 诊断方法：查询pg_replication_slots计算保留WAL量：

  SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots;

• PG 17增强：支持max_slot_wal_keep_size参数限制复制槽WAL保留上限，防止磁盘耗尽。

5.4 Checkpoint 频率与耗时

• 指标定义：checkpoint执行的时间间隔与单次完成耗时。
• 故障机制：checkpoint过于频繁引发I/O风暴导致性能抖动；单次耗时过长会增加崩溃恢复时间。
• 告警阈值：checkpoint间隔小于5分钟（过于频繁）警告；单次耗时超过checkpoint_timeout的50%警告。
• 监控视图（PG 17+）：使用pg_stat_checkpointer独立视图（PG 17从pg_stat_bgwriter分离）。
• 关键参数调优：
  • checkpoint_timeout（默认5min）：checkpoint最大间隔
  • max_wal_size（默认1GB）：触发checkpoint的WAL上限
  • checkpoint_completion_target（默认0.9）：平滑写入目标，建议0.7-0.9

6.锁与等待事件指标

6.1 锁等待

• 指标定义：正在等待获取锁的会话数量及其等待时长。
• 故障机制：锁等待链过长可能引发死锁导致事务回滚；长时间锁等待会造成连接堆积，间接耗尽连接池。
• 告警阈值：锁等待超过30秒的会话数大于5个触发警告；任一锁等待超过5分钟触发严重告警。
• 诊断方法：通过pg_locks与pg_stat_activity关联查询阻塞链：

  SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query FROM pg_locks blocked_locks JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.pid != blocked_locks.pid JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid WHERE NOT blocked_locks.granted;

• 关键参数（PG 17）：
  • lock_timeout：建议设置30秒，超时自动取消语句
  • deadlock_timeout（默认1秒）：死锁检测周期

6.2 等待事件分布

• 指标定义：各类等待事件（Lock、LWLock、IO、BufferPin等）的占比分布。
• 故障机制：IO等待过多表明磁盘带宽瓶颈；LWLock等待过多反映内部缓冲区或锁管理器的激烈争用。
• 告警阈值：任一等待事件类型占比超过50%且持续5分钟触发告警。
• 诊断方法：按等待事件类型聚合统计：

  SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state = 'active' AND wait_event IS NOT NULL GROUP BY wait_event_type, wait_event ORDER BY count DESC;

7.查询性能指标

7.1 长事务

• 指标定义：事务运行时间超过预设阈值的会话。
• 故障机制：长事务阻止VACUUM清理死元组，导致表膨胀、XID年龄增长加速，最终可能触发事务ID回卷或磁盘耗尽。
• 告警阈值：事务运行超过30分钟警告，超过2小时严重。
• 诊断方法：定位运行超过阈值的事务：

  SELECT pid, now() - xact_start AS xact_duration, now() - query_start AS query_duration, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start > interval '30 minutes' ORDER BY xact_duration DESC;

7.2 慢查询

• 指标定义：执行时间超过阈值的SQL语句。
• 故障机制：慢查询持续堆积会耗尽CPU/IO资源，导致连接积压，最终实例不可用。
• 告警阈值：单条查询超过10秒警告，超过60秒严重。
• 诊断工具（PG 17+）：使用pg_stat_statements视图分析（PG 17增强内存使用和嵌套语句追踪）：

  SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;

• 关键参数：设置log_min_duration_statement = 1000（毫秒），记录超过1秒的查询。

7.3 临时文件使用

• 指标定义：排序、哈希等内存操作溢出到磁盘产生的临时文件大小。
• 故障机制：大量临时文件生成会快速消耗磁盘空间，导致磁盘满实例崩溃。
• 告警阈值：单查询临时文件超过1GB警告，累计临时文件超过10GB严重。
• 诊断方法：查询数据库级临时文件统计：

  SELECT datname, temp_files, temp_bytes FROM pg_stat_database WHERE temp_bytes > 0;

• 关键参数调优：
  • work_mem：增大可减少磁盘溢出（注意并发连接总内存）
  • temp_file_limit（PG 17+）：限制单会话临时文件最大尺寸

8.缓存与 Buffer 指标

8.1 Buffer Cache 命中率

• 指标定义：数据页从shared_buffers共享缓冲区中命中的比例。
• 故障机制：命中率过低导致大量磁盘IO，查询性能下降并引发IO瓶颈。
• 告警阈值：命中率低于95%警告，低于90%严重。
• 诊断方法：
  • 全局级：计算pg_stat_database中缓存命中比例

    SELECT sum(blks_hit) / greatest(sum(blks_hit + blks_read), 1) * 100 AS cache_hit_ratio FROM pg_stat_database;

  • 表级：定位低命中率的表

    SELECT relname, round(heap_blks_hit::numeric / greatest(heap_blks_hit + heap_blks_read, 1) * 100, 2) AS hit_ratio FROM pg_statio_user_tables ORDER BY heap_blks_read DESC LIMIT 20;

8.2 索引命中率

• 指标定义：索引扫描次数占总扫描次数（索引扫描+顺序扫描）的比例。
• 故障机制：索引命中率低意味着大量全表扫描，增加CPU和IO压力。
• 告警阈值：OLTP场景下建议低于90%触发告警。
• 诊断方法：定位索引命中率低的表

  SELECT relname, seq_scan, idx_scan, round(idx_scan::numeric / greatest(idx_scan + seq_scan, 1) * 100, 2) AS idx_hit_ratio FROM pg_stat_user_tables WHERE seq_scan + idx_scan > 100 ORDER BY idx_hit_ratio ASC LIMIT 20;