Re: (auto)vacuum truncate exclusive lock

On 2013-04-12 13:09:02 -0400, Tom Lane wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> > OK, will review that to confirm;but assuming that's right, and
> > nobody else is already working on a fix, I propose to do the
> > following:
>
> > (1) Restore the prior behavior of the VACUUM command. This was
> > only ever intended to be a fix for a serious autovacuum problem
> > which caused many users serious performance problems -- in some
> > cases including unscheduled down time. I also saw sites where,
> > having been bitten by this, they disabled autovacuum and later ran
> > into problems with bloat and/or xid wraparound.
>
> > (2) If autovacuum decides to try to truncate but the lock cannot
> > be initially acquired, and analyze is requested, skip the
> > truncation and do the autoanalyze. If the table is so hot that we
> > cannot get the lock, the space may get re-used soon, and if not
> > there is a good chance another autovacuum will trigger soon. If
> > the user really wants the space released to the OS immediately,
> > they can run a manual vacuum to force the issue.
>
> I think that the minimum appropriate fix here is to revert the hunk
> I quoted, ie take out the suppression of stats reporting and analysis.
>
> However, we're still thinking too small. I've been wondering whether we
> couldn't entirely remove the dirty, awful kluges that were installed in
> the lock manager to kill autovacuum when somebody blocked behind it.
> This mechanism should ensure that AV never takes an exclusive lock
> for long enough to be a serious problem, so do we need that anymore?

Wouldn't that make DROP TABLE stop working while autovac is processing
the table? Considering how long e.g. a full table vacuum on a huge table
can take that doesn't seem to be acceptable.
Sure, you can manually kill the autovac process, but that will soon be
restarted. So you have to know that you need to start the DROP TABLE
beforehand so it will get the lock quicker and such.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services