PostgreSQL VACUUM 之深入浅出 (三)

2021-11-20 19:58:13

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