pg_stat_kcache: 收集查询的 I/O 和 CPU 统计信息

二月 5, 2024

摘要pg_stat_kcache扩展可收集数据库后端产生的有关物理磁盘访问和 CPU 消耗的统计信息。

安装

要求

pg_stat_kcache以一个扩展的形式提供,支持 PostgreSQL 9.4 或更高版本,并且需要已经安装好 pg_stat_statements 扩展。需要 PostgreSQL 9.4 或更高版本,因为以前版本提供的 pg_stat_statements 没有提供 queryid 字段。

从 PGDG 软件仓库

如果您从 PGDG 软件仓库(Debian/Ubuntu <https://apt.postgresql.org> 上的 APT 或 RHEL/Rocky <https://yum.postgresql.org> 上的 YUM)安装了 PostgreSQL,那么推荐的安装方法是从相同的软件仓库去获取它。

对于 Debian/Ubuntu:

apt install postgresql-XY-pg-stat-kcache

和 RHEL/Rocky:

yum install pg_stat_kcacheXY

或者对 PostgreSQL 11 及更高版本:

yum install pg_stat_kcache_XY

(其中 XY 是您的 PostgreSQL 主版本编号)

编译

该模块可以使用标准的 PGXS 机制来构建。为此,pg_config程序必须在您的$PATH配置中可用。安装步骤如下:

git clone https://github.com/powa-team/pg_stat_kcache.git
cd pg_stat_kcache
make
make install

PostgreSQL 设置

现在可以使用该扩展了。但是,由于它需要一些共享内存来保存其计数器,因此必须在 PostgreSQL 启动时加载该模块。因此,您必须在postgresql.conf中将模块添加到shared_preload_libraries。您需要重新启动服务器,才能应用配置更改。由于此扩展需要依赖 pg_stat_statements,因此还需要将其添加到shared_preload_libraries

将以下参数添加到您的postgresql.conf中:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'

重新启动 PostgreSQL 实例后,您可以在需要访问统计信息的每个数据库中安装该扩展:

mydb=# CREATE EXTENSION pg_stat_kcache;

配置

可以在postgresql.conf中,配置以下 GUC 参数:

  • pg_stat_kcache.linux_hz(整型,默认值 -1):告知pg_stat_kcache在 linux 中的 CONFIG_HZ 配置选项值。这用于pg_stat_kcache补偿采样误差。默认值为 -1,尝试在启动时猜测它。
  • pg_stat_kcache.track(枚举型,默认 top):控制pg_stat_kcache跟踪哪些语句。指定 top 以跟踪顶层语句(由客户端直接发出的语句),指定 all 以跟踪包括嵌套语句在内的所有语句(例如在函数中调用的语句),或指定 none 以禁用语句统计信息收集。
  • pg_stat_kcache.track_planning(布尔型,默认 off):控制pg_stat_kcache是否跟踪规划的操作和持续时间(需要 PostgreSQL 13 或更高版本)。

用法

pg_stat_kcache会创建多个对象。

pg_stat_kcache 视图

名称 类型 描述
datname name 数据库的名称
plan_user_time double precision 在此数据库中规划语句时使用的用户层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_system_time double precision 在此数据库中规划语句时使用的系统层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_minflts bigint 在此数据库中规划语句时发生的页面回收(软页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_majflts bigint 在此数据库中规划语句时发生的页面错误(硬页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nswaps bigint 在此数据库中规划语句时发生的换入换出次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads bigint 在此数据库中规划语句时由文件系统层读取的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads_blks bigint 在此数据库中规划语句时由文件系统层读取的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes bigint 在此数据库中规划语句时由文件系统层写入的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes_blks bigint 在此数据库中规划语句时由文件系统层写入的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgsnds bigint 在此数据库中规划语句时发送的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgrcvs bigint 在此数据库中规划语句时收到的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nsignals bigint 在此数据库中规划语句时接收的信号数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nvcsws bigint 在此数据库中规划语句时主动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nivcsws bigint 在此数据库中规划语句时被动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
exec_user_time double precision 在此数据库中执行语句时所用的用户层 CPU 时间(以秒和毫秒为单位)
exec_system_time double precision 在此数据库中执行语句时所用的系统层 CPU 时间(以秒和毫秒为单位)
exec_minflts bigint 在此数据库中执行语句时发生的页面回收(软页面错误)次数
exec_majflts bigint 在此数据库中执行语句时发生的页面错误(硬页面错误)次数
exec_nswaps bigint 在此数据库中执行语句时发生的换入换出次数
exec_reads bigint 在此数据库中执行语句时由文件系统层读取的字节数
exec_reads_blks bigint 在此数据库中执行语句时由文件系统层读取的 8K 块数
exec_writes bigint 在此数据库中执行语句时由文件系统层写入的字节数
exec_writes_blks bigint 在此数据库中执行语句时由文件系统层写入的 8K 块数
exec_msgsnds bigint 在此数据库中执行语句时发送的 IPC 消息数
exec_msgrcvs bigint 在此数据库中执行语句时收到的 IPC 消息数
exec_nsignals bigint 在此数据库中执行语句时接收的信号数
exec_nvcsws bigint 在此数据库中执行语句时主动发生的上下文切换次数
exec_nivcsws bigint 在此数据库中执行语句时被动发生的上下文切换次数

pg_stat_kcache_detail 视图

名称 类型 描述
query text 查询文本
top bool 如果语句是顶层的,则为 True
datname name 数据库的名称
rolname name 角色名称
plan_user_time double precision 在此数据库中规划语句时使用的用户层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_system_time double precision 在此数据库中规划语句时使用的系统层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_minflts bigint 在此数据库中规划语句时发生的页面回收(软页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_majflts bigint 在此数据库中规划语句时发生的页面错误(硬页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nswaps bigint 在此数据库中规划语句时发生的换入换出次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads bigint 在此数据库中规划语句时由文件系统层读取的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads_blks bigint 在此数据库中规划语句时由文件系统层读取的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes bigint 在此数据库中规划语句时由文件系统层写入的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes_blks bigint 在此数据库中规划语句时由文件系统层写入的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgsnds bigint 在此数据库中规划语句时发送的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgrcvs bigint 在此数据库中规划语句时收到的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nsignals bigint 在此数据库中规划语句时接收的信号数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nvcsws bigint 在此数据库中规划语句时主动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nivcsws bigint 在此数据库中规划语句时被动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
exec_user_time double precision 在此数据库中执行语句时所用的用户层 CPU 时间(以秒和毫秒为单位)
exec_system_time double precision 在此数据库中执行语句时所用的系统层 CPU 时间(以秒和毫秒为单位)
exec_minflts bigint 在此数据库中执行语句时发生的页面回收(软页面错误)次数
exec_majflts bigint 在此数据库中执行语句时发生的页面错误(硬页面错误)次数
exec_nswaps bigint 在此数据库中执行语句时发生的换入换出次数
exec_reads bigint 在此数据库中执行语句时由文件系统层读取的字节数
exec_reads_blks bigint 在此数据库中执行语句时由文件系统层读取的 8K 块数
exec_writes bigint 在此数据库中执行语句时由文件系统层写入的字节数
exec_writes_blks bigint 在此数据库中执行语句时由文件系统层写入的 8K 块数
exec_msgsnds bigint 在此数据库中执行语句时发送的 IPC 消息数
exec_msgrcvs bigint 在此数据库中执行语句时收到的 IPC 消息数
exec_nsignals bigint 在此数据库中执行语句时接收的信号数
exec_nvcsws bigint 在此数据库中执行语句时主动发生的上下文切换次数
exec_nivcsws bigint 在此数据库中执行语句时被动发生的上下文切换次数

pg_stat_kcache_reset 函数

重置 pg_stat_kcache 收集的统计信息。可以由超级用户调用:

pg_stat_kcache_reset()

pg_stat_kcache 函数

此函数是一个集合返回函数,用于输出共享内存结构中计数器的内容。pg_stat_kcache 视图会使用此函数。该函数可以被任何用户调用:

SELECT * FROM pg_stat_kcache();

它提供以下列:

名称 类型 描述
queryid bigint pg_stat_statements 中的查询标识符
top bool 如果语句是顶层的,则为 True
userid oid 用户 OID
dbid oid 数据库 OID
plan_user_time double precision 在此数据库中规划语句时使用的用户层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_system_time double precision 在此数据库中规划语句时使用的系统层 CPU 时间(以秒和毫秒为单位)(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_minflts bigint 在此数据库中规划语句时发生的页面回收(软页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_majflts bigint 在此数据库中规划语句时发生的页面错误(硬页面错误)次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nswaps bigint 在此数据库中规划语句时发生的换入换出次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads bigint 在此数据库中规划语句时由文件系统层读取的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_reads_blks bigint 在此数据库中规划语句时由文件系统层读取的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes bigint 在此数据库中规划语句时由文件系统层写入的字节数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_writes_blks bigint 在此数据库中规划语句时由文件系统层写入的 8K 块数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgsnds bigint 在此数据库中规划语句时发送的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_msgrcvs bigint 在此数据库中规划语句时收到的 IPC 消息数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nsignals bigint 在此数据库中规划语句时接收的信号数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nvcsws bigint 在此数据库中规划语句时主动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
plan_nivcsws bigint 在此数据库中规划语句时被动发生的上下文切换次数(需要启用 pg_stat_kcache.track_planning,否则为零)
exec_user_time double precision 在此数据库中执行语句时所用的用户层 CPU 时间(以秒和毫秒为单位)
exec_system_time double precision 在此数据库中执行语句时所用的系统层 CPU 时间(以秒和毫秒为单位)
exec_minflts bigint 在此数据库中执行语句时发生的页面回收(软页面错误)次数
exec_majflts bigint 在此数据库中执行语句时发生的页面错误(硬页面错误)次数
exec_nswaps bigint 在此数据库中执行语句时发生的换入换出次数
exec_reads bigint 在此数据库中执行语句时由文件系统层读取的字节数
exec_reads_blks bigint 在此数据库中执行语句时由文件系统层读取的 8K 块数
exec_writes bigint 在此数据库中执行语句时由文件系统层写入的字节数
exec_writes_blks bigint 在此数据库中执行语句时由文件系统层写入的 8K 块数
exec_msgsnds bigint 在此数据库中执行语句时发送的 IPC 消息数
exec_msgrcvs bigint 在此数据库中执行语句时收到的 IPC 消息数
exec_nsignals bigint 在此数据库中执行语句时接收的信号数
exec_nvcsws bigint 在此数据库中执行语句时主动发生的上下文切换次数
exec_nivcsws bigint 在此数据库中执行语句时被动发生的上下文切换次数

更新扩展

请注意,除 SQL 对象外,其他更改都需要重新启动 PostgreSQL。无论扩展是否更新,重启完成后,大多数新代码都会被启用,因为扩展只负责在 SQL 中公开内部数据结构。

另请注意,当集合返回函数的字段发生变化时,需要重新启动 PostgreSQL 才能加载新版本的扩展。在重新启动完成之前,更新扩展将失败,并显示类似以下内容的消息:

could not find function "pg_stat_kcache_2_2" in file pg_stat_kcache.so

Bug 和限制

没有发现已知的错误。

跟踪规划器资源使用情况需要 PostgreSQL 13 或更高版本。

我们会假定一个内核块的大小是 512 字节。对于 Linux 来说,情况确实如此,但对于另一些 Unix 系统来说,情况可能并非如此。

请参见:http://lkml.indiana.edu/hypermail/linux/kernel/0703.2/0937.html

在没有提供原生的 getrusage(2) 的平台上,除了 user_time 和 system_time 之外的所有字段都将为 NULL。

在提供了原生的 getrusage(2) 的平台上,某些字段可能没有维护。这是一个依赖于系统平台的行为,请参考你的系统中 getrusage(2) 手册页来了解更多细节。

如果 pg_stat_kcache.track 是 all,则 pg_stat_kcache 会跟踪嵌套语句。为了保持实现的简单,跟踪的嵌套级别的最大层级数限制为 64,但这对于合理的场景来说应该足够了。

即使 pg_stat_kcache.track 是 all,pg_stat_kcache 视图也只会输出顶层语句的统计信息。因此,有一种情况是,即使在规划一条嵌套语句时使用的用户层 CPU 时间很高,pg_stat_kcache 视图的 plan_user_time 还是很小。在这种情况下,规划一条嵌套语句时使用的用户层 CPU 时间会计入到 exec_user_time。