pg_stat_statements 入门

查询优化的两个分支

查询优化有两个主要的分支：

1. "微观"优化：分析和改进特定的查询。主要工具是 EXPLAIN。
2. "宏观"优化：分析整个或大部分工作负载，进行细分，研究其特性，从上到下识别并改进表现最差的部分。主要工具是 pg_stat_statements(以及其扩展或替代方案)、等待事件分析和 PostgreSQL 日志。

今天我们将重点讨论如何从基础开始，阅读和使用 pg_stat_statements，并逐步利用它的数据进行宏观优化。

pg_stat_statements 基础

pg_stat_statements 扩展 (简称为 "pgss") 已经成为宏观分析的事实标准。

它跟踪所有查询，并将它们聚合到查询组中 — 即"标准化查询"，其中参数会被去除。

有一些值得注意的限制：

• 它不会显示正在运行的查询信息 (可以通过 pg_stat_activity 找到)。
• 一个主要问题是：它不会跟踪失败的查询，这有时可能会导致错误的结论 (例如：CPU 和磁盘 I/O 负载很高，但 99% 的查询因为 statement_timeout 失败，导致系统负载增加却没有产生任何有用的结果——在这种情况下，pgss 是"瞎"的)。
• 如果查询中有 SQL 注释，它们不会被移除，每个标准化查询的查询列中只会保留第一个注释的值。

pg_stat_statements 视图包含三种类型的列：

1. queryid – 标准化查询的标识符。在最新的 PostgreSQL 版本中，它还可以用于将 pgss 的数据与 pg_stat_activity 和 PostgreSQL 日志连接（JOIN）。需要注意的是：queryid 的值可能是负数。
2. 描述性的列：包括数据库 ID (dbid)、用户 ID (userid) 和查询本身 (query)。
3. 指标：大部分指标都是累积的，例如 calls、total_time、rows 等。非累积指标包括 stddev_plan_time、stddev_exec_time、min_exec_time 等。在这篇文章中，我们主要关注累积指标。

// 注：在下文中，我有时会将标准化查询称为"查询组"或简称为"组"。

我们来提一些在宏观优化中最常用的指标 (完整列表请参阅文档)：

1. calls – 此查询组 (标准化查询) 的调用次数。

2. total_plan_time 和 total_exec_time – 该查询组的规划和执行的聚合持续时间 (请再次记住：失败的查询不会被跟踪，包括那些由于 statement_timeout 而失败的查询)。

3. rows – 此查询组中的查询返回行数。

4. shared_blks_hit 和 shared_blks_read – 来自缓冲池的命中和读取操作次数。这里有两个重要的注意点：

   • 此处的 "read" 是指从缓冲池读取——这不一定意味着物理读取磁盘，因为数据可能已缓存于操作系统的页面缓存中。因此，我们不能直接认为这些读取来自磁盘。一些监控系统会犯这个错误，但在某些情况下，这种细微差别对于我们的分析能否得出正确结论至关重要。
   • "blocks hit" 和 "blocks read" 的名称可能会产生误导，暗示我们在讨论数据量 — 块 (缓冲区) 的数量。虽然这些值可以聚合，但我们必须记住同一个缓冲区可能会被多次读取或命中。因此，与其说 "blocks have been hit"，不如说 "blocks hits"。

5. wal_bytes – 此查询组中查询写入 WAL 的字节数。

还有许多其他有趣的指标，建议阅读文档来了解它们 (文档)。

处理 pgss 中的累积指标

要读取和解释 pgss 中的数据，需要以下三个步骤：

1. 获取两个时间点对应的快照。
2. 计算每个累积指标的差值，以及这两个点之间的时间差。
   • 一个特殊情况是，第一个点是统计数据收集的开始时刻。在 PostgreSQL 14 及更新版本中，有一个单独的视图 pg_stat_statements_info，其中包含 pgss 统计数据上次重置的时间信息。不幸的是，在 PostgreSQL 13 及更早版本中，此信息未被存储。
3. (最有趣的部分！) 对每个累积指标的差值计算三种衍生指标——假设 M 是我们的指标：a. dM/dt – 指标 M 的基于时间的微分。 b. dM/dc – 基于调用次数的微分 (将在下一篇文章中详细解释)。 c. %M – 该标准化查询在整个工作负载中指标 M 所占的百分比。

