On Sun, Jun 5, 2011 at 4:03 AM, Robert Haas <robertmh...@gmail.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
vacuum_skip_busy_pages.v1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
