Skip to content

PostgreSQL 常用监控指标详解

PostgreSQL的监控指标是DBA日常运维工作中的重要组成部分,通过监控关键指标,可以及时发现和解决数据库问题,确保数据库的稳定运行。本文档将详细介绍PostgreSQL中常用的监控指标,包括监控来源、阈值建议和使用场景,帮助DBA构建全面的监控体系。

连接与会话指标

连接数量指标

指标名称说明监控来源阈值建议监控SQL
活跃连接数当前正在执行SQL的连接数pg_stat_activity> max_connections * 0.8SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';
总连接数当前所有连接数pg_stat_activity> max_connections * 0.9SELECT COUNT(*) FROM pg_stat_activity;
空闲连接数空闲等待的连接数pg_stat_activity> 500SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle';
空闲事务连接数处于空闲事务状态的连接数pg_stat_activity> 10SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle in transaction';
连接拒绝数被拒绝的连接尝试次数pg_stat_database> 0SELECT datname, xact_rollback FROM pg_stat_database;

连接来源分布

监控SQL

sql
SELECT 
  usename, 
  application_name, 
  client_addr, 
  COUNT(*) 
FROM pg_stat_activity 
GROUP BY usename, application_name, client_addr 
ORDER BY COUNT(*) DESC;

使用场景

  • 识别异常连接来源
  • 优化连接池配置
  • 调整连接限制
  • 排查连接泄漏问题

版本差异

  • PostgreSQL 13+新增了query_id字段,可用于连接查询性能分析
  • PostgreSQL 14+新增了backend_xid字段,可用于事务跟踪

事务与锁指标

事务指标

指标名称说明监控来源阈值建议监控SQL
事务提交率已提交事务数/总事务数pg_stat_database< 0.95SELECT datname, xact_commit::float / NULLIF(xact_commit + xact_rollback, 0) AS commit_ratio FROM pg_stat_database;
事务回滚率回滚事务数/总事务数pg_stat_database> 0.05SELECT datname, xact_rollback::float / NULLIF(xact_commit + xact_rollback, 0) AS rollback_ratio FROM pg_stat_database;
长事务数量运行时间超过阈值的事务数pg_stat_activity> 0SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '5 minutes';

锁指标

指标名称说明监控来源阈值建议监控SQL
锁等待数量等待锁的会话数pg_locks> 0SELECT COUNT(DISTINCT pid) FROM pg_locks WHERE NOT granted;
锁等待时间平均锁等待时间(ms)pg_stat_activity + pg_locks> 100SELECT now() - query_start AS wait_time FROM pg_stat_activity a JOIN pg_locks l ON a.pid = l.pid WHERE NOT l.granted;
死锁数量死锁发生次数pg_stat_database> 0SHOW deadlock_timeout;

锁等待监控SQL

sql
SELECT 
  blocked_locks.pid AS blocked_pid, 
  blocking_locks.pid AS blocking_pid, 
  now() - blocked_activity.query_start AS wait_time, 
  blocked_activity.query AS blocked_query, 
  blocking_activity.query AS blocking_query 
FROM pg_locks blocked_locks 
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid 
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype 
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database 
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation 
  AND blocking_locks.pid != blocked_locks.pid 
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid 
WHERE NOT blocked_locks.granted;

复制与高可用指标

主从复制指标

指标名称说明监控来源阈值建议监控SQL
复制延迟从库与主库的延迟时间(s)pg_stat_replication> 30SELECT application_name, ROUND(EXTRACT(EPOCH FROM replay_lag), 2) AS replay_lag_seconds FROM pg_stat_replication;
复制状态复制连接状态pg_stat_replication非streamingSELECT application_name, state FROM pg_stat_replication;
复制槽数量复制槽总数pg_replication_slots异常变化SELECT COUNT(*) FROM pg_replication_slots;
WAL堆积量复制槽未消费的WAL大小pg_replication_slots> 1GBSELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_pending FROM pg_replication_slots;

从库状态监控SQL

sql
-- 从库上执行,查看WAL接收状态
SELECT 
  status, 
  receive_start_lsn, 
  written_lsn, 
  flushed_lsn, 
  last_msg_receipt_time 
