时间戳

首页 > 解决方案 > LockRows 计划节点耗时较长

postgresql - LockRows 计划节点耗时较长

问题描述

我在 Postgres 中有以下查询（模拟工作队列）：

DELETE FROM work_queue
WHERE id IN ( SELECT l.id
              FROM work_queue l
              WHERE l.delivered = 'f' and l.error = 'f' and l.archived = 'f'
              ORDER BY created_at
              LIMIT 5000
              FOR UPDATE SKIP LOCKED );

在同时运行上述内容（每秒 4 个进程）以及以 10K 记录/秒的速率并发摄取时，该查询有效地成为节点work_queue上的瓶颈。LockRow

查询计划输出：

 Delete on work_queue  (cost=478.39..39609.09 rows=5000 width=67) (actual time=38734.995..38734.998 rows=0 loops=1)
   ->  Nested Loop  (cost=478.39..39609.09 rows=5000 width=67) (actual time=36654.711..38507.393 rows=5000 loops=1)
         ->  HashAggregate  (cost=477.96..527.96 rows=5000 width=98) (actual time=36654.690..36658.495 rows=5000 loops=1)
               Group Key: "ANY_subquery".id
               ->  Subquery Scan on "ANY_subquery"  (cost=0.43..465.46 rows=5000 width=98) (actual time=36600.963..36638.250 rows=5000 loops=1)
                     ->  Limit  (cost=0.43..415.46 rows=5000 width=51) (actual time=36600.958..36635.886 rows=5000 loops=1)
                           ->  LockRows  (cost=0.43..111701.83 rows=1345680 width=51) (actual time=36600.956..36635.039 rows=5000 loops=1)
                                 ->  Index Scan using work_queue_created_at_idx on work_queue l  (cost=0.43..98245.03 rows=1345680 width=51) (actual time=779.706..2690.340 rows=250692 loops=1)
                                       Filter: ((NOT delivered) AND (NOT error) AND (NOT archived))
         ->  Index Scan using work_queue_pkey on work_queue  (cost=0.43..7.84 rows=1 width=43) (actual time=0.364..0.364 rows=1 loops=5000)
               Index Cond: (id = "ANY_subquery".id)
 Planning Time: 8.424 ms
 Trigger for constraint work_queue_logs_work_queue_id_fkey: time=5490.925 calls=5000
 Trigger work_queue_locked_trigger: time=2119.540 calls=1
 Execution Time: 46346.471 ms

（对应可视化：https ://explain.dalibo.com/plan/ZaZ ）

有什么改进的想法吗？为什么在存在并发插入的情况下锁定行需要这么长时间？请注意，如果我没有并发插入到work_queue表中，则查询速度非常快。

标签： postgresql

解决方案

我们可以看到索引扫描返回了 250692 行，以便找到 5000 行进行锁定。所以显然我们不得不跳过其他 49 个锁定行的查询。这不会很有效，尽管如果是静态的，它不应该像你在这里看到的那么慢。但它必须为每次尝试获取一段内存的临时排他锁。如果它与许多其他进程争夺这些锁，您可能会导致性能的级联崩溃。

如果您每秒启动 4 个这样的语句，没有上限并且没有等待任何先前的语句完成，那么您的情况就不稳定。你一次跑得越多，他们就越互相争斗并放慢速度。如果完成率下降但启动间隔没有下降，那么您只会让更多进程与更多其他进程发生冲突，并且每个进程都会变慢。因此，一旦你被推到边缘，它可能永远无法自行恢复。

并发插入的作用可能只是为系统提供足够的嘈杂负载，让崩溃有机会站稳脚跟。当然，如果没有并发插入，您的删除操作很快就会用完要删除的东西，此时它们会非常快。

推荐阅读