高删除/更新率下的 PostgreSQL11 空间重用

Question

我们正在为我们的产品评估 PostgreSQL 11.1。拥有每秒 4251 次更新、每秒约 1000 次删除和每秒约 3221 次插入以及每天 10 亿个事务的系统，我们面临着 PostgreSQL 不重用其(删除/更新)空间并且表的大小不断增加的挑战.

我们配置了积极的 Autovacuum 设置以避免环绕情况。还尝试添加定期执行 vacuum analyze 和 vacuum –而且仍然没有空间重用。 (只有 vacuum full 或 pg_repack 释放空间给操作系统——但这不是重用。)

以下是我们的真空设置:

autovacuum                          | on
vacuum_cost_limit                   | 6000
autovacuum_analyze_threshold        | 50
autovacuum_vacuum_threshold         | 50
autovacuum_vacuum_cost_delay        | 5
autovacuum_max_workers              | 32
autovacuum_freeze_max_age           | 2000000
autovacuum_multixact_freeze_max_age | 2000000
vacuum_freeze_table_age             | 20000
vacuum_multixact_freeze_table_age   | 20000
vacuum_cost_page_dirty              | 20
vacuum_freeze_min_age               | 10000
vacuum_multixact_freeze_min_age     | 10000
log_autovacuum_min_duration         | 1000
autovacuum_naptime                  | 10
autovacuum_analyze_scale_factor     | 0
autovacuum_vacuum_scale_factor      | 0
vacuum_cleanup_index_scale_factor   | 0
vacuum_cost_delay                   | 0
vacuum_defer_cleanup_age            | 0
autovacuum_vacuum_cost_limit        | -1
autovacuum_work_mem                 | -1

Answer 1

您对 PostgreSQL 的要求特别苛刻。

• 您应该将该表的 autovacuum_vacuum_cost_delay 设置为 0。

• 将 autovacuum_max_workers 和 autovacuum_naptime 重置为默认值。

• 将 autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor 重置为其默认值或稍低的值。

您的问题是不是 autovacuum 运行不够频繁，问题是它太慢而无法跟上。

即便如此，您可能也只能通过热更新来处理此工作负载:

• 确保经常更新的属性不是任何索引的一部分。

• 创建 fillfactor 低于 100 的表，比如 70。

HOT 更新通常避免了对 VACUUM 和更新索引的需要。

检查 pg_stat_us-er_tables 的 n_tup_hot_upd 列，看看它是否有效。