Re: controlling autovacuum during the day.

Michael Fuhr wrote:
> On Wed, Dec 17, 2008 at 09:47:23AM -0500, Tom Lane wrote:
> > "John Lister" <john(dot)lister-ps(at)kickstone(dot)com> writes:
> > > Cheers for the quick reply. I've tweaked them quite a bit, but we have quite
> > > a few heavily updated tables that i'd like vacuuming to keep them in check.
> > > Unfortunately the autovacuum does a FULL vacuum every so often locking the
> > > tables for quite a long time, i'd like to move these to the evening if
> > > possible.
> >
> > Huh? Autovacuum *never* does VACUUM FULL.
>
> Perhaps autovacuum is shrinking the table after finding lots of empty
> pages at the end, as in what VACUUM VERBOSE is logging here:
>
> INFO: "foo": truncated 11944384 to 8877366 pages
>
> I think this acquires an AccessExclusiveLock. I've seen this take
> hours in the case of a table with a lot of empty pages.

There was a bug in earlier versions which caused lazy vacuum to do
cost-based delays during this phase, which caused the lock to be held
for ridiculous lengths of time. This was fixed in 8.2.something; it
shouldn't sleep anymore, but it does need to scan those pages in order
to truncate, so it can still take a while.

I think it was fixed in 8.2.4:

revision 1.81.2.1
date: 2007-09-10 13:58:50 -0400; author: alvherre; state: Exp; lines: +6 -2;
Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
an exclusive lock on the table at this point, which we want to release as soon
as possible. This is called in the phase of lazy vacuum where we truncate the
empty pages at the end of the table.

An alternative solution would be to lower the vacuum delay settings before
starting the truncating phase, but this doesn't work very well in autovacuum
due to the autobalancing code (which can cause other processes to change our
cost delay settings). This case could be considered in the balancing code, but
it is simpler this way.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.