FROM pg_stat_wal_receiver;

版本差异

  • PostgreSQL 10+新增了write_lagflush_lagreplay_lag字段,可用于更精确的复制延迟监控
  • PostgreSQL 13+新增了pg_stat_wal视图,可用于WAL生成速率监控

缓存与I/O指标

缓存命中率指标

指标名称说明监控来源阈值建议监控SQL
共享缓冲区命中率共享缓冲区数据命中比例pg_stat_database< 0.9SELECT datname, blks_hit::float / NULLIF(blks_hit + blks_read, 0) AS cache_hit_ratio FROM pg_stat_database;
索引缓冲区命中率索引缓冲区命中比例pg_statio_user_indexes< 0.95SELECT SUM(idx_blks_hit)::float / NULLIF(SUM(idx_blks_hit) + SUM(idx_blks_read), 0) AS idx_hit_ratio FROM pg_statio_user_tables;
表缓冲区命中率表数据缓冲区命中比例pg_statio_user_tables< 0.9SELECT SUM(heap_blks_hit)::float / NULLIF(SUM(heap_blks_hit) + SUM(heap_blks_read), 0) AS heap_hit_ratio FROM pg_statio_user_tables;

I/O操作指标

指标名称说明监控来源阈值建议监控SQL
每秒读取数据块数从磁盘读取的数据块速率pg_stat_database异常波动SELECT datname, blks_read FROM pg_stat_database;
每秒写入数据块数写入磁盘的数据块速率pg_stat_database异常波动SELECT datname, blks_written FROM pg_stat_database;
检查点频率每秒检查点次数pg_stat_bgwriter> 0.1SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
检查点同步时间检查点同步到磁盘的时间pg_stat_bgwriter> 1000msSELECT checkpoint_sync_time FROM pg_stat_bgwriter;

I/O性能监控SQL

sql
SELECT 
  'heap' AS type, 
  SUM(heap_blks_read) AS read_blocks, 
  SUM(heap_blks_hit) AS hit_blocks, 
  SUM(heap_blks_read + heap_blks_hit) AS total_blocks, 
  SUM(heap_blks_hit)::float / NULLIF(SUM(heap_blks_read + heap_blks_hit), 0) AS hit_ratio
FROM pg_statio_user_tables
UNION ALL
SELECT 
  'index' AS type, 
  SUM(idx_blks_read) AS read_blocks, 
  SUM(idx_blks_hit) AS hit_blocks, 
  SUM(idx_blks_read + idx_blks_hit) AS total_blocks, 
  SUM(idx_blks_hit)::float / NULLIF(SUM(idx_blks_read + idx_blks_hit), 0) AS hit_ratio
FROM pg_statio_user_tables;

查询性能指标

查询执行指标

指标名称说明监控来源阈值建议监控SQL
慢查询数量执行时间超过阈值的查询数pg_stat_statements> 10/分钟SELECT COUNT(*) FROM pg_stat_statements WHERE mean_exec_time > 1000;
每秒查询数每秒执行的查询数量pg_stat_database异常波动SELECT datname, tup_returned + tup_fetched AS total_tuples FROM pg_stat_database;
平均查询执行时间查询平均执行时间(ms)pg_stat_statements> 1000SELECT AVG(mean_exec_time) FROM pg_stat_statements;
全表扫描次数执行全表扫描的次数pg_stat_user_tables异常增加SELECT relname, seq_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;

慢查询详情监控SQL

sql
SELECT 
  query, 
  calls, 
  total_exec_time, 
  mean_exec_time, 
  max_exec_time 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

版本差异

  • PostgreSQL 9.4+支持pg_stat_statements扩展,用于查询性能分析
  • PostgreSQL 13+新增了query_id字段,可用于查询指纹识别

执行计划指标

指标名称说明监控来源阈值建议监控SQL
缓冲区命中率查询执行的缓冲区命中比例EXPLAIN ANALYZE< 0.9EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
索引使用率索引被使用的频率pg_stat_user_indexes< 0.1SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 10;
排序操作次数查询执行的排序操作次数pg_stat_database异常增加SELECT datname, tup_returned, tup_fetched FROM pg_stat_database;

