印刻数据库

外观

PostgreSQL 监控数据收集策略 ​

PostgreSQL 监控数据收集策略是确保数据库稳定运行的重要基础，通过合理收集和分析监控数据，可以及时发现性能瓶颈、预测故障风险、优化数据库配置。

监控数据类型 ​

1. 系统层面指标 ​

  • CPU 使用率：包括总体 CPU 使用率、用户态 CPU 使用率、系统态 CPU 使用率
  • 内存使用：包括内存总量、已使用内存、缓存使用情况、swap 使用率
  • 磁盘 I/O：包括磁盘读写速率、IOPS、磁盘使用率、磁盘延迟
  • 网络：包括网络吞吐量、连接数、网络延迟

2. PostgreSQL 层面指标 ​

  • 连接数：包括总连接数、活跃连接数、空闲连接数、最大连接数
  • 事务指标：包括事务提交数、回滚数、事务速率
  • 查询性能：包括 QPS、TPS、慢查询数、平均查询响应时间
  • 缓存命中率：包括共享缓冲区命中率、工作内存使用率、计划缓存命中率

3. WAL 与复制指标 ​

  • WAL 生成速率：包括 WAL 写入速率、WAL 文件数量
  • 复制延迟：包括主从复制延迟、备库追赶速率
  • 复制状态：包括复制槽状态、WAL 发送者/接收器状态

4. 表与索引指标 ​

  • 表大小：包括表数据大小、索引大小、TOAST 表大小
  • 索引使用情况：包括索引命中率、未使用索引
  • 真空状态：包括 autovacuum 运行情况、死元组数、膨胀率

监控数据收集方法 ​

1. 内置视图查询 ​

PostgreSQL 提供了丰富的内置视图，可以直接查询各种监控指标。

sql
-- 1. 查询连接数
SELECT 
  count(*) AS total_connections,
  sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active_connections,
  sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle_connections
FROM pg_stat_activity;

-- 2. 查询缓存命中率
SELECT 
  round(100 * (blks_hit / (blks_hit + blks_read)), 2) AS buffer_cache_hit_ratio
FROM pg_stat_database;

-- 3. 查询事务指标
SELECT 
  xact_commit AS commits,
  xact_rollback AS rollbacks,
  tup_returned AS tuples_returned,
  tup_fetched AS tuples_fetched,
  tup_inserted AS tuples_inserted,
  tup_updated AS tuples_updated,
  tup_deleted AS tuples_deleted
FROM pg_stat_database WHERE datname = 'mydb';

-- 4. 查询WAL指标
SELECT 
  wal_written / 1024 / 1024 AS wal_mb_written,
  wal_written_time AS wal_write_time
FROM pg_stat_wal;

-- 5. 查询表膨胀情况
SELECT 
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_tuple_ratio
FROM pg_stat_user_tables;

2. 系统命令 ​

使用系统命令收集操作系统层面的监控数据。

bash
# 1. 收集CPU使用率
mpstat 1 5

# 2. 收集内存使用情况
free -h

# 3. 收集磁盘I/O
iostat -xdk 1 5

# 4. 收集网络情况
tcpdump -i eth0 -c 100
netstat -an | grep ESTABLISHED | wc -l

# 5. 收集PostgreSQL进程信息
top -p $(pgrep -d ',' postgres)

3. 扩展工具 ​

使用 PostgreSQL 扩展工具收集更详细的监控数据。

pg_stat_statements ​

sql
-- 1. 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 2. 配置参数
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track_utility = 'on';

-- 3. 查询慢查询
SELECT 
  queryid,
  query,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

pg_stat_monitor ​

sql
-- 1. 安装扩展（需要从PGDG仓库安装）
-- yum install postgresql14-pg-stat-monitor

-- 2. 配置参数
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';
ALTER SYSTEM SET pg_stat_monitor.pgsm_max = 10000;
ALTER SYSTEM SET pg_stat_monitor.pgsm_track = 'all';

-- 3. 查询监控数据
SELECT 
  query,
  calls,
  total_time,
  min_time,
  max_time,
  mean_time,
  rows
FROM pg_stat_monitor 
ORDER BY total_time DESC 
LIMIT 10;

监控数据收集工具 ​

1. 开源监控工具 ​

工具名称特点适用场景
Prometheus + Grafana强大的数据收集和可视化能力，支持告警大规模集群监控，需要实时可视化
Zabbix全面的监控功能，支持多种监控方式企业级监控，需要集成多种系统
Nagios传统监控工具，插件丰富简单监控场景，已有Nagios环境
pgAdminPostgreSQL 官方管理工具，内置监控功能单实例监控，简单部署

2. 商业监控工具 ​

  • Datadog：云原生监控平台，支持 PostgreSQL 深度集成
  • New Relic：应用性能监控，支持 PostgreSQL 监控
  • SolarWinds：全面的 IT 监控解决方案
  • AppDynamics：应用性能管理，支持数据库监控

监控数据收集策略 ​

1. 采样频率设置 ​

指标类型建议采样频率说明
系统层面指标1-5秒系统指标变化较快，需要高频采样
PostgreSQL 连接数5-10秒连接数变化可能较快，影响系统稳定性
查询性能指标10-30秒QPS、TPS等指标需要适中的采样频率
缓存命中率1-5分钟缓存命中率变化相对较慢
表与索引指标5-15分钟表大小、索引使用情况变化较慢
WAL 与复制指标1-5秒复制延迟需要实时监控

2. 数据存储策略 ​

