印刻数据库

外观

PostgreSQL 数据库级别指标 ​

PostgreSQL 数据库级别指标是数据库监控的核心，包括连接数、事务指标、查询性能、缓存命中率等。这些指标直接反映数据库的运行状态和性能表现，是DBA进行性能优化和故障排查的重要依据。

数据库级别指标类型 ​

1. 连接数指标 ​

  • 总连接数：当前数据库的总连接数
  • 活跃连接数：当前正在执行查询的连接数
  • 空闲连接数：当前空闲的连接数
  • 等待连接数：等待获取连接的连接数
  • 最大连接数：数据库允许的最大连接数
  • 连接使用率：当前连接数与最大连接数的比值

2. 事务指标 ​

  • 事务提交数：每秒提交的事务数量（TPS）
  • 事务回滚数：每秒回滚的事务数量
  • 事务成功率：提交事务数与总事务数的比值
  • 长事务数量：运行时间超过阈值的事务数量
  • 长事务平均运行时间：长事务的平均运行时间

3. 查询性能指标 ​

  • 查询次数：每秒执行的查询数量（QPS）
  • 慢查询数：每秒执行的慢查询数量
  • 平均查询响应时间：所有查询的平均响应时间
  • 查询计划命中率：计划缓存的命中率
  • 临时文件使用率：查询生成的临时文件数量

4. 缓存指标 ​

  • 共享缓冲区命中率：共享缓冲区的命中率
  • 工作内存使用率：工作内存的使用率
  • 计划缓存命中率：计划缓存的命中率
  • 操作系统缓存命中率：操作系统缓存的命中率
  • 有效缓存命中率：共享缓冲区和操作系统缓存的综合命中率

5. 锁指标 ​

  • 锁数量：当前数据库中的锁数量
  • 等待锁的查询数：等待获取锁的查询数量
  • 锁等待时间：查询等待锁的平均时间
  • 死锁数量：发生死锁的数量

指标收集方法 ​

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,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) AS connection_usage_percent
FROM pg_stat_activity;

-- 2. 查询事务指标
SELECT 
  datname,
  xact_commit AS transactions_committed,
  xact_rollback AS transactions_rolled_back,
  round(100.0 * xact_commit / nullif(xact_commit + xact_rollback, 0), 2) AS transaction_success_rate
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');

-- 3. 查询查询性能指标
SELECT 
  datname,
  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,
  blks_read AS blocks_read,
  blks_hit AS blocks_hit,
  round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS buffer_hit_ratio
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');

-- 4. 查询长事务
SELECT 
  pid,
  datname,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > INTERVAL '5 minutes' 
ORDER BY duration DESC;

-- 5. 查询锁信息
SELECT 
  locktype,
  database,
  relation::regclass,
  mode,
  granted,
  pid,
  usename,
  query
FROM pg_locks 
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid 
WHERE NOT granted 
ORDER BY query_start;

2. 扩展工具 ​

使用 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;

3. 监控工具 ​

使用专业监控工具收集和分析数据库级别指标。

Prometheus + PostgreSQL Exporter ​

yaml
# 1. PostgreSQL Exporter 配置
# 下载并安装 PostgreSQL Exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.14.0/postgres_exporter-0.14.0.linux-amd64.tar.gz
tar xvfz postgres_exporter-0.14.0.linux-amd64.tar.gz
cd postgres_exporter-0.14.0.linux-amd64

# 2. 创建数据库用户和配置
CREATE USER postgres_exporter WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT SELECT ON pg_stat_database TO postgres_exporter;

# 3. 启动 PostgreSQL Exporter
export DATA_SOURCE_NAME="postgresql://postgres_exporter:password@localhost:5432/postgres?sslmode=disable"
./postgres_exporter

# 4. Prometheus 配置
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']
    scrape_interval: 15s

关键指标监控 ​

1. 连接数监控 ​

