From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ernesto QuiƱones <ernestoq@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Question about VACUUM
Date: Mon, 05 Dec 2011 12:36:34 -0600
Message-ID: <4EDCBAD2020000250004384F@gw.wicourts.gov> (raw)
In-Reply-To: <CAMB2kH73WpbY+E8bBjU9_Yopmo3PdLaGwhMT5tZ3fQ_nvHODLQ@mail.gmail.com>
References: <4ED9F32A02000025000437EC@gw.wicourts.gov>
<CAMB2kH73WpbY+E8bBjU9_Yopmo3PdLaGwhMT5tZ3fQ_nvHODLQ@mail.gmail.com>
Ernesto QuiƱones<ernestoq@gmail.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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, ernestoq@gmail.com
Subject: Re: Question about VACUUM
In-Reply-To: <4EDCBAD2020000250004384F@gw.wicourts.gov>
References: <4ED9F32A02000025000437EC@gw.wicourts.gov> <CAMB2kH73WpbY+E8bBjU9_Yopmo3PdLaGwhMT5tZ3fQ_nvHODLQ@mail.gmail.com> <4EDCBAD2020000250004384F@gw.wicourts.gov>
* 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