bash
# 1. Prometheus 配置示例（prometheus.yml）
global:
  scrape_interval: 15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metrics_path: '/metrics'
    scrape_interval: 5s

# 2. 数据保留策略
retention:
  time: 15d
  size: 10GB

3. 监控数据收集最佳实践 ​

  • 分层监控：从系统层、数据库层、应用层进行分层监控
  • 重点突出：针对关键指标进行重点监控，如连接数、慢查询、复制延迟
  • 历史对比：建立基线数据，进行历史对比分析
  • 告警联动：监控数据与告警系统联动，及时发现问题
  • 自动化：实现监控数据收集的自动化配置和管理

性能优化建议 ​

1. 减少监控对数据库的影响 ​

sql
-- 1. 优化监控查询
-- 避免在监控查询中使用复杂的聚合操作
-- 限制返回结果数量
SELECT * FROM pg_stat_activity LIMIT 100;

-- 2. 合理设置采样频率
-- 高频采样可能影响数据库性能，根据实际情况调整

-- 3. 使用只读副本进行监控
-- 将监控查询路由到只读副本，减轻主库压力

2. 优化监控数据存储 ​

bash
# 1. 压缩监控数据
# Prometheus 支持数据压缩，减少存储空间

# 2. 分层存储
# 近期数据保留在高性能存储，历史数据迁移到低成本存储

# 3. 数据降采样
# 对历史数据进行降采样，减少存储量

常见问题处理 ​

1. 监控数据收集对数据库性能影响过大 ​

问题：监控查询导致数据库性能下降

解决方法：

  • 降低采样频率
  • 优化监控查询语句
  • 将监控查询路由到只读副本
  • 使用轻量级监控工具

2. 监控数据丢失 ​

问题：监控数据不完整或丢失

解决方法：

  • 检查监控工具配置
  • 确保监控服务正常运行
  • 增加监控数据的冗余存储
  • 配置监控工具的高可用性

3. 监控告警过多 ​

问题：收到大量无效告警，导致告警疲劳

解决方法：

  • 调整告警阈值，避免误报
  • 配置告警抑制和聚合规则
  • 对告警进行分级，只关注重要告警
  • 建立告警升级机制

常见问题（FAQ） ​

Q1：如何选择合适的监控数据采样频率？ ​

A1：采样频率的选择需要平衡监控精度和系统开销：

  • 对于变化较快的指标（如CPU、内存、复制延迟），建议采样频率为1-5秒
  • 对于变化较慢的指标（如表大小、索引使用情况），建议采样频率为5-15分钟
  • 可以根据业务重要性调整不同实例的采样频率

Q2：如何减少监控对数据库性能的影响？ ​

A2：可以采取以下措施：

  • 优化监控查询语句，避免复杂聚合操作
  • 降低非关键指标的采样频率
  • 将监控查询路由到只读副本
  • 使用轻量级监控工具
  • 限制监控查询的资源使用

Q3：如何存储大量的监控数据？ ​

A3：可以采用以下存储策略：

  • 使用专门的时序数据库（如Prometheus、InfluxDB）存储监控数据
  • 配置合理的数据保留期限，如近期数据保留15天，历史数据降采样后保留1年
  • 实现数据分层存储，近期数据存储在高性能存储，历史数据迁移到低成本存储
  • 对监控数据进行压缩，减少存储空间

Q4：如何选择合适的监控工具？ ​

A4：选择监控工具需要考虑以下因素：

  • 监控规模：小规模环境可以使用简单工具，大规模环境需要强大的分布式监控系统
  • 功能需求：是否需要实时可视化、告警、历史数据分析等功能
  • 技术栈：与现有技术栈的兼容性
  • 预算：开源工具成本低，商业工具功能更全面
  • 运维复杂度：考虑工具的部署和维护成本

Q5：如何建立有效的监控告警机制？ ​

A5：建立有效告警机制需要：

  • 确定关键监控指标和告警阈值
  • 对告警进行分级（如P0、P1、P2）
  • 配置告警通知方式（如邮件、短信、钉钉、Slack）
  • 建立告警响应流程和升级机制
  • 定期回顾和调整告警规则，减少误报

Q6：如何利用监控数据进行性能优化？ ​

A6：可以通过以下方式利用监控数据优化性能：

  • 分析慢查询日志，优化查询语句和索引
  • 根据缓存命中率调整shared_buffers配置
  • 根据连接数调整max_connections配置
  • 根据WAL生成速率调整checkpoint配置
  • 根据复制延迟优化复制配置

Q7：pg_stat_statements 和 pg_stat_monitor 有什么区别？ ​

A7：主要区别如下：

  • pg_stat_statements 是 PostgreSQL 官方扩展，稳定性更好
  • pg_stat_monitor 提供更丰富的监控指标，如查询执行计划、绑定变量信息
  • pg_stat_monitor 支持查询采样和统计信息的聚合
  • pg_stat_monitor 对高并发环境的性能影响更小
  • pg_stat_monitor 需要从第三方仓库安装，而 pg_stat_statements 是内置扩展

Q8：如何监控 PostgreSQL 集群的复制状态？ ​

A8：可以通过以下方式监控复制状态：

  • 查询 pg_stat_replication 视图获取复制状态信息
  • 查询 pg_stat_wal 视图获取 WAL 生成和发送情况
  • 查询 pg_replication_slots 视图获取复制槽状态
  • 使用监控工具（如Prometheus + Grafana）可视化复制延迟
  • 配置复制延迟告警，及时发现复制问题