Re: lazy vacuum sleeps with exclusive lock on table

On Fri, 2007-06-29 at 09:29 +0900, ITAGAKI Takahiro wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> > What I'm requesting here is that the sleep in count_nondeletable_pages()
> > be removed and that change backpatched to 8.2 and 8.1.
>
> Agreed. We'd better to shorten the exclusive locking as far as possible.

That is just one possibility, but I'd like to consider other
possibilities before we go for that, especially backpatched.

ISTM holding the lock across many I/Os is the thing that is causing long
lock times. Removing the sleep may not substantially reduce the time on
a busy system. Alvaro's example also shows that the number of blocks
removed could be a substantial number - reminding us that the time the
lock is held would still be O(N), whereas we would like it to be O(1).
This is important since we don't even attempt truncation until the
number of blocks is large enough to be worth bothering with.

Would it be better to keep the sleep in there, but release and
re-acquire the lock either side of the sleep? That would allow other
transactions to progress without long lock waits.

Currently, releasing the lock is a problem because the new FSM entries
are added after truncation, so any updates and inserts would probably
try to extend the relation, thus preventing further truncation. If we
did things differently, we would have no reason to fail when we attempt
to re-acquire the lock:
- analyze where the truncation point would be on the vacuum pass
- add FSM entries for all blocks below the truncation point. If that is
below a minimum of 5% of the entries/16 blocks then we can move the
truncation point higher so that the FSM entry is large enough to allow
us time to truncate.
- truncate the file, one bite at a time as we sleep (or max 16 blocks at
a time if no sleep requested), possibly scanning forwards not back

I would still like to see VACUUM spin a few times trying to acquire the
lock before it gives up attempting to truncate. Re-running the whole
VACUUM just to get another split-second chance to truncate is not very
useful behaviour either.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com