PostgreSQL VACUUM 之深入浅出 (四)

最新推荐文章于 2022-12-11 22:02:20 发布

DBADaily

最新推荐文章于 2022-12-11 22:02:20 发布

阅读量429

点赞数

分类专栏： PostgreSQL 文章标签： postgresql database 数据库

本文链接：https://blog.csdn.net/DBADaily/article/details/123008440

版权

VACUUM 参数优化

上面已经介绍过了以下设置表级 AUTOVACUUM 相关参数和 autovacuum_max_workers：

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);

下面就以下常用 VACUUM 参数详细介绍如何进行调优。

maintenance_work_mem 参数

#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem

vacuum_cost_delay 参数

#vacuum_cost_delay = 0
#autovacuum_vacuum_cost_delay = 20ms

vacuum_cost_limit 参数

#vacuum_cost_limit = 200
#autovacuum_vacuum_cost_limit = -1

参数优化测试用例

使用 pgbench 生成 5000 万测试数据。

pgbench -i -s 500 alvindb

设置表级 AUTOVACUUM 相关参数：

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);

删除 2000 万数据：

DELETE FROM pgbench_accounts WHERE aid>=1500001 AND aid <=3500000;
DELETE FROM pgbench_accounts WHERE aid>=15000001 AND aid <=17000000;
DELETE FROM pgbench_accounts WHERE aid>=25000001 AND aid <=28000000;
DELETE FROM pgbench_accounts WHERE aid>=35000001 AND aid <=38000000;
DELETE FROM pgbench_accounts WHERE aid>=40000001;