主页 / dba / 问题 / 299618
Accepted
Robert
Asked: 2021-09-16 02:41:17 +0800 CST

大桌子上的真空有时需要很长时间

  • 772

在 PostgreSQL 11 数据库服务器中，我有一个大表（9.32 亿行，2150GB，未分区），我每晚通过批处理作业向其中插入大约 2-3 百万行。该表是用autovacuum_enabled=FALSE. 插入后，我VACUUM (ANALYZE)在桌子上执行 a，这通常需要大约 30-40 秒。然而，每隔几周，这种真空就需要很长时间，比如今天需要 2:11 h:mm。

我现在正在考虑在我的夜间工作之前做一个真空吸尘器，以避免在工作运行期间这些长时间的运行时间。我查看了PostgreSQL 11 Vacuum 文档页面中描述的选项。我知道 aVACUUM (FULL)在运行时非常昂贵，因此可能是不可能的。在我VACUUM (FREEZE)的情况下会有用吗？另一种选择VACUUM (DISABLE_PAGE_SKIPPING)似乎与我的需求无关。

编辑（2021-09-27）：回顾过去的 270 天（2021-01-01 到 2021-09-27），大多数日子需要 16-60 秒才能完成抽真空。然而，在第 9 天，真空度超过了 1000 秒，其中 4 天甚至超过了 3500 秒（见下图）。有趣的是这些异常值之间的时间差，介于 25 到 35 天之间。在这 25-35 天的时间里，似乎有什么东西在堆积，需要更长的真空期。这很可能是下面评论中提到的事务 ID 环绕。

VACUUM 性能图

编辑 (2022-02-08)：在 2021 年 9 月，我们通过更改以下内容修改了脚本：

VACUUM (ANALYZE)

至：

ANALYZE (VERBOSE)
VACUUM (FREEZE, VERBOSE)

不幸的是，这个工作步骤大约每月一次的长时间运行仍在继续。然而，通过分成两个独立的分析和真空步骤，以及详细的输出，我们能够更好地判断发生了什么。

在快速的一天，分析和真空步骤的输出如下所示：

INFO:  analyzing "analytics.a_box_events"
INFO:  "a_box_events": scanned 30000 of 152881380 pages, containing 264294 live rows and 0 dead rows; 30000 rows in sample, 1346854382 estimated total rows
ANALYZE
INFO:  aggressively vacuuming "analytics.a_box_events"
INFO:  "a_box_events": found 0 removable, 3407161 nonremovable row versions in 406302 out of 152881380 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108425108
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152475078 frozen pages.
0 pages are entirely empty.
CPU: user: 4.53 s, system: 3.76 s, elapsed: 25.77 s.
INFO:  aggressively vacuuming "pg_toast.pg_toast_1361926"
INFO:  "pg_toast_1361926": found 0 removable, 3700 nonremovable row versions in 680 out of 465143 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108425110
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 464463 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.05 s.
VACUUM

分析没有定时输出，主表上的真空大约需要20-40秒。分析和抽真空通常在一分钟内完成。

每 35-45 天一次，清理需要更长的时间，因为需要花费几分钟清理表的 39 个索引中的每一个。这有两种变体：

  1. 没有要删除的索引行版本

示例输出：

INFO:  index "idx_a_box_events_node_id" now contains 1347181817 row versions in 4038010 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 5.44 s, system: 13.45 s, elapsed: 230.59 s.

整个真空的最终输出如下所示：

INFO:  "a_box_events": found 0 removable, 2837554 nonremovable row versions in 340887 out of 153111143 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108429069
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152770256 frozen pages.
0 pages are entirely empty.
CPU: user: 669.94 s, system: 870.59 s, elapsed: 10501.17 s.
  1. 要删除的索引行版本

示例输出：

INFO:  scanned index "idx_a_box_events_node_id" to remove 2524 row versions
DETAIL:  CPU: user: 49.34 s, system: 11.42 s, elapsed: 198.63 s

和：

