PostgreSQL 数据库监控项

在维护和优化 PostgreSQL 数据库时，采集并监控数据库的各种静态和动态指标非常重要。这些指标包括数据库的配置信息、资源使用情况、性能指标等，能够帮助数据库管理员及时发现并解决潜在的问题，从而提高数据库的稳定性和性能。本文提供了一系列 SQL 查询，用于获取 PostgreSQL 数据库的各项重要指标和配置信息。

1. PostgreSQL 版本信息

• 查询命令：

  SELECT version();
  

  返回 PostgreSQL 数据库的版本信息。

2. 配置参数

2.1 shared_buffers

• 查询命令：

  SHOW shared_buffers;
  

  返回 shared_buffers 的配置值。

2.2 max_connections

• 查询命令：

  SHOW max_connections;
  

  返回 max_connections 的配置值。

3. 数据库大小

• 查询命令：

  SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size
  FROM pg_database;
  

  返回每个数据库的名称及其大小。

4. 数据库年龄

• 查询命令：

  SELECT datname, age(datfrozenxid) AS age
  FROM pg_database;
  

  返回每个数据库的名称及其年龄（以事务ID表示）。

5. 活动会话

• 查询命令：

  SELECT datname, count(*) AS num_connections
  FROM pg_stat_activity
  GROUP BY datname;
  

  返回每个数据库当前的活动会话数量。

6. 会话详细信息

• 查询命令：

  SELECT pid, usename, datname, application_name, client_addr, client_port, backend_start, state, query
  FROM pg_stat_activity;
  

  返回当前所有活动会话的详细信息。

7. 数据库大小和索引大小

• 查询命令：

  SELECT
      datname,
      pg_size_pretty(pg_database_size(datname)) AS db_size,
      pg_size_pretty(pg_indexes_size(datname)) AS index_size
  FROM
      pg_database;
  

  返回每个数据库的大小和索引大小。

8. 检查点信息

• 查询命令：

  SELECT checkpoint_time, current_setting('checkpoint_completion_target') AS checkpoint_completion_target
  FROM pg_stat_bgwriter;
  

  返回最近检查点的时间以及 checkpoint_completion_target 的配置值。

9. 真空和分析信息

• 查询命令：

  SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables
  ORDER BY last_autovacuum DESC LIMIT 10;
  

  返回用户表的最后一次 VACUUM 和 ANALYZE 操作的时间。

10. 索引使用情况

• 查询命令：

  SELECT relname AS table_name, indexrelname AS index_name, idx_scan AS index_scans
  FROM pg_stat_user_indexes
  JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
  WHERE schemaname = 'public';
  

  返回用户表的索引使用情况。

11. 表膨胀

• 查询命令：

  SELECT schemaname, tablename, pg_size_pretty(total_bytes) AS total_size,
         pg_size_pretty(heap_bytes) AS heap_size,
         pg_size_pretty(toast_bytes) AS toast_size,
         pg_size_pretty(index_bytes) AS index_size
  FROM (
    SELECT *, total_bytes - heap_bytes - toast_bytes - index_bytes AS table_bytes
    FROM (
      SELECT c.oid, nspname AS schemaname, relname AS tablename,
             pg_total_relation_size(c.oid) AS total_bytes,
             pg_relation_size(c.oid) AS heap_bytes,
             COALESCE(pg_total_relation_size(reltoastrelid), 0) AS toast_bytes,
             COALESCE(SUM(pg_total_relation_size(i.indexrelid)), 0) AS index_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      LEFT JOIN pg_index i ON i.indrelid = c.oid
      WHERE c.relkind = 'r'
      GROUP BY 1, 2, 3, 4, 5, 6
    ) a
  ) b
  ORDER BY total_bytes DESC LIMIT 10;
  

  返回每个表的总大小、堆大小、TOAST 大小和索引大小。

12. 数据库年龄

• 查询命令：

  SELECT datname, age(datfrozenxid) AS age
  FROM pg_database;
  

  返回每个数据库的名称及其年龄。

13. 活动连接数

• 查询命令：

  SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
  

  返回当前活动的连接数。

好的，以下是新增的 10 项 PostgreSQL 数据库的采集项及其对应的 SQL 查询：

14. 缓存命中率

• 查询命令：

  SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS cache_hit_ratio
  FROM pg_stat_database;
  

  返回缓存命中率，接近 1 表示缓存利用率高。

15. 每秒事务数 (TPS)

• 查询命令：

  SELECT
      date_trunc('second', now()) AS time,
      xact_commit + xact_rollback AS tps
  FROM
      pg_stat_database;
  

  返回当前时间每秒事务数（TPS）。

16. 每秒查询数 (QPS)

• 查询命令：

  SELECT
      date_trunc('second', now()) AS time,
      sum(numbackends) AS qps
  FROM
      pg_stat_database;
  

  返回当前时间每秒查询数（QPS）。

17. 死锁数量

• 查询命令：

  SELECT count(*) AS deadlocks
  FROM pg_stat_database_conflicts
  WHERE conflict_type = 'deadlock';
  

  返回数据库中的死锁数量。

18. 当前锁定情况

• 查询命令：

  SELECT locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted
  FROM pg_locks
  WHERE NOT granted;
  

  返回当前未授予的锁定情况。

19. 缓冲区大小

• 查询命令：

  SELECT
      pg_size_pretty(pg_total_relation_size(relid)) AS buffer_size
  FROM
      pg_statio_user_tables;
  

  返回每个用户表的缓冲区大小。

20. 查询计划缓存命中率

• 查询命令：

  SELECT
      round((blks_hit - blks_read)::numeric / blks_hit, 4) AS plan_cache_hit_ratio
  FROM
      pg_stat_database;
  

  返回查询计划缓存命中率。

21. 最长查询时间

• 查询命令：

  SELECT
      max(now() - query_start) AS longest_query_time
  FROM
      pg_stat_activity
  WHERE state = 'active';
  

  返回当前活动查询的最长查询时间。

22. 最大事务数

• 查询命令：

  SELECT
      max(now() - xact_start) AS max_transaction_age
  FROM
      pg_stat_activity
  WHERE state = 'active';
  

  返回当前活动事务的最大事务时间。

23. 索引扫描数

• 查询命令：

  SELECT
      schemaname,
      relname,
      indexrelname,
      idx_scan
  FROM
      pg_stat_user_indexes
  ORDER BY
      idx_scan DESC
  LIMIT 10;
  

  返回索引扫描次数最多的前 10 个索引。

结论

通过上述 SQL 查询，您可以全面地获取 PostgreSQL 数据库的各项静态和动态指标。这些信息有助于数据库的深入监控、维护和优化，确保数据库的高效运行和稳定性。

参考资料

• PostgreSQL Documentation
• Prometheus Documentation
• Postgres Exporter