From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention
Date: Thu, 07 Jul 2011 17:30:50 -0400
Message-ID: <4E16258A.4050007@2ndQuadrant.com> (raw)
In-Reply-To: <CAJtzaR8azbzwnu=X=v6qd2WBBBY0cEofZbnNYePxm5ROZ76LmA@mail.gmail.com>
References: <CAJtzaR8azbzwnu=X=v6qd2WBBBY0cEofZbnNYePxm5ROZ76LmA@mail.gmail.com>
On 07/07/2011 04:30 PM, D C wrote:
>
> autovacuum_naptime = 30s
> autovacuum_vacuum_threshold = 200
> autovacuum_vacuum_scale_factor = 0.5
> autovacuum_vacuum_cost_delay = 10
>
These are slightly strange settings. How did you come up with them?
The autovacuum_vacuum_scale_factor being so high is particularly
dangerous. If anything, you should be reducing that from its default of
0.2, not increasing it further.
> In addition to autovacuuming, each day, early, in the morning, we run
> a full vacuum, like this: "vacuumdb --all --full --analyze". We do
> not have any special variable set for vacuum in postgresql.conf.
>
VACUUM FULL takes an exclusive lock on the table while it runs, and it
extremely problematic for several other reasons too. See
http://wiki.postgresql.org/wiki/VACUUM_FULL for more information.
You didn't mention your PostgreSQL version so I can't be sure exactly
how bad of a problem you're causing with this, but you should almost
certainly stop doing it.
> The problem is that once or twice a week, the "vacuum full analyze"
> seems to cancel out the autovacuum that has already started at the
> same time. E.g.,
>
Yes. VACUUM FULL needs to take a large lock on the table, and it will
kick out autovacuum in that case, and cause countless other trouble
too. And if the VACUUM FULL is already running, other things will end
up getting stuck waiting for it, and all sorts of locking issues can
come out of that.
You should remove the "--full" from your daily routine, reduce
autovacuum_vacuum_scale_factor back to a reasonable number again, and
see how things go after that. You're trying to use PostgreSQL in a way
it's known not to work well right now.
> I am guessing that we can do the above by setting the
> "autovacuum_vacuum_cost_limit" to a fairly high value (rather than it
> not being set at all, as it is right now, and thus inheriting the
> "200" default value from vacuum_cost_limit).
>
The cost limit has nothing to do with the issue you're seeing. It
adjust how much work autovacuum does at any moment in time, it isn't
involved in any prioritization.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/
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: greg@2ndQuadrant.com
Subject: Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention
In-Reply-To: <4E16258A.4050007@2ndQuadrant.com>
* 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