从零开始学PostgreSQL (八)：监控数据库动态

目录

1、统计信息概述

2、统计信息视图

3、查看锁

4、终止锁定事务

统计信息概述

PostgreSQL 提供了一套丰富的统计信息收集和报告机制，用于监控数据库的运行状况和性能。以下是这些机制的一些关键要点：

统计信息收集配置

• track_activities: 控制对服务器进程当前活动的监控。

• track_counts: 决定是否收集表和索引访问的累积统计信息。

• track_functions: 跟踪用户定义函数的调用和执行时间。

• track_io_timing: 监测块读写时间。

• track_wal_io_timing: 开启对WAL写入时间的监控。

这些参数通常在postgresql.conf文件中设置，但超级用户也可以在会话中动态调整它们。

统计信息存储与持久化

• 统计信息在共享内存中累积，并在适当时间间隔刷新。

• 服务器关闭时，统计信息会保存到pg_stat目录下的子目录，以便重启时恢复。

• 不干净的关闭或特殊启动场景（如立即关闭、服务器崩溃、从基础备份启动或时间点恢复）会导致统计信息计数器重置。

统计信息查看

• 多个预定义视图（如pg_stat_activity、pg_stat_database等）显示当前系统状态。

• 累积统计信息通过其他视图（如pg_stat_user_tables、pg_stat_user_functions等）展示。

• 统计信息可能不是实时更新的，而是以固定频率刷新，这可能导致信息滞后。

• 统计信息在被访问时会被缓存，直到当前事务结束，以保持一致性。

• 特殊权限（如pg_read_all_stats）允许用户查看更全面的统计信息。

动态统计信息与安全限制

统计信息视图

视图名称	描述
pg_stat_activity	显示每个会话的实时活动，包括正在执行的查询、等待状态、会话状态等，是监控和调试运行中的查询的常用工具。
pg_stat_replication	列出所有正在运行的WAL发送者进程，提供流式复制到备用服务器的统计信息，如发送位置、写入位置、复制延迟等。
pg_stat_wal_receiver	显示WAL接收者进程的状态，用于监控从主服务器接收WAL文件的情况。
pg_stat_recovery_prefetch	展示在恢复过程中预取块的统计信息，用于了解恢复期间的预取效率。
pg_stat_subscription	列出所有订阅的统计信息，包括订阅的状态、进度等，对于监控逻辑复制订阅的健康状态至关重要。
pg_stat_ssl	显示每个使用SSL连接的统计信息，如会话状态、SSL版本等，用于监控安全连接的使用情况。
pg_stat_gssapi	提供每个使用GSSAPI进行身份验证和加密的连接的统计信息，用于监控安全连接的健康状态。
pg_stat_progress_analyze	显示正在进行的ANALYZE操作的进度，用于监控索引统计信息的更新过程。
pg_stat_progress_create_index	显示正在进行的CREATE INDEX操作的进度，用于监控索引创建任务的状态。
pg_stat_progress_vacuum	显示正在进行的VACUUM操作的进度，用于监控清理和优化表的进程。
pg_stat_progress_cluster	显示正在进行的CLUSTER操作的进度，用于监控基于索引排序表的进程。
pg_stat_progress_basebackup	显示正在进行的基本备份操作的进度，用于监控流式备份的状态。
pg_stat_progress_copy	显示正在进行的COPY操作的进度，用于监控数据导入导出任务的状态。
pg_stat_archiver	显示WAL归档器进程的统计信息，用于监控WAL文件的归档状态。
pg_stat_bgwriter	显示后台写入器进程的统计信息，用于监控缓冲区的清理和写入磁盘的活动。
pg_stat_database	显示每个数据库的统计信息，包括事务数、回滚数、临时文件使用情况等，用于监控数据库级别的活动。
pg_stat_database_conflicts	显示因恢复冲突而被取消的查询的统计信息，用于监控数据恢复过程中可能遇到的问题。
pg_stat_io	显示I/O操作的统计信息，包括读写次数、时间等，用于监控磁盘I/O的效率。
pg_stat_replication_slots	显示每个复制槽的统计信息，用于监控复制槽的使用情况。
pg_stat_slru	显示每个简单LRU内存管理器的统计信息，用于监控内存管理的效率。
pg_stat_subscription_stats	显示每个订阅的统计信息，包括错误和警告的计数，用于监控订阅的健康状态。
pg_stat_wal	显示WAL操作的统计信息，包括记录数、字节数、写入时间等，用于监控WAL日志的生成和写入活动。
pg_stat_all_tables	显示所有表的统计信息，包括扫描次数、行数、更新次数等，用于监控表级别的活动。
pg_stat_sys_tables	显示系统表的统计信息，与pg_stat_all_tables类似，但仅限于系统表。
pg_stat_user_tables	显示用户表的统计信息，与pg_stat_all_tables类似，但仅限于用户表。
pg_stat_xact_all_tables	显示所有表在当前事务中的统计信息，与pg_stat_all_tables类似，但反映的是事务内的操作。
pg_stat_xact_sys_tables	显示系统表在当前事务中的统计信息，与pg_stat_xact_all_tables类似，但仅限于系统表。
pg_stat_xact_user_tables	显示用户表在当前事务中的统计信息，与pg_stat_xact_all_tables类似，但仅限于用户表。
pg_stat_all_indexes	显示所有索引的统计信息，包括扫描次数、使用次数等，用于监控索引的使用情况。
pg_stat_sys_indexes	显示系统索引的统计信息，与pg_stat_all_indexes类似，但仅限于系统索引。
pg_stat_user_indexes	显示用户索引的统计信息，与pg_stat_all_indexes类似，但仅限于用户索引。
pg_stat_user_functions	显示用户定义函数的统计信息，包括调用次数、执行时间等，用于监控函数的使用情况。
pg_stat_xact_user_functions	显示用户定义函数在当前事务中的统计信息，与pg_stat_user_functions类似，但反映的是事务内的函数调用。
pg_statio_all_tables	显示所有表的I/O统计信息，包括读写次数、字节数等，用于监控表级别的磁盘I/O活动。
pg_statio_sys_tables	显示系统表的I/O统计信息，与pg_statio_all_tables类似，但仅限于系统表。
pg_statio_user_tables	显示用户表的I/O统计信息，与pg_statio_all_tables类似，但仅限于用户表。
pg_statio_all_indexes	显示所有索引的I/O统计信息，用于监控索引的磁盘I/O活动。
pg_statio_sys_indexes	显示系统索引的I/O统计信息，与pg_statio_all_indexes类似，但仅限于系统索引。
pg_statio_user_indexes	显示用户索引的I/O统计信息，与pg_statio_all_indexes类似，但仅限于用户索引。
pg_statio_all_sequences	显示所有序列的I/O统计信息，用于监控序列的磁盘I/O活动。
pg_statio_sys_sequences	显示系统序列的I/O统计信息，与pg_statio_all_sequences类似，但仅限于系统序列。
pg_statio_user_sequences	显示用户序列的I/O统计信息，与pg_statio_all_sequences类似，但仅限于用户序列。

