PostgreSQL 数据库监控项

发布于:2024-07-11 ⋅ 阅读:(17) ⋅ 点赞:(0)

在维护和优化 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 数据库的各项静态和动态指标。这些信息有助于数据库的深入监控、维护和优化,确保数据库的高效运行和稳定性。

参考资料