From: Bruce McAlister <bruce.mcalister@blueface.ie>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
Date: Fri, 9 Mar 2007 10:45:16 -0000
Message-ID: <esrdsb$1klg$1@news.hub.org> (raw)
References: <esh2mg$16vu$1@news.hub.org>
<19106.1173111068@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce.mcalister@blueface.ie
Subject: Re: PostgreSQL 8.2.3 VACUUM Timings/Performance
In-Reply-To: <esrdsb$1klg$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox