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. 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. > > Today I had what might be a better idea: don't try to acquire a > cleanup lock at all. Instead, acquire an exclusive lock. After > having done so, observe the pin count. If there are no other buffer > pins, that means our exclusive lock is actually a cleanup lock, and we > proceed as now. If other buffer pins do exist, then we can't > defragment the page, but that doesn't mean no useful work can be done: > we can still mark used line pointers dead, or dead line pointers > unused. We cannot defragment, but that can be done either by the next > VACUUM or by a HOT cleanup. We can even arrange - using existing > mechanism - to leave behind a hint that the page is a good candidate > for a HOT cleanup, by setting pd_prune_xid to, say, FrozenXID. > > Like the idea of skipping pages on which we can't acquire a cleanup > lock altogether, this should prevent VACUUM from getting stuck trying > to lock a heap page. While buffer pins can be held for extended > periods of time, I don't think there is any operation that holds a > buffer content lock more than very briefly. Furthermore, unlike the > idea of skipping the page altogether, we could use this approach even > during an anti-wraparound vacuum. > > Thoughts?
Not waiting seems like a good idea. Not returning to the block while it is in RAM or not cleaning the block at all would cause a different performance issues, which I would wish to avoid. Hot Standby has specific code to avoid this situation. Perhaps you could copy that, not sure. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
