14.1 介绍
收集有关文件系统层完成的实际读取和写入的统计信息。扩展需要 PostgreSQL >= 9.4. ，并且需要安装 pg_stat_statements 扩展。需要 PostgreSQL 9.4 或更高版本，因为提供的 pg_stat_statements 的先前版本没有公开 queryid 字段。
14.2 安装
编译
该模块可以使用标准 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 集群后，您可以在需要访问统计信息的每个数据库中安装扩展：
CREATE EXTENSION pg_stat_kcache;
14.3 配置
可以在 postgresql.conf 中配置以下 GUC：
- pg_stat_kcache.linux_hz (int, default -1): 通知 pg_stat_kcache linux CONFIG_HZ 配置选项。 pg_stat_kcache 使用它来补偿采样错误。默认值为-1，尝试在启动时猜测它。
- pg_stat_kcache.track (enum, default top): 控制哪些语句被 pg_stat_kcache 跟踪。指定 top 以跟踪顶级语句（由客户端直接发出的语句）， all 也跟踪嵌套语句（例如在函数中调用的语句），或 none 以禁用语句统计收集。
- pg_stat_kcache.track_planning (bool, default off): 控制pg_stat_kcache是否跟踪计划操作和持续时间（需要PostgreSQL 13或更高版本）。
14.4 用法
扩展pg_stat_kcache 创建几个对象。
视图pg_stat_kcache
| Name | Type | Description |
|---|---|---|
| datname | name | Name of the database |
| plan_user_time | double precision | User CPU time used planning statements in this database, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)在此数据库中计划语句使用的用户 CPU 时间，以秒和毫秒为单位（如果启用 pg_stat_kcache.track_planning，否则为零） |
| plan_system_time | double precision | System CPU time used planning statements in this database, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中计划语句使用的系统 CPU 时间，以秒和毫秒为单位（如果启用 pg_stat_kcache.track_planning，否则为零） |
| plan_minflts | bigint | Number of page reclaims (soft page faults) planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中的页面回收（软页面错误）计划语句数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_majflts | bigint | Number of page faults (hard page faults) planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中的页面错误（硬页面错误）计划语句数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_nswaps | bigint | Number of swaps planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中的交换计划语句数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_reads | bigint | Number of bytes read by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中文件系统层规划语句读取的字节数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_reads_blks | bigint | Number of 8K blocks read by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中文件系统层规划语句读取的 8K 块数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_writes | bigint | Number of bytes written by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中文件系统层规划语句写入的字节数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_writes_blks | bigint | Number of 8K blocks written by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中文件系统层规划语句读取的 8K 块数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_msgsnds | bigint | Number of IPC messages sent planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中发送计划语句的 IPC 消息数（如果启用 pg_stat_kcache.track_planning，否则为零） |
| plan_msgrcvs | bigint | Number of IPC messages received planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)在这个数据库中接收到计划语句的 IPC 消息数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_nsignals | bigint | Number of signals received planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)在此数据库中接收到规划语句的信号数（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_nvcsws | bigint | Number of voluntary context switches planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中自愿上下文切换计划语句的数量（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| plan_nivcsws | bigint | Number of involuntary context switches planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)此数据库中非自愿上下文切换计划语句的数量（如果启用了 pg_stat_kcache.track_planning，否则为零） |
| exec_user_time | double precision | User CPU time used executing statements in this database, in seconds and milliseconds 用户在此数据库中执行语句所使用的 CPU 时间，以秒和毫秒为单位 |
| exec_system_time | double precision | System CPU time used executing statements in this database, in seconds and milliseconds 在此数据库中执行语句所使用的系统 CPU 时间，以秒和毫秒为单位 |
| exec_minflts | bigint | Number of page reclaims (soft page faults) executing statements in this database 在此数据库中执行语句的页面回收（软页面错误）数 |
| exec_majflts | bigint | Number of page faults (hard page faults) executing statements in this database此数据库中执行语句的页错误（硬页错误）数 |
| exec_nswaps | bigint | Number of swaps executing statements in this database 此数据库中执行语句的交换数 |
| exec_reads | bigint | Number of bytes read by the filesystem layer executing statements in this database 文件系统层在此数据库中执行语句读取的字节数 |
| exec_reads_blks | bigint | Number of 8K blocks read by the filesystem layer executing statements in this database 文件系统层在此数据库中执行语句读取的 8K 块数 |
| exec_writes | bigint | Number of bytes written by the filesystem layer executing statements in this database 文件系统层在此数据库中执行语句写入的字节数 |
| exec_writes_blks | bigint | Number of 8K blocks written by the filesystem layer executing statements in this database 该数据库中文件系统层执行语句写入的 8K 块数 |
| exec_msgsnds | bigint | Number of IPC messages sent executing statements in this database 在此数据库中执行语句发送的 IPC 消息数 |
| exec_msgrcvs | bigint | Number of IPC messages received executing statements in this database在此数据库中执行语句接收到的 IPC 消息数 |
| exec_nsignals | bigint | Number of signals received executing statements in this database在此数据库中执行语句接收到的信号数 |
| exec_nvcsws | bigint | Number of voluntary context switches executing statements in this database在此数据库中执行语句的自愿上下文切换数 |
| exec_nivcsws | bigint | Number of involuntary context switches executing statements in this database 在此数据库中执行语句的非自愿上下文切换数 |
视图pg_stat_kcache_detail
| Name | Type | Description |
|---|---|---|
| query | text | Query text |
| top | bool | True if the statement is top-level |
| datname | name | Database name |
| rolname | name | Role name |
| plan_user_time | double precision | User CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_system_time | double precision | System CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_minflts | bigint | Number of page reclaims (soft page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_majflts | bigint | Number of page faults (hard page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nswaps | bigint | Number of swaps planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_reads | bigint | Number of bytes read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_reads_blks | bigint | Number of 8K blocks read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_writes | bigint | Number of bytes written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_writes_blks | bigint | Number of 8K blocks written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_msgsnds | bigint | Number of IPC messages sent planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_msgrcvs | bigint | Number of IPC messages received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nsignals | bigint | Number of signals received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nvcsws | bigint | Number of voluntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nivcsws | bigint | Number of involuntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| exec_user_time | double precision | User CPU time used executing the statement, in seconds and milliseconds |
| exec_system_time | double precision | System CPU time used executing the statement, in seconds and milliseconds |
| exec_minflts | bigint | Number of page reclaims (soft page faults) executing the statements |
| exec_majflts | bigint | Number of page faults (hard page faults) executing the statements |
| exec_nswaps | bigint | Number of swaps executing the statements |
| exec_reads | bigint | Number of bytes read by the filesystem layer executing the statements |
| exec_reads_blks | bigint | Number of 8K blocks read by the filesystem layer executing the statements |
| exec_writes | bigint | Number of bytes written by the filesystem layer executing the statements |
| exec_writes_blks | bigint | Number of 8K blocks written by the filesystem layer executing the statements |
| exec_msgsnds | bigint | Number of IPC messages sent executing the statements |
| exec_msgrcvs | bigint | Number of IPC messages received executing the statements |
| exec_nsignals | bigint | Number of signals received executing the statements |
| exec_nvcsws | bigint | Number of voluntary context switches executing the statements |
| exec_nivcsws | bigint | Number of involuntary context switches executing the statements |
函数pg_stat_kcache_reset
重置 pg_stat_kcache 收集的统计信息。可以由超级用户调用：
select pg_stat_kcache_reset();
函数pg_stat_kcache function
此函数是一个集合返回函数，它转储共享内存结构的计数器的包含。该函数由 pg_stat_kcache 视图使用。任何用户都可以调用该函数：
SELECT * FROM pg_stat_kcache();
它提供了以下列：
| Name | Type | Description |
|---|---|---|
| queryid | bigint | pg_stat_statements’ query identifier |
| top | bool | True if the statement is top-level |
| userid | oid | Database OID |
| dbid | oid | Database OID |
| plan_user_time | double precision | User CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_system_time | double precision | System CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_minflts | bigint | Number of page reclaims (soft page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_majflts | bigint | Number of page faults (hard page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nswaps | bigint | Number of swaps planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_reads | bigint | Number of bytes read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_reads_blks | bigint | Number of 8K blocks read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_writes | bigint | Number of bytes written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_writes_blks | bigint | Number of 8K blocks written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_msgsnds | bigint | Number of IPC messages sent planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_msgrcvs | bigint | Number of IPC messages received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nsignals | bigint | Number of signals received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nvcsws | bigint | Number of voluntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| plan_nivcsws | bigint | Number of involuntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero) |
| exec_user_time | double precision | User CPU time used executing the statement, in seconds and milliseconds |
| exec_system_time | double precision | System CPU time used executing the statement, in seconds and milliseconds |
| exec_minflts | bigint | Number of page reclaims (soft page faults) executing the statements |
| exec_majflts | bigint | Number of page faults (hard page faults) executing the statements |
| exec_nswaps | bigint | Number of swaps executing the statements |
| exec_reads | bigint | Number of bytes read by the filesystem layer executing the statements |
| exec_reads_blks | bigint | Number of 8K blocks read by the filesystem layer executing the statements |
| exec_writes | bigint | Number of bytes written by the filesystem layer executing the statements |
| exec_writes_blks | bigint | Number of 8K blocks written by the filesystem layer executing the statements |
| exec_msgsnds | bigint | Number of IPC messages sent executing the statements |
| exec_msgrcvs | bigint | Number of IPC messages received executing the statements |
| exec_nsignals | bigint | Number of signals received executing the statements |
| exec_nvcsws | bigint | Number of voluntary context switches executing the statements |
| exec_nivcsws | bigint | Number of involuntary context switches executing the statements |
14.5 更新扩展
请注意，除了 SQL 对象之外的更改需要重新启动 PostgreSQL。大多数新代码将在重启完成后立即启用，无论扩展是否更新，因为扩展只负责在 SQL 中公开内部数据结构。
另请注意，当 set-returning 函数字段发生更改时，需要重新启动 PostgreSQL 才能加载新版本的扩展。在重新启动完成之前，更新扩展程序将失败，并显示类似于以下内容的消息：
在文件 …/pg_stat_kcache.so 中找不到函数“pg_stat_kcache_2_2”
14.6 BUG 和限制
没有已知的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。
14.7 作者
pg_stat_kcache 是 Thomas Reiss 的原创开发，大部分代码灵感来自 pg_stat_plans。 Julien Rouhaud 也贡献了扩展的某些部分。
感谢 Peter Geoghegan 为 pg_stat_plans 提供了很多灵感，因此我们可以非常简单地编写这个扩展。
14.8 许可
pg_stat_kcache 是在 PostgreSQL 许可下分发的免费软件。
版权所有 © 2014-2017, Dalibo 版权所有 © 2018-2022, PoWA 团队
最后修改时间：2022-03-25 16:48:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