查看锁

在 PostgreSQL 中，pg_locks 系统表是一个非常有用的工具，用于监视数据库活动中的锁管理情况。下面是对 pg_locks 的总结：

用途

pg_locks 表提供了关于当前活跃锁的信息，这对于数据库管理员来说是非常有价值的，因为它可以帮助识别和解决锁相关的性能问题。以下是一些常见的用途：

1. 查看当前未完成的所有锁：这可以帮助你了解哪些类型的锁正在被持有，以及它们的状态（如锁定模式、锁定对象等）。

2. 特定数据库中关系上的所有锁：这有助于确定哪些表或索引正受到锁争用的影响。

3. 特定关系上的所有锁：这可以让你深入了解特定表上的锁争用情况。

4. 特定 PostgreSQL 会话持有的所有锁：这有助于找出哪些会话正在持有锁，以及它们是否是造成性能瓶颈的原因。

如何使用 pg_locks

你可以通过查询 pg_locks 表来获取所需的信息。以下是一些基本的查询示例：

1. 查看所有未完成的锁：

SELECT * FROM pg_locks;

1. 查看特定数据库中关系上的所有锁：

SELECT * FROM pg_locks WHERE database = <database_oid>;

1. 查看特定关系上的所有锁：

SELECT * FROM pg_locks WHERE relation = <relation_oid>;

1. 查看特定会话持有的所有锁：

SELECT * FROM pg_locks WHERE pid = <session_pid>;

5.找出正在执行的事务（等待事务添加 WHERE    NOT l.granted;）

SELECT
    l.locktype,
    l.mode,
    l.transactionid,
    l.classid,
    l.relation,
    l.page,
    l.tuple,
    l.virtualxid,
    l.database,
    l.pid AS locked_pid,
    p.query,
    p.usename
FROM
    pg_locks l
JOIN
    pg_stat_activity p ON l.pid = p.pid

终止锁定事务：

• 一旦你找到了需要终止的事务的PID，你可以使用以下函数来取消或终止该事务：

• pg_cancel_backend(pid)：尝试优雅地取消事务。如果事务正在进行查询，查询将被中断，事务将被回滚。

• pg_terminate_backend(pid)：直接终止事务的后端进程。这将导致事务立即终止，无论它正在进行什么操作

SELECT pg_cancel_backend(542768);
SELECT pg_terminate_backend(542768);

总之，PostgreSQL的统计信息收集和报告机制提供了深入的数据库性能洞察，同时考虑了实时性、一致性和安全性。通过合理配置和使用这些工具，数据库管理员可以有效地监控和优化数据库性能。

上一篇：从零开始学PostgreSQL (七)：高可用性、负载平衡和复制

从零开始学PostgreSQL (六)：备份和恢复

从零开始学PostgreSQL (五)：日常数据库维护任务

从零开始学PostgreSQL (四)：数据库角色

从零开始学PostgreSQL (三)：索引篇

从零开始学PostgreSQL (二)：配置文件

从零开始学PostgreSQL (一)：Centos8 RPM安装PostgreSQL16

公众号内直接回复加群也可以,扫码加群也可以，觉得帖子写的不错，点点关注，点点赞，多多转载，请多多支持。