INFO:  index "idx_a_box_events_node_id" now contains 1228052362 row versions in 3478524 pages
DETAIL:  2524 index row versions were removed.
6 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

整个真空的最终输出如下所示：

INFO:  "a_box_events": found 56 removable, 3851482 nonremovable row versions in 461834 out of 139126225 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 97583006
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 138664391 frozen pages.
0 pages are entirely empty.
CPU: user: 2367.93 s, system: 761.34 s, elapsed: 8138.76 s.

如此长的运行时间目前比显示停止更令人讨厌。计划在 2023 年底升级到最新的 Postgres 服务器版本。届时我们将尝试对 VACUUM 使用 INDEX_CLEANUP 选项，每周末运行一次，以避免在日常 VACUUM 中昂贵的索引清理。与此同时，我们显然别无选择，只能耐心等待。

编辑（2022-08-24）：与此同时，我们将大表分区为每月切片，目前为我们提供了 56 个分区，其中大约 75-90 GB 用于表，45-55 GB 用于每个分区的索引。一旦一个晚上的新数据被添加到当前月份的分区，然后是一个ANALYZE (VERBOSE)（我们不再每天做VACUUM）。在每个月的第一个星期日，我们还会对VACUUM (FULL, ANALYZE)上个月的分区进行一次分区。

可以预料，ANALYZE (VERBOSE)单个分区上的每日运行速度非常快，平均每次运行大约 16-18 秒，从每月第一天的近 0 秒到月底的最大值，之后如下INSERT并ANALYZE应用于新分区并再次更快： 分析性能图

postgresql postgresql-performance

2 个回答

  1. 这很可能是事务 ID 回绕，它必须扫描并冻结自上次回绕以来插入的行。

    您应该将夜间运行更改VACUUM (FREEZE)为分配负载，特别是如果它是一个仅插入表，其中主动冻结不会消耗不必要的资源。有关冻结元组的必要性的详细讨论，请参阅文档。

    另一种方法是升级到 PostgreSQL v13，它根据INSERTs的数量添加了 autovacuum 。

    • 1
  2. Best Answer

    我对这里发生的事情的理论是，对于仅 INSERT 表，vacuum 只需要访问被插入弄脏的表部分，并且可以完全跳过访问索引。

    但是，如果它甚至找到一个（在 v11 中）死元组，那么它需要扫描所有索引的整体，这可能需要很长时间。我很惊讶它需要超过 2 个小时，但那是一张非常大的桌子，所以索引也会非常大。成功的 INSERT 和 COPY 不会生成任何死元组。但即使是一个 UPDATE 或 DELETE 也会这样做，任何回滚的 INSERT 或 COPY 也是如此。这个理论看起来与你所看到的相符吗？你是否曾经有过任何一个元组的删除或更新，或者曾经有一个失败的插入？

    您可以在晚上 9 点运行 VACUUM，然后如果它确实必须扫描所有索引，那么您在批量加载之后运行的那个就不必再做一次了——除非批量加载本身就是导致死机的东西元组。

    但是，如果您只是在批量加载之后单独并按顺序运行 ANALYZE，然后 VACUUM，该怎么办？是长 VACUUM 在您开始工作时使用过多 IO 并减慢速度的问题，还是长 VACUUM 意味着 ANALYZE 尚未完成，因此您在工作日开始时计划不好的问题？

    另一个问题是，如果您没有做任何事情来冻结表格的某些部分，那么一旦反环绕 VACUUM 启动，您可能会陷入非常糟糕的时期。您执行的第一个 VACUUM FREEZE 将生成大量 IO 并花费很长时间，但之后它一次只需要冻结表的一小部分，因此应该是可管理的。与 VACUUM 的索引扫描部分不同，VACUUM FREEZE 的好处在于，如果它被中断，它不会丢失它到目前为止所做的所有工作，因为在可见性图中记录为冻结的页面可以在下次跳过. 因此，您可以启动 VACUUM FREEZE，然后在它似乎引起问题时取消，或者如果您离开您的办公桌，这样您将暂时无法监控它。那么一旦你被追上，

    • 0

相关问题