sql
-- 1. 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 2. 查看连接状态分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- 3. 监控连接使用率的告警设置
-- 当连接使用率超过 80% 时触发告警
-- Prometheus 告警规则示例
ALERT HighConnectionUsage
  IF (sum by(instance) (pg_stat_activity_count{datname!~"template.*"}) / pg_settings_max_connections * 100) > 80
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "High connection usage on {{ $labels.instance }}",
    description = "Connection usage is {{ $value }}% for 5 minutes"
  }

2. 事务监控 ​

sql
-- 1. 查看事务提交和回滚率
SELECT 
  datname,
  xact_commit,
  xact_rollback,
  round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 2) AS rollback_rate
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');

-- 2. 监控事务回滚率的告警设置
-- 当事务回滚率超过 10% 时触发告警
-- Prometheus 告警规则示例
ALERT HighTransactionRollbackRate
  IF (sum by(instance, datname) (rate(pg_stat_database_xact_rollback[5m])) / sum by(instance, datname) (rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m]))) * 100 > 10
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "High transaction rollback rate on {{ $labels.instance }} {{ $labels.datname }}",
    description = "Transaction rollback rate is {{ $value }}% for 5 minutes"
  }

3. 缓存命中率监控 ​

sql
-- 1. 查看共享缓冲区命中率
SELECT 
  datname,
  round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS buffer_hit_ratio
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');

-- 2. 监控缓存命中率的告警设置
-- 当缓存命中率低于 95% 时触发告警
-- Prometheus 告警规则示例
ALERT LowBufferHitRatio
  IF (sum by(instance, datname) (rate(pg_stat_database_blks_hit[5m])) / sum by(instance, datname) (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m]))) * 100 < 95
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "Low buffer hit ratio on {{ $labels.instance }} {{ $labels.datname }}",
    description = "Buffer hit ratio is {{ $value }}% for 5 minutes"
  }

4. 慢查询监控 ​

sql
-- 1. 查看慢查询日志配置
SHOW log_min_duration_statement;

-- 2. 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = '1000ms';

-- 3. 查看慢查询
SELECT 
  pid,
  datname,
  usename,
  application_name,
  client_addr,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > INTERVAL '1 second' 
ORDER BY duration DESC;

-- 4. 监控慢查询的告警设置
-- 当慢查询数超过 5 个时触发告警
-- Prometheus 告警规则示例
ALERT HighSlowQueryCount
  IF sum by(instance, datname) (rate(pg_stat_database_conflicts[5m])) > 5
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "High slow query count on {{ $labels.instance }} {{ $labels.datname }}",
    description = "Slow query count is {{ $value }} for 5 minutes"
  }

最佳实践 ​

1. 生产环境监控建议 ​

  • 设置合理的告警阈值：根据业务需求和系统配置设置合适的告警阈值
  • 监控关键指标：重点监控连接数、事务指标、缓存命中率、慢查询等关键指标
  • 建立基线：建立正常运行时的指标基线，便于识别异常情况
  • 定期分析监控数据：定期分析历史监控数据，识别性能趋势和潜在问题
  • 配置多级别告警：根据问题严重程度设置不同级别的告警

2. 性能优化建议 ​

sql
-- 1. 连接数优化
-- 调整最大连接数
ALTER SYSTEM SET max_connections = '200';

-- 配置连接池
-- 使用 PgBouncer 或 Odyssey 等连接池工具

-- 2. 内存配置优化
-- 调整共享缓冲区大小（建议为系统内存的 25-30%）
ALTER SYSTEM SET shared_buffers = '4GB';

-- 调整工作内存
ALTER SYSTEM SET work_mem = '16MB';

-- 调整 maintenance 工作内存
ALTER SYSTEM SET maintenance_work_mem = '512MB';

-- 3. 事务优化
-- 避免长事务
-- 合理使用事务隔离级别
-- 及时提交或回滚事务

-- 4. 查询优化
-- 优化慢查询语句
-- 合理创建索引
-- 使用 EXPLAIN ANALYZE 分析查询计划

3. 监控工具选择 ​