存储与表空间指标

数据库大小指标

指标名称说明监控来源阈值建议监控SQL
数据库总大小数据库总大小(包括索引)pg_database_size()异常增长SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;
表大小排名按大小排序的表列表pg_total_relation_size()异常增长SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
索引大小比例索引大小/表总大小pg_indexes_size()> 0.5SELECT schemaname, relname, pg_size_pretty(pg_indexes_size(relid)) AS index_size, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_indexes_size(relid)::float / NULLIF(pg_total_relation_size(relid), 0) AS index_ratio FROM pg_stat_user_tables ORDER BY index_ratio DESC LIMIT 10;

表空间指标

指标名称说明监控来源阈值建议监控SQL
表空间使用率表空间已使用空间比例pg_tablespace_size()> 0.8SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size FROM pg_tablespace;
表空间分布各表空间使用情况pg_tablespace_size()异常分布SELECT tablespace, COUNT(*) AS table_count, pg_size_pretty(SUM(pg_total_relation_size(relid))) AS total_size FROM pg_class GROUP BY tablespace;

表空间使用率监控SQL

sql
-- 结合操作系统命令获取表空间使用率
-- Linux: df -h /path/to/tablespace
-- Windows: wmic logicaldisk get size,freespace,caption

VACUUM与维护指标

VACUUM指标

指标名称说明监控来源阈值建议监控SQL
死元组比例死元组数量/活元组数量pg_stat_user_tables> 0.2SELECT relname, n_dead_tup::float / NULLIF(n_live_tup, 0) AS dead_tuple_ratio FROM pg_stat_user_tables ORDER BY dead_tuple_ratio DESC LIMIT 10;
自动VACUUM执行频率自动VACUUM执行次数pg_stat_user_tables异常频繁SELECT relname, autovacuum_count FROM pg_stat_user_tables ORDER BY autovacuum_count DESC LIMIT 10;
手动VACUUM执行频率手动VACUUM执行次数pg_stat_user_tables异常频繁SELECT relname, vacuum_count FROM pg_stat_user_tables ORDER BY vacuum_count DESC LIMIT 10;
分析执行频率自动分析执行次数pg_stat_user_tables不足SELECT relname, autoanalyze_count FROM pg_stat_user_tables ORDER BY autoanalyze_count ASC LIMIT 10;

需要VACUUM的表监控SQL

sql
SELECT 
  schemaname, 
  relname, 
  n_live_tup, 
  n_dead_tup, 
  ROUND(n_dead_tup::float / NULLIF(n_live_tup, 0) * 100, 2) AS dead_tuple_percent, 
  last_autovacuum, 
  last_autoanalyze 
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000 OR (n_dead_tup::float / NULLIF(n_live_tup, 0) > 0.2) 
ORDER BY dead_tuple_percent DESC;

版本差异

  • PostgreSQL 12+支持并行VACUUM,可通过vacuum_cost_limit参数调整
  • PostgreSQL 13+新增了vacuum_cleanup_index_scale_factor参数,可用于优化索引VACUUM性能

系统资源指标

CPU使用率指标

指标名称说明监控来源阈值建议监控方法
数据库CPU使用率数据库进程CPU使用率操作系统监控> 80%top -p $(pgrep -f postmaster)
系统CPU使用率整体系统CPU使用率操作系统监控> 90%tophtop
CPU负载系统CPU负载操作系统监控> CPU核心数*1.5uptime

内存使用率指标

