Hi Tom,
Thanks for the suggestion. It's been a while since I replied to this as I
had to go and do some further investigation of the docs with regards the
autovacuum daemons configuration. According to the documentation, the
formula's for the vacuum and analyze are as follows:
Vacuum
vacuum threshold = vacuum base threshold + vacuum scale factor * number
of tuples
Analyze
analyze threshold = analyze base threshold + analyze scale factor *
number of tuples
My current settings for autovacuum are as follows:
# - Cost-Based Vacuum Delay -
vacuum_cost_delay = 200 # 0-1000 milliseconds
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 # 0-10000 credits
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on #
enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
autovacuum_naptime = 1min # time
between autovacuum runs
autovacuum_vacuum_threshold = 500 # min # of tuple
updates before
# vacuum
autovacuum_analyze_threshold = 250 # min # of tuple
updates before
# analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel
size before
# vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of rel
size before
# analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before
forced vacuum
# (change requires restart)
autovacuum_vacuum_cost_delay = -1 # default vacuum
cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum
cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
Thus to make the autovacuum more aggressive I am thinking along the lines of
changing the following parameters:
autovacuum_vacuum_threshold = 250
autovacuum_analyze_threshold = 125
The documentation also mentions that when the autovacuum runs it selects a
single database to process on that run. This means that the particular table
that we are interrested in will only be vacuumed once every 17 minutes,
assuming we have 18 databases and the selection process is sequential
through the database list.
