PostgreSQL VACUUM 之深入浅出 (三)

VACUUM 相关参数

对 VACUUM 有了一定的了解之后，下面系统介绍下 VACUUM 相关参数。

VACUUM 相关参数主要分为三大类。

第一类 与资源相关参数

#-----------------------------
# RESOURCE USAGE (except WAL)
#-----------------------------
# - Memory -
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0                  # 0-100 milliseconds (0 disables)
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

这里有两部分。

第一部分是内存相关。主要是 autovacuum_work_mem，默认值为 -1，即同 maintenance_work_mem。maintenance_work_mem 默认值为 64MB。

第二部分是 Cost-Based Vacuum Delay。

当 VACUUM 工作超出一定量之后，会 sleep 一段时间。

一定量是多少呢？是 vacuum_cost_limit。默认值为 200。

sleep 多长时间呢？是 vacuum_cost_delay 。默认值是 0，即不 sleep。

工作量又是怎么算出来的？根据要 VACUUM 的 page 的不同，其 cost 是不一样的。

以下是三种不同 page 的 cost，默认值分别为 1、10、20，基本不用调整。

vacuum_cost_page_hit - The estimated cost for vacuuming a buffer found in the shared buffer cache.

vacuum_cost_page_miss - The estimated cost for vacuuming a buffer that has to be read from disk.

vacuum_cost_page_dirty - The estimated cost charged when vacuum modifies a block that was previously clean.

日常工作中手动 VACUUM 时主要调整 vacuum_cost_limit 和 vacuum_cost_delay 。如调整为:

vacuum_cost_delay = 2
vacuum_cost_limit = 2000

即当 VACUUM 工作量超出 2000 之后，sleep 2ms。

需要注意，手动 VACUUM 和 AUTOVACUUM 的参数是不一样的。当 AUTOVACUUM 参数为 -1 时，则同手动 VACUUM 参数。

手动 VACUUM 对应的参数是 maintenance_work_mem，vacuum_cost_delay 和 vacuum_cost_limit。

AUTOVACUUM 对应的参数是 autovacuum_work_mem，autovacuum_vacuum_cost_delay，autovacuum_vacuum_cost_limit。

可以从下面 AUTOVACUUM 参数中可以看到， autovacuum_vacuum_cost_delay 默认值为 20ms，这样的话，AUTOVACUUM 运行时其对数据库影响较小。PostgreSQL 12 开始，其默认值调整为了 2ms。

#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

第二类 AUTOVACUUM 相关参数

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
 
#autovacuum = on                        # Enable autovacuum subprocess?  'on'
                                        # requires track_counts to also be on.
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
                                        # their durations, > 0 logs only
                                        # actions running at least this number
                                        # of milliseconds.
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                        # (change requires restart)
#autovacuum_naptime = 1min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 50       # min number of row updates before
                                        # vacuum
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                                        # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000        # maximum multixact age
                                        # before forced vacuum
                                        # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

以下参数前面已有提到，一般调整为通用配置后基本不调整，调整的话多是调整表级的参数，即根据不同的表设置不同的参数。

#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

下面两个参数是当某个表的 age 达到一定阈值后，AUTOVACUUM 会对整个数据库实例进行 aggressive vacuum 以避免 wraparound，即使表没有 dead tuple。数据库运行良好的话，很少会触发。

#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age = 400000000

当数据库中表比较多，甚至一个实例中数据库也比较多的情况，可适当增大 autovacuum_max_workers。

#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                        # (change requires restart)

问题来了，增大 autovacuum_max_workers 后，一定会提高 AUTOVACUUM 速度吗？

这里需要注意，autovacuum_vacuum_cost_limit 是所有 autovacuum worker 所用 cost 之和达到 limit 之后 sleep，增大 autovacuum_max_workers 之后，每个 worker 平均的 cost limit 就小了，即就相对更容易达到 limit，这样做同样的工作，就会 sleep 更多的时间，反而就更慢了。

所以，在增大 autovacuum_max_workers 之后，可以相应比例增大 autovacuum_vacuum_cost_limit。

第三类 FREEZE 相关参数

以下是 FREEZE 相关参数，以后将系统介绍 FREEZE，本文不再展开讨论。

#------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000

公众号

关注 DBA Daily 公众号，第一时间收到文章的更新。
通过一线 DBA 的日常工作，学习实用数据库技术干货！

公众号优质文章推荐

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 扩展创建失败原因调查