指标名称说明监控来源阈值建议监控方法
数据库内存使用率数据库进程内存使用率操作系统监控> 90%`ps aux --sort=-rss
系统内存使用率整体系统内存使用率操作系统监控> 90%free -h
共享缓冲区使用率共享缓冲区使用比例pg_settings> 0.9SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%shared_buffers%';

磁盘I/O指标

指标名称说明监控来源阈值建议监控方法
磁盘读写速率磁盘每秒读写数据量操作系统监控接近磁盘最大IOPSiostat -x 1
磁盘IOPS磁盘每秒I/O操作次数操作系统监控接近磁盘最大IOPSiostat -d -x 1
磁盘延迟磁盘I/O操作延迟(ms)操作系统监控> 100iostat -x 1

磁盘空间指标

指标名称说明监控来源阈值建议监控方法
数据目录使用率数据目录所在磁盘使用率操作系统监控> 80%df -h /path/to/data
WAL目录使用率WAL目录所在磁盘使用率操作系统监控> 80%df -h /path/to/wal
表空间磁盘使用率表空间所在磁盘使用率操作系统监控> 80%df -h /path/to/tablespace

WAL与检查点指标

WAL指标

指标名称说明监控来源阈值建议监控SQL
WAL生成速率每秒生成的WAL字节数pg_stat_wal异常增长SELECT wal_records, wal_bytes, wal_buffers_full FROM pg_stat_wal;
WAL写入速率每秒写入的WAL字节数pg_stat_wal异常增长SELECT wal_write, wal_sync FROM pg_stat_wal;
WAL缓冲区使用率WAL缓冲区使用比例pg_stat_wal> 0.9SELECT wal_buffers_full FROM pg_stat_wal;

检查点指标

指标名称说明监控来源阈值建议监控SQL
检查点频率每分钟检查点次数pg_stat_bgwriter> 5SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
检查点写入时间检查点写入操作时间(ms)pg_stat_bgwriter> 5000SELECT checkpoint_write_time, checkpoint_sync_time FROM pg_stat_bgwriter;
检查点同步时间检查点同步操作时间(ms)pg_stat_bgwriter> 1000SELECT checkpoint_sync_time FROM pg_stat_bgwriter;

检查点详细监控SQL

sql
SELECT 
  checkpoints_timed, 
  checkpoints_req, 
  ROUND(checkpoint_write_time / 1000.0, 2) AS write_seconds, 
  ROUND(checkpoint_sync_time / 1000.0, 2) AS sync_seconds, 
  buffers_checkpoint, 
  buffers_clean, 
  buffers_backend 
FROM pg_stat_bgwriter;

版本差异

  • PostgreSQL 13+新增了pg_stat_wal视图,可用于WAL生成速率监控
  • PostgreSQL 14+新增了checkpoint_stats_reset字段,可用于检查点统计重置时间

监控策略与最佳实践

监控频率建议

监控指标类别建议监控频率
连接与事务1分钟
复制状态30秒
缓存与I/O5分钟
慢查询10分钟
存储与表空间30分钟
系统资源1分钟
WAL与检查点5分钟
VACUUM状态15分钟

指标告警规则

  1. 分级告警

    • 警告级别:性能指标偏离正常范围(如缓存命中率<90%)
    • 严重级别:影响业务运行的指标(如复制延迟>30秒)
    • 紧急级别:导致数据库不可用的指标(如磁盘空间使用率>95%)
  2. 告警抑制

    • 同一指标短时间内不重复告警(如5分钟内只告警一次)
    • 相关指标告警合并(如连接数告警与连接拒绝告警合并)
    • 维护期间抑制非紧急告警
  3. 告警升级

    • 15分钟未处理升级至二线工程师
    • 30分钟未处理升级至DBA
    • 60分钟未处理升级至技术负责人

监控数据保留策略

监控数据类型保留时间建议
实时监控数据7天
小时聚合数据30天
天聚合数据1年
慢查询详情30天
告警历史1年
性能基线数据2年

常见监控工具的指标映射

Prometheus + Grafana

常用指标映射

PostgreSQL指标Prometheus指标说明
连接数pg_stat_database_numbackends当前连接数
事务提交数pg_stat_database_xact_commit每秒事务提交数
事务回滚数pg_stat_database_xact_rollback每秒事务回滚数
检查点写入时间pg_stat_bgwriter_checkpoint_write_time检查点写入时间
复制延迟pg_stat_replication_replay_lag从库复制延迟
缓冲区命中率pg_stat_database_blks_hit_ratio共享缓冲区命中率
慢查询数量pg_stat_statements_count慢查询数量

Grafana仪表盘建议

  • 主面板:显示关键指标概览(连接数、事务率、复制延迟、磁盘使用率)
  • 性能面板:显示查询性能、缓存命中率、I/O指标
  • 复制面板:显示复制状态、延迟、WAL堆积情况
  • 存储面板:显示数据库大小、表空间使用率、增长趋势

Zabbix监控

常用监控项

监控项名称键值说明阈值建议
活跃连接数postgres.backends.active当前活跃连接数> max_connections * 0.8
事务数postgres.transactions每秒事务数异常波动
锁数量postgres.locks当前锁数量> 100
复制延迟postgres.replication.delay复制延迟时间> 30秒
磁盘使用率vfs.fs.size[/path/to/data,pused]数据目录使用率> 80%
慢查询数postgres.pgstatstatements.slow慢查询数量> 10/分钟

Zabbix模板建议

  • 使用官方PostgreSQL模板
  • 自定义监控项:添加表空间使用率、VACUUM状态监控
  • 配置触发器:设置合理的告警阈值
  • 配置图形:可视化关键指标趋势

监控案例分析

连接数突增案例

现象

  • 连接数突然增加到max_connections的90%以上
  • 应用响应变慢
  • 出现连接拒绝错误

分析步骤

  1. 查看连接来源分布:SELECT usename, application_name, client_addr, COUNT(*) FROM pg_stat_activity GROUP BY usename, application_name, client_addr ORDER BY COUNT(*) DESC;
  2. 识别异常连接的应用或IP
  3. 分析应用代码,查找连接泄漏问题
  4. 检查连接池配置,确认是否存在配置错误

解决方案

  • 临时增加max_connections:ALTER SYSTEM SET max_connections = 1000; SELECT pg_reload_conf();
  • 优化应用连接池配置:增加最大连接数、调整空闲连接超时
  • 修复应用代码中的连接泄漏问题
  • 配置连接限制:ALTER USER app_user CONNECTION LIMIT 100;

复制延迟增加案例

现象

  • 从库复制延迟逐渐增加,超过30秒
  • 主库WAL生成速率正常
  • 从库CPU使用率较高

分析步骤

  1. 检查从库资源使用情况:top, free -h, iostat -x 1
  2. 分析从库慢查询:SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
  3. 检查从库日志:tail -f /var/log/postgresql/postgresql-14-main.log
  4. 查看复制状态:SELECT application_name, state, replay_lag FROM pg_stat_replication;

解决方案

  • 优化从库慢查询:添加缺失索引、调整查询语句
  • 增加从库资源:升级CPU、增加内存
  • 调整复制参数:ALTER SYSTEM SET max_wal_senders = 10; SELECT pg_reload_conf();
  • 考虑使用级联复制:减少主库复制压力

性能下降案例

现象

  • 查询响应时间增加
  • CPU使用率上升
  • 缓冲区命中率下降

分析步骤

  1. 查看慢查询日志:pgbadger -o slow_queries.html /var/log/postgresql/postgresql-14-main.log
  2. 分析执行计划:EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
  3. 检查锁等待情况:SELECT COUNT(*) FROM pg_locks WHERE NOT granted;
  4. 查看缓存使用情况:SELECT datname, blks_hit::float / NULLIF(blks_hit + blks_read, 0) AS cache_hit_ratio FROM pg_stat_database;

解决方案

  • 优化慢查询:添加缺失索引、重写查询语句
  • 调整内存参数:ALTER SYSTEM SET shared_buffers = '8GB'; SELECT pg_reload_conf();
  • 清理死锁:SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '10 minutes';
  • 执行VACUUM和ANALYZE:VACUUM ANALYZE your_table;

总结

PostgreSQL的监控指标非常丰富,涵盖了数据库的各个方面。通过合理监控这些指标,可以及时发现和解决数据库问题,确保数据库的稳定运行。

在实际监控中,应根据业务需求和数据库规模,选择合适的指标进行监控,并设置合理的告警阈值。同时,定期分析监控数据,优化数据库配置和性能,是数据库运维的重要工作。

建议结合多种监控工具,如Prometheus + Grafana、Zabbix等,构建全面的监控体系,实现对PostgreSQL数据库的全方位监控和管理。此外,定期进行监控体系的评估和优化,确保监控指标的有效性和告警规则的合理性。