工具名称适用场景优点缺点
Prometheus + PostgreSQL Exporter大规模分布式环境开源、灵活、强大的查询语言需要手动配置告警规则
pgAdmin单实例监控官方工具、易于使用功能相对简单
Datadog云原生环境易于部署、强大的分析功能商业化产品，成本高
New Relic应用性能监控与应用深度集成专注于应用监控
Zabbix企业级监控全面的监控功能配置复杂、资源消耗大

常见问题处理 ​

1. 连接数过多 ​

问题：数据库连接数达到最大限制，新连接无法建立

解决方法：

  • 增加最大连接数配置
  • 优化应用程序，减少不必要的连接
  • 使用连接池工具，如 PgBouncer 或 Odyssey
  • 关闭空闲连接

2. 事务回滚率过高 ​

问题：事务回滚率过高，影响数据库性能

解决方法：

  • 检查应用程序代码，找出导致事务回滚的原因
  • 优化事务逻辑，减少事务冲突
  • 合理设置事务隔离级别
  • 增加死锁检测频率

3. 缓存命中率过低 ​

问题：数据库缓存命中率过低，导致大量磁盘 I/O

解决方法：

  • 增加 shared_buffers 配置
  • 优化查询，减少磁盘 I/O 操作
  • 考虑使用 SSD 存储
  • 分析缓存使用情况，找出缓存未命中的原因

4. 慢查询过多 ​

问题：数据库中存在大量慢查询，影响整体性能

解决方法：

  • 启用慢查询日志，找出慢查询语句
  • 使用 EXPLAIN ANALYZE 分析查询计划
  • 优化查询语句，添加必要的索引
  • 考虑使用查询重写或物化视图

常见问题（FAQ） ​

Q1：如何确定数据库级指标的正常范围？ ​

A1：数据库级指标的正常范围取决于数据库配置和业务负载，需要：

  • 建立基线：在数据库正常运行时收集指标数据，建立基线
  • 参考行业标准：如共享缓冲区命中率一般应高于 95%，连接使用率应低于 80%
  • 根据业务需求调整：不同业务对数据库性能的要求不同

Q2：如何监控 PostgreSQL 数据库的连接数？ ​

A2：可以使用以下方法监控连接数：

  • 查询 pg_stat_activity 视图
  • 使用监控工具如 Prometheus + PostgreSQL Exporter
  • 配置连接数告警

Q3：如何优化 PostgreSQL 数据库的缓存命中率？ ​

A3：可以采取以下措施：

  • 增加 shared_buffers 配置
  • 优化查询，减少磁盘 I/O 操作
  • 合理设置 work_mem 配置
  • 考虑使用 SSD 存储

Q4：如何处理 PostgreSQL 中的长事务？ ​

A4：处理长事务可以采取以下措施：

  • 识别长事务：使用 pg_stat_activity 视图查询长事务
  • 分析长事务原因：检查应用程序代码，找出导致长事务的原因
  • 优化事务逻辑：将长事务拆分为多个短事务
  • 设置事务超时：配置 idle_in_transaction_session_timeout 参数

Q5：如何监控 PostgreSQL 中的慢查询？ ​

A5：监控慢查询可以采取以下措施：

  • 启用慢查询日志：设置 log_min_duration_statement 参数
  • 使用 pg_stat_statements 扩展：收集和分析慢查询
  • 使用专业监控工具：如 Prometheus + PostgreSQL Exporter
  • 配置慢查询告警：当慢查询数超过阈值时触发告警

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

A6：主要区别如下：

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

Q7：如何优化 PostgreSQL 数据库的事务性能？ ​

A7：优化事务性能可以采取以下措施：

  • 避免长事务
  • 合理设置事务隔离级别
  • 及时提交或回滚事务
  • 减少事务中的锁竞争
  • 考虑使用乐观锁机制

Q8：如何监控 PostgreSQL 数据库的锁情况？ ​

A8：监控锁情况可以采取以下措施：

  • 查询 pg_locks 视图
  • 使用 pg_stat_activity 视图关联查询锁信息
  • 配置锁等待告警：当锁等待时间超过阈值时触发告警
  • 使用专业监控工具：如 Prometheus + PostgreSQL Exporter