PostgreSQL VACUUM 之深入浅出 (四)

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;

另外,将 autovacuum_naptime 设置为 3min 以给删除数据留够时间。

alvindb=> SHOW autovacuum_naptime;
autovacuum_naptime
--------------------
3min
(1 row)

AUTOVACUUM 测试

首先采用默认参数:

SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
64MB
(1 row)
SHOW autovacuum_work_mem;
autovacuum_work_mem
---------------------
-1
(1 row)
SHOW autovacuum_vacuum_cost_delay;
autovacuum_vacuum_cost_delay
------------------------------
20ms
(1 row)
SHOW autovacuum_vacuum_cost_limit;
autovacuum_vacuum_cost_limit
------------------------------
-1
(1 row)
SHOW vacuum_cost_limit;
vacuum_cost_limit
-------------------
200
(1 row)

执行测试用例的同时,通过如下 SQL 查询

SELECT * FROM pg_stat_activity WHERE backend_type ~ 'autovacuum worker' AND pid <> pg_backend_pid();\watch 1

可以看到, AUTOVACUUM 已触发,并且 autovacuum worker 已启动,先是 query 为空,而后 query 为 VACUUM ANALYZE public.pgbench_accounts。

2021年11月07日 星期日 23时17分09秒 (every 1s)

-[ RECORD 1 ]----+------------------------------
datid | 37509
datname | alvindb
pid | 16660
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2021-11-07 23:17:09.427627+08
xact_start | 2021-11-07 23:17:09.426378+08
query_start |
state_change |
wait_event_type |
wait_event |
state |
backend_xid |
backend_xmin | 13180269
query |
backend_type | autovacuum worker 2021年11月07日 星期日 23时17分10秒 (every 1s) -[ RECORD 1 ]----+---------------------------------------------------
datid | 37509
datname | alvindb
pid | 16660
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2021-11-07 23:17:09.427627+08
xact_start | 2021-11-07 23:17:09.459083+08
query_start | 2021-11-07 23:17:09.459083+08
state_change | 2021-11-07 23:17:09.459084+08
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 13180269
query | autovacuum: VACUUM ANALYZE public.pgbench_accounts
backend_type | autovacuum worker

完成后,根据 last_autoanalyzelast_autovacuum 得知是先做了 VACUUM,然后做 ANALYZE:

schemaname                      | public
relname | pgbench_accounts
autovacuum_vacuum_scale_factor | 0.1
autovacuum_vacuum_threshold | 2000
autovacuum_analyze_scale_factor | 0.05
autovacuum_analyze_threshold | 2000
n_live_tup | 30000000
reltuples | 30000000
autovacuum_analyze_trigger | 1502001
n_mod_since_analyze | 0
rows_to_mod_before_analyze | 1502001
last_autoanalyze | 2021-11-07 23:22:41.640812+08
autovacuum_vacuum_trigger | 3002001
n_dead_tup | 142848
rows_to_delete_before_vacuum | 2859153
last_autovacuum | 2021-11-07 23:22:14.06792+08

查看 PostgreSQL 日志,得知 VACUUM 用时 304.60 s,ANALYZE 用时 27.56 s。

[    2021-11-07 23:22:14.067 CST 16660 6187edf5.4114 1 3/238595 13180270]LOG:  automatic vacuum of table "alvindb.public.pgbench_accounts": in
dex scans: 2
pages: 35699 removed, 165914 remain, 576 skipped due to pins, 0 skipped frozen
tuples: 6190880 removed, 30142834 remain, 0 are dead but not yet removable, oldest xmin: 13180269
buffer usage: 419596 hits, 40795 misses, 100492 dirtied
avg read rate: 4.185 MB/s, avg write rate: 10.310 MB/s
system usage: CPU: user: 14.02 s, system: 1.41 s, elapsed: 304.60 s [ 2021-11-07 23:22:41.640 CST 16660 6187edf5.4114 2 3/238596 13180271]LOG: automatic analyze of table "alvindb.public.pgbench_accounts" system usage: CPU: user: 3.25 s, system: 7.58 s, elapsed: 27.56 s

通过调整参数,逐步并反复测试,结果如下:

maintenance_work_mem autovacuum_vacuum_cost_delay vacuum_cost_limit AUTOVACUUM 用时
64MB 20ms 200 304.60 s + 27.56 s
64MB 2ms 200 39.45 s +8.73 s
64MB 2ms 2000 18.79 s + 5.50 s
64MB 2ms 200 42.04 s + 8.00 s
64MB 20ms 200 329.72 s + 22.82 s
64MB 0ms 2000 17.72 s + 3.45 s
512MB 0ms 2000 12.75 s + 3.35 s
64MB 0ms 2000 15.13 s + 5.45 s

根据如上测试,可以验证,适当增大 autovacuum_work_memautovacuum_vacuum_cost_limit、减少 autovacuum_vacuum_cost_delay 可提高 AUTOVACUUM 性能。

手动 VACUUM ANALYZE 测试

下面测试手动 VACUUM ANALYZE。测试方法基本与 AUTOVACUUM 一样。

这里主要简单测试下 maintenance_work_mem,其他不再重复测试。

关闭 autovacuum 并将 vacuum_cost_delay 设置为 0,并手动执行如下 SQL:

VACUUM ANALYZE pgbench_accounts;

用时统计如下:

maintenance_work_mem vacuum_cost_delay vacuum_cost_limit AUTOVACUUM 用时
64MB 0ms 2000 23.137 s
128MB 0ms 2000 18.284 s
64MB 0ms 2000 24.144 s

根据如上测试,可以验证,适当增大 maintenance_work_memvacuum_cost_limit、减少 vacuum_cost_delay 可提高 AUTOVACUUM 性能。

与 AUTOVACUUM 不同的是,手动 VACUUM 可以通过如下方式设置参数。这样,在实际工作中,就可以灵活调整参数而不需要改配置文件了。

SET vacuum_cost_delay = 10;
VACUUM ANALYZE pgbench_accounts;

公众号

关注 DBA Daily 公众号,第一时间收到文章的更新。

通过一线 DBA 的日常工作,学习实用数据库技术干货!

PostgreSQL VACUUM 之深入浅出 (四)

公众号优质文章推荐

PostgreSQL VACUUM 之深入浅出

华山论剑之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失败原因调查

PG 数据导入 Hive 乱码问题调查

PostGIS 扩展创建失败原因调查

上一篇:HTTP压缩的过程


下一篇:VueRouter 源码深度解析