Re: heap vacuum & cleanup locks

On Mon, Jun 6, 2011 at 2:36 AM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> Do we know if this is really a problem though ? The deadlock for
> example, can happen only when a backend tries to get a table level
> conflicting lock while holding the buffer pin and I am not sure if we
> do that.

The deadlock isn't terribly common, because, as you say, you need the
process holding the buffer pin to try to take a lock on the relation
being vacuumed that is strong enough to conflict with
ShareUpdateExclusiveLock. That's a slightly unusual thing to do.

But the problem of vacuum stalling out because it can't get the
cleanup lock is a very real one. I've seen at least one customer hit
this in production, and it was pretty painful. Now, granted, you need
some bad application design, too: you have to leave a cursor lying
around instead of running it to completion and then stopping. But
supposing you do make that mistake, you might hope that it wouldn't
cause VACUUM starvation, which is what happens today. IOW, I'm less
worried about whether the cleanup lock is slowing vacuum down than I
am about eliminating the pathological cases where an autovacuum
workers gets pinned down, stuck waiting for a cleanup lock that never
arrives. Now the table doesn't get vacuumed (bad) and the system as a
whole is one AV worker short of what it's supposed to have (also bad).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company