Re: heap vacuum & cleanup locks

On Sun, Jun 5, 2011 at 4:03 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> We've occasionally seen problems with VACUUM getting stuck for failure
> to acquire a cleanup lock due to, for example, a cursor holding a pin
> on the buffer page.  In the worst case, this can cause an undetected
> deadlock, if the backend holding the buffer pin blocks trying to
> acquire a heavyweight lock that is in turn blocked by VACUUM.

Those deadlocks can be detected in exactly the same way as is used for
Hot Standby.

Cleanup waiter registers interest in pin, anyone with a lock request
that must wait checks to see if they hold a pin that would cause
deadlock.

I'll look at doing a patch for that. Shouldn't take long.

> A while
> back, someone (Greg Stark? me?) floated the idea of not waiting for
> the cleanup lock.  If we can't get it immediately, or within some
> short period of time, then we just skip the page and continue on.

Separately, that sounds like a great idea and it's simple to implement
- patch attached.

Enhancements to that are that I don't see any particular reason why
the heap pages need to be vacuumed in exactly sequential order. If
they are on disk, reading sequentially is useful, in which case nobody
has a pin and so we will continue. But if the blocks are already in
shared_buffers, then the sequential order doesn't matter at all. So we
could skip pages and then return to them later on.

Also, ISTM that LockBufferForCleanup() waits for just a single buffer,
but it could equally well wait for multiple buffers at the same time.
By this, we would then be able to simply register our interest in
multiple buffers and get woken as soon as one of them were free. That
way we could read the blocks sequentially, but lock and clean them out
of sequence if necessary. Do this in chunks, so it plays nicely with
buffer strategy. (Patch doesn't do that yet).

(Not sure if the patch handles vacuum map correctly if we skip the
page, but its a reasonable prototype for discussion).

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