第 3 步不仅可以应用于单个主机上的特定标准化查询，还可以应用于更大的组，例如：

• 所有从节点的聚合工作负载。
• 单个节点 (例如，主节点) 的整个工作负载。
• 更大的分组，例如特定用户或特定数据库的所有查询。
• 特定类型的所有查询——例如所有 UPDATE 查询。

如果您的监控系统支持 pgss，那么您无需手动处理快照。不过，请注意，我个人不知道有任何监控工具可以完美支持 pgss，并保留本文中讨论的所有信息 (而且我研究过不少 PostgreSQL 监控工具)。

假设您已经成功获取了 pgss 的两个快照 (记得记录它们的收集时间戳) 或使用了适当的监控工具，现在我们来看看前面讨论的三种衍生指标的实际意义。

衍生指标 1：基于时间的微分

• dM/dt，其中 M 是 calls – 这个指标的意义很简单，它表示每秒的查询次数 (QPS)。如果我们讨论的是特定查询组 (标准化查询)，那么它表示该组中所有查询的每秒查询数。例如，如果 QPS 为 10,000，这表明可能需要改进客户端 (应用程序) 的行为以减少此值；如果 QPS 为 10，那就相对较少 (当然，具体还要看情况)。如果考虑整个节点的 QPS，那么它就是我们的"全局 QPS"。

• dM/dt，其中 M 是 total_plan_time + total_exec_time – 这是查询宏观分析中最有趣和最关键的指标之一，主要针对资源消耗优化 (目标是减少服务器处理查询所花费的时间)。一个有趣的事实是，它以 "seconds per second" 为单位，表示服务器每秒处理这个查询组所花费的时间。一个粗略（但具有说明性）的解释是：如果该值为 2 sec/sec，表示服务器每秒花费 2 秒来处理这些查询——因此我们肯定希望有超过 2 个 vCPU 来处理这个工作负载。尽管这是一个粗略的解释，因为 pgss 无法区分查询是在等待锁还是在 CPU 上执行实际工作。因此，可能会出现该值很高但对 CPU 负载影响不大的情况。

• dM/dt，其中 M 是 rows – 这个指标表示该查询组每秒返回的行流量。例如，如果每秒返回 1000 行，表示从 PostgreSQL 服务器到客户端有明显的数据流。这里的一个有趣点是，有时我们需要考虑 PostgreSQL 生成的结果会给应用程序节点带来多大的负载——返回过多的行可能会在客户端一侧占用大量资源。

• dM/dt，其中 M 是 shared_blks_hit + shared_blks_read – 这是每秒的缓冲区操作次数 (仅读取数据，不涉及写入)。这是另一个关键的优化指标。值得将缓冲区操作数转换为字节数。在大多数情况下，缓冲区大小为 8 KiB (show block_size;进行确认)，因此每秒 500,000 次缓冲区命中和读取相当于大约每秒 500000 bytes/sec * 8 / 1024 / 1024 = ~ 3.8 GiB/s 的内部数据读取流量 (注意：同一个缓冲区可能会多次处理)。这是一个显著的负载——您可能需要检查其他指标以确定这是否合理，或者是否可以作为优化的候选项。

• dM/dt，其中 M 是 wal_bytes – 这是每秒写入的 WAL 字节流量。这个指标是在 PostgreSQL 13 及更新版本中引入的，主要用于了解哪些查询对 WAL 写入贡献最大。当然，写入的 WAL 越多，对物理和逻辑复制，以及备份系统的压力也就越大。一个高度病态的工作负载示例如下：一系列这样的事务 begin; delete from ...; rollback; 删除大量行并撤销这些操作，这会产生大量 WAL，而没有执行任何有用的工作。(注意：尽管这里使用了 ROLLBACK，而且 pgss 无法跟踪失败的语句，但这些语句仍然会被跟踪，因为它们在事务中是成功的。)

这就是 pgss 相关使用指南的第一部分。在接下来的部分中，我们将讨论 dM/dc 和 %M，以及基于 pgss 进行宏观优化的其他实用内容。

如果你觉得这篇文章有帮助，请分享给你的同事以及任何使用 PostgreSQL 的人。