主页 / dba / 问题 / 18664
Accepted
François Beausoleil
Asked: 2012-06-01 17:56:01 +0800 CST

在 9.1 下是否仍建议使用常规 VACUUM ANALYZE？

  • 772

我在 Ubuntu 上使用 PostgreSQL 9.1。是否VACUUM ANALYZE仍建议按计划进行，还是 autovacuum 足以满足所有需求？

如果答案是“视情况而定”，那么：

  • 我有一个较大的数据库（30 GiB 压缩转储大小，200 GiB 数据目录）
  • 我在数据库中进行 ETL，每周导入近 300 万行
  • 变化最频繁的表都继承自一个主表，主表中没有数据（数据按周分区）
  • 我创建每小时汇总，并从那里创建每日、每周和每月报告

我问是因为计划VACUUM ANALYZE影响了我的报告。它运行了 5 个多小时，我这周不得不杀死它两次，因为它影响了常规的数据库导入。check_postgres没有报告数据库有任何明显的膨胀，所以这不是一个真正的问题。

从文档中，autovacuum 也应该处理事务 ID 环绕。问题是：我还需要一个VACUUM ANALYZE吗？

postgresql etl

3 个回答

  1. Best Answer

    只有在非临时表中更新或删除的行上才需要 VACUUM。显然，您正在执行大量 INSERT，但从描述中看不出您也在执行大量 UPDATE 或 DELETE。

    这些操作可以通过pg_stat_all_tables视图进行跟踪，特别是n_tup_upd和n_tup_del列。此外，更重要的是，有一n_dead_tup列告诉每个表需要清理多少行。（有关与统计信息收集相关的功能和视图，请参阅文档中的监控统计信息）。

    在您的情况下，一个可能的策略是抑制预定的 VACUUM，密切关注此视图并检查哪些表n_dead_tup显着上升。然后只对这些表应用积极的 VACUUM。如果有大表的行永远不会被删除或更新，并且只有在较小的表上才真正需要激进的 VACUUM，这将是一个胜利。

    但是请继续运行 ANALYZE 以使优化器始终拥有新的统计信息。

    • 32

  2. 在您的问题中，我没有看到任何问题autovacuum。这在很大程度上取决于你写作活动的模式。您提到每周有 300 万新INSERT行，但（或COPY）通常不会创建表和索引膨胀。（autovacuum只需要处理列统计、可见性地图和一些小工作）。UPDATE并且DELETE是表和索引膨胀的主要原因，尤其是在针对随机行时。我在你的问题中没有看到任何这些。

    autovacuum已经走了很长一段路，并且在 Postgres 9.1 或更高版本中做得很好。我会看看autovacuum设置。如果吸尘往往会干扰您的工作量，请查看“基于成本的吸尘延迟”。手动吸尘应该是罕见的例外。

    如果您有很多随机UPDATEs，您可能希望将 设置FILLFACTOR为低于 100 的值，以允许立即进行 HOT 更新并减少对VACUUM. 更多关于热门更新：

    另请注意，临时表需要手动VACUUM& ANALYZE。我引用手册CREATE TABLE：

    autovacuum 守护进程无法访问，因此无法清理或分析临时表。因此，应通过会话 SQL 命令执行适当的清理和分析操作。例如，如果要在复杂查询中使用ANALYZE临时表，则在填充临时表后在临时表上运行是明智的。

    • 26

  3. 虽然我同意最好使用自动功能而不是在数据库范围内运行它，但在大多数情况下，每个表的调整都是必要的。

    我不太同意 postgres 将vacuum 和analyze 结合在一起的设计选择，我见过几个实例，其中执行大量插入/更新但很少删除的数据库从未完成分析并开始表现不佳。

    解决方案是进入那些被大量使用并受到大量查询的表，并将这些表的自动分析设置设置为它们被分析一次或每隔一天进行一次分析的地方。

    您可以在自动真空选项卡上的 gui 中访问每个表的设置，您将在那里看到可以独立于真空设置的分析设置。

    设置最终出现在 reloptions 表中，可以通过查询查看

    SELECT c.relname, c.reloptions FROM pg_class c where reloptions is not null

    一个积极分析的样本值可能是

    {autovacuum_enabled=true,autovacuum_analyze_threshold=10,autovacuum_analyze_scale_factor=.01}

    查看您的表上次获得自动分析查询的时间

    select 
    relname, 
    n_dead_tup, 
    n_tup_ins, 
    n_tup_upd, 
    n_tup_del, 
    last_autoanalyze, 
    autoanalyze_count 
from pg_stat_user_tables 
where last_autoanalyze is not null 
order by last_autoanalyze desc;
    • 7

相关问题