文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

如何发现 PostgreSQL 中那些消耗 CPU 和 I/O 资源的 SQL 查询?

2024-11-29 19:18

关注

从 PGDG 软件仓库

如果您从 PGDG 软件仓库(Debian/Ubuntu  上的 APT 或 RHEL/Rocky  上的 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。

来源:红石PG内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