Ernesto Quiñones<ernestoq(at)gmail(dot)com> wrote:
I understand the impulse to run autovacuum less frequently or less
aggressively. When we first started running PostgreSQL the default
configuration was very cautious. A lot of bloat would accumulate
before it kicked in, at which point there was a noticeable
performance hit, as it worked though a large number of dead pages.
The first thing I did was to make it run less often, which only made
things worse. The numbers we settled on through testing as optimal
for us are very close to current default values (for recent major
releases).
Not only do queries run more quickly between autovacuum runs,
because there is less dead space to wade through to get the current
tuples, but the autovacuum runs just don't have the same degree of
impact -- presumably because they find less to do. Some small,
frequently updated tables when from having hundreds of pages down to
one or two.
> autovacuum_analyze_scale_factor 0,5
> autovacuum_analyze_threshold 50000
We use 0.10 + 10 in production. Defaults are now 0.10 + 50. That's
the portion of the table plus a number of rows. Analyze just does a
random sample from the table; it doesn't pass the whole table.
> autovacuum_vacuum_scale_factor 0,5
> autovacuum_vacuum_threshold 50000
We use 0.20 + 10 in production. Defaults are now 0.20 + 50. Again,
a proportion of the table (in this case what is expected to have
become unusable dead space) plus a number of unusable dead tuples.
> autovacuum_naptime 1h
A one-page table could easily bloat to hundreds (or thousands) of
pages within an hour. You will wonder where all your CPU time is
going because it will constantly be scanning the same (cached) pages
to find the one version of the row which matters. I recommend 1min.
> vacuum_cost_delay 1s
A vacuum run will never get much done at that rate. I recommend
10ms.
> vacuum_cost_limit 200
We've boosted this to 600. Once you're in a "steady state", this is
the setting you might want to adjust up or down as needed to make
cleanup aggressive enough without putting a noticeable dent in
performance while it is running.
On 8.3 I believe you still need to worry about the fsm settings.
Run your regular database vacuum with the VERBOSE option, and check
what the last few lines say. If you don't have enough memory set
aside to track free space, no vacuum regimen will prevent bloat.
-Kevin