Re: lazy vacuum sleeps with exclusive lock on table

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > 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.
>
> Are you sure that that is, and always will be, the only sleep in that
> part of the code path?

It is currently, as far as I can see, the only sleep. I think we could
backpatch the removal of that call, and consider changing the
cost_delay parameters when we acquire the exclusive lock in HEAD.

I haven't tried with crazy features like gist indexes though. Maybe
there's more sleep calls in the vacuum code for those. But from what I
can gather, all the index clean up is done before trying to truncate the
relation so we should be safe.

Also, we don't release the exclusive lock; we hold on it till commit.
Maybe it would be a good idea to release it as soon as we're done with
it.

> Seems like it might be better to adjust the cost_delay parameters after
> we acquire exclusive lock. I'm not sure dialing them all the way back
> to zero is a good idea, but certainly we could make them more
> aggressive.

Hmm. The less we keep the exclusive lock, the better. I think an extra
bit of I/O for a short moment is warranted in this case -- better than
holding a lock that lots of processes could be waiting on.

> >> Seems like VACUUM shouldn't try just once to get the lock.
>
> > We don't know how many pages we can truncate until after we have
> > acquired the exclusive lock and examined the pages in question, scanning
> > backwards from the end of the table.
>
> We could estimate this during the forward scan. The backward scan is
> still necessary to see if anyone has inserted tuples after we looked at
> a page, but certainly a page that VACUUM failed to empty will still be
> nonempty, so we can determine an upper bound on how much might be
> truncatable.
>
> However, that's an orthogonal concern and should probably be discussed
> separately.

Right (and furthermore we shouldn't backpatch it).

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"This is a foot just waiting to be shot" (Andrew Dunstan)