postgresql - Postgres 更快的 autovacuum

Question

我有一个非常大的表，有 400MM 条记录，通常只接收插入。但是，最近我必须对记录进行大量更新才能完成任务。这会产生很多死元组。我已将全局配置更新为以下内容:

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_vacuum_cost_limit = 2000
autovacuum_max_workers = 6

通过这些设置，我的意图是只要死元组增加超过 10,000 条记录，autovacuum 就会将其清除。

但是，我发现当表忙于其他插入等时，死元组计数不会改变。它保持固定在某个死元组计数。只有当数据库事件在夜间减慢时，autovacuum 似乎才能正常工作。

我需要 autovacuum 全天积极处理死元组。我将如何做到这一点？我是否需要增加 max_workers 数量？

更新:用户@Laurenz Albe 建议我运行一些有无死元组的性能数据来证明性能差异。

我将提供 sql 查询和 EXPLAIN(ANALYZE, BUFFERS) 结果。为了隐私，我更改了表名和组键。

EXPLAIN (ANALYZE, BUFFERS)
SELECT  ld.upid,
        MAX(ld.lid)
INTO _tt_test_with_dead_tuples
FROM big_table ld
GROUP BY ld.upid;

-->>> 大约有 1%(383.2MM 中的 3.648MM)死元组，结果如下。

HashAggregate  (cost=25579746.07..25584552.97 rows=480690 width=8) (actual time=5966760.520..5975279.359 rows=16238417 loops=1)
  Group Key: upid
  Buffers: shared hit=3015376 read=16753169 dirtied=1759802 written=1360458
  ->  Seq Scan on big_table ld  (cost=0.00..23642679.05 rows=387413405 width=8) (actual time=0.024..5593239.148 rows=383753513 loops=1)
        Buffers: shared hit=3015376 read=16753169 dirtied=1759802 written=1360458
Planning time: 2.677 ms
Execution time: 6012319.846 ms

-->>> 有 0 个死元组，结果如下。

HashAggregate  (cost=25558409.48..25562861.52 rows=445204 width=8) (actual time=825662.640..835163.398 rows=16238417 loops=1)
  Group Key: upid
  Buffers: shared hit=15812 read=19753809
  ->  Seq Scan on big_table ld  (cost=0.00..23628813.32 rows=385919232 width=8) (actual time=0.020..533386.128 rows=383753513 loops=1)
        Buffers: shared hit=15812 read=19753809
Planning time: 10.109 ms
Execution time: 843319.731 ms

Answer 1

死元组不是你的问题。

你真正的问题在别的地方；我在下面突出显示了它。

慢查询中的顺序扫描:

Buffers: shared hit=3015376 read=16753169 <strong>dirtied=1759802 written=1360458</strong>

快速查询中的顺序扫描:

Buffers: shared hit=15812 read=19753809

看起来大约有 200 万个表 block 包含最近写入或更新的元组。

在写入元组时，PostgreSQL 还不知道事务是提交还是回滚，因此这些信息不存储在元组中。但是，它记录在存储在 pg_xact 中的提交日志 中。 (或 pg_clog ，具体取决于您的版本)。

现在，第一个阅读新编写的元组的读者将不得不查阅提交日志以确定该元组是否“存在”。为了避免将来的读者遇到麻烦，它在元组中设置了所谓的提示位以反射(reflect)该信息。

这会改变并因此“弄脏”包含元组的 block ，如果你有一个小的 shared_buffers设置并用完可用缓冲区，后端甚至必须将 block 写入存储以清理它们并腾出空间。

这种写法使您的查询如此缓慢。

清空表可以解决问题，因为 VACUUM不仅清理死元组，还为你设置提示位(它也是一个阅读器!)。

要验证这一点，请运行相同的 SELECT第二次不清空表，你会发现它对 300 万个死元组的处理速度一样快，因为现在提示位都已设置。

这就是为什么运行 VACUUM 是个好主意的原因之一。在加载大量行后在表格上，即使没有任何内容需要清理——您可以为第一位读者节省大量工作。

想法:增加shared_buffers改善情况？

但是由于对表进行清理可以解决问题，您也可以使用 autovacuum 更频繁地设置提示位。

为此，您可以设置 autovacuum_vacuum_scale_factor为 0 并设置 autovacuum_vacuum_threshold到一个大常量(方式大于 10000)，这样没有提示位的行永远不会太多。

此外，设置autovacuum_vacuum_cost_delay到 0 以便 autovacuum 快速完成。

不要全局更改这些参数，使用ALTER TABLE ... SET (...)仅为该表设置它们。