Skip site navigation (1) Skip section navigation (2)

Re: lazy vacuum sleeps with exclusive lock on table

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: lazy vacuum sleeps with exclusive lock on table
Date: 2007-06-29 01:31:57
Message-ID: 20070629013157.GH32626@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-06-29 02:06:09
Subject: Re: 'SET LOCAL ROLE blah;' doesn't work?
Previous:From: Bruce MomjianDate: 2007-06-29 01:07:50
Subject: Re: How do we create the releases?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group