主页 / server / 问题 / 1162898
Accepted
mattbasta
Asked: 2024-07-23 01:30:41 +0800 CST

Postgres 查询延迟出现神秘峰值

  • 772

在工作中，我们遇到了一种神秘的问题。每隔几个小时，我们的一个表（tasks）就会出现查询延迟增长（由 CPU 使用率引起），随后出现爆发式增长LWLock:BufferContent，然后又下降到正常水平。以下是 RDS 性能洞察的屏幕截图：

图表显示 CPU 使用率增加，随后 BufferContent 锁定并返回稳定状态

我们做了大量工作来尝试缓解这种情况，这些工作已经导致事故发生的频率（据传闻）降低，严重程度也降低。然而，问题并没有消失。

首先，我们注意到有些查询没有命中索引。我花了一些时间确保针对此表的所有查询都执行索引和仅索引扫描。查询通常如下所示：

SELECT COUNT(1) FROM tasks WHERE status = 'PENDING' AND NOT deleted

status并且我们在条件上有相应的索引status = 'PENDING' AND deleted = FALSE。EXPLAIN ANALYZE表明索引正在被正确使用。

我们确实发现，当用户有大量记录时，一些在我们的列上指定条件的查询userId会有一个糟糕的查询计划。这个问题通过修复查询和索引改进得到了解决。据我们所知，没有任何查询对任何值都有糟糕的查询计划（但问题仍然存在）。

在峰值期间，传入负载没有增加。在事件发生之前、期间和之后，受影响的查询在 RDS 性能洞察中显示相同的“调用次数/秒”，并且我们的客户端应用程序显示稳定的 QPS，因此这不是由查询增加引起的。

我确实观察到，EXPLAIN ANALYZE这些查询的结果确实表明在这些峰值期间堆提取有所增加，即使对于仅索引扫描也是如此。我读到在索引扫描期间堆仍然会受到影响，因为可见性图可能表明包含索引引用的元组的页面可能不可见。这似乎是一个强烈的信号：PENDING任务（如上面的查询所示）通常是接收许多更新的“热”行，因此包含这些行的页面的可见性图需要提取是有道理的。

为了弥补这一缺陷，我们调整了自动清理设置，使清理频率大大提高。现在我们每隔几个小时就会看到一次自动清理，但问题并没有消失。我看到这些峰值出现在表的自动清理完成 20 分钟后。此外，EXPLAIN ANALYZE峰值期间报告的堆提取次数可能比任务记录数高出 1-2 个数量级PENDING：总数很少超过 200，我们可以看到在仅包含PENDING记录的索引的索引扫描中存在数千次堆提取。

在调试过程中，我注意到查询规划器确实倾向于随着时间的推移调整查询的索引，即使负载保持不变。它有时会选择允许索引扫描但不允许仅索引扫描的索引。例如，它可能会选择 上的索引，而忽略上的条件status索引，即使后者完全满足查询。我将和设置调整为 而不是 Postgres 默认值（分别为 1 和 4），这应该会指示 Postgres 优先使用索引扫描。不幸的是，问题仍然存在（尽管可能频率较低）。上面的屏幕截图是在设置调整之后的。statusstatus = 'PENDING' and not deletedseq_page_costrandom_page_cost1.0

一些附加信息：

  1. 该问题仅影响此一张表（或者我应该说，此一张表的查询）。
  2. 峰值期间表上的所有查询都会受到影响，而不是单个查询。

到目前为止，我几乎没有主意了。作为参考，我们在 Postgres 14.10 上运行db.m5.16xlargeGP2 SSD 卷。我怀疑我们的 Postgres 版本只需要更新（计划中的工作），但这不是一个令人满意的解决方案。

我有点困惑的是，为什么造成延迟的资源使用是 CPU。您可以在上面的屏幕截图中看到绿色（CPU）的增加。基本上每个查询都应该扫描很少的记录。所有查询都是计数聚合，查询的条件都是简单的相等。这是没有峰值时受影响的查询输出之一：

Aggregate  (cost=46.02..46.03 rows=1 width=8) (actual time=0.362..0.362 rows=1 loops=1)
"  ->  Index Only Scan using ""tasks_globalPending"" on tasks  (cost=0.25..37.54 rows=3393 width=0) (actual time=0.239..0.353 rows=128 loops=1)"
        Heap Fetches: 178
Planning Time: 0.161 ms
Execution Time: 0.377 ms

非常快！可以说是非常高效（尽管我更喜欢零堆提取）。对于COUNT()~never 返回值大于 300 的程序来说，令人费解的是，它如何从每个查询 0.4 毫秒增加到 10-20 毫秒，并将这段时间浪费在 CPU 使用上。它在做什么？

postgresql

1 个回答

  1. Best Answer

    谜团已经解开了！

    问题在于启用了此功能的只读副本上的长时间运行查询hot_standby_feedback。ETL 作业的副本上的长时间运行查询与峰值完全一致。我发现峰值的峰值似乎总是在整点后 30 分钟左右出现。在寻找以这种节奏运行的东西时，我找到了我们的 ETL 作业。

    hot_standby_feedback告诉主服务器“嘿，我正在做一件事，做完后我会通知你的”。这可以防止复制滞后。

    由于 Postgres 复制的工作方式，在副本上长时间运行查询时可能会发生复制滞后。Postgres 会复制对元组的更改，而不是发送查询并在副本上重播查询。对元组的更新“反馈”以串行方式传入，并应用于副本。请考虑以下场景：

    1. 副本开始执行长时间运行的查询
    2. 主服务器运行 VACUUM 操作，删除副本上的查询可能正在读取的一些元组。
    3. 副本在查询过程中收到这些元组的更新

    此时，副本必须暂停应用这些更新，因为它不知道这些特定元组是否会影响查询结果。这导致副本落后于主副本。

    hot_standby_feedback让主服务器知道在副本服务器处于其中一个查询中时不要执行该操作，从而允许副本服务器中的并发查询使用来自主服务器的新数据成功完成。

    缺点是，对于非常繁忙的表，这似乎会导致许多问题（至少在 Postgres 14.10 上）。解决方法是向繁忙的表添加索引，这样副本上长时间运行的查询就变成了相对较快的查询。完成此操作后，主服务器上的问题就完全消失了。

    • 2

相关问题