统计信息概述
PostgreSQL 提供了一套丰富的统计信息收集和报告机制,用于监控数据库的运行状况和性能。以下是这些机制的一些关键要点:
统计信息收集配置
这些参数通常在postgresql.conf文件中设置,但超级用户也可以在会话中动态调整它们。
统计信息存储与持久化
统计信息查看
动态统计信息与安全限制
统计信息视图
视图名称 | 描述 |
---|---|
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 表提供了关于当前活跃锁的信息,这对于数据库管理员来说是非常有价值的,因为它可以帮助识别和解决锁相关的性能问题。以下是一些常见的用途:
如何使用 pg_locks
你可以通过查询 pg_locks 表来获取所需的信息。以下是一些基本的查询示例:
SELECT * FROM pg_locks;
SELECT * FROM pg_locks WHERE database = <database_oid>;
SELECT * FROM pg_locks WHERE relation = <relation_oid>;
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
终止锁定事务:
SELECT pg_cancel_backend(542768);
SELECT pg_terminate_backend(542768);
总之,PostgreSQL的统计信息收集和报告机制提供了深入的数据库性能洞察,同时考虑了实时性、一致性和安全性。通过合理配置和使用这些工具,数据库管理员可以有效地监控和优化数据库性能。