如何发现 PostgreSQL 中那些消耗 CPU 和 I/O 资源的 SQL 查询？-51CTO.COM

安装

要求

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。