Re: autovacuum truncate exclusive lock round two - Mailing list pgsql-hackers

On Friday, October 26, 2012 10:59 AM Amit Kapila wrote:
> On Thursday, October 25, 2012 9:46 PM Jan Wieck wrote:
> > On 10/25/2012 10:12 AM, Stephen Frost wrote:
> > > Jan,
> > >
> > > * Jan Wieck (JanWieck@Yahoo.com) wrote:
> > >> The problem case this patch is dealing with is rolling window
> tables
> > >> that experienced some bloat. The typical example is a log table,
> > >> that has new data constantly added and the oldest data constantly
> > >> purged out. This data normally rotates through some blocks like a
> > >> rolling window. If for some reason (purging turned off for example)
> > >> this table bloats by several GB and later shrinks back to its
> normal
> > >> content, soon all the used blocks are at the beginning of the heap
> > >> and we find tens of thousands of empty pages at the end. Only now
> > >> does the second scan take more than 1000ms and autovacuum is at
> risk
> > >> to get killed while at it.
> > >
> > > My concern is that this could certainly also happen to a heavily
> > updated
> > > table in an OLTP type of environment where the requirement to take a
> > > heavy lock to clean it up might prevent it from ever happening..  I
> > was
> > > simply hoping we could find a mechanism to lock just those pages
> we're
> > > getting ready to nuke rather than the entire relation.  Perhaps we
> can
> > > consider how to make those changes alongside of changes to eliminate
> > or
> > > reduce the extent locking that has been painful (for me at least)
> when
> > > doing massive parallel loads into a table.
> >
> > I've been testing this with loads of 20 writes/s to that bloated
> table.
> > Preventing not only the clean up, but the following ANALYZE as well is
> > precisely what happens. There may be multiple ways how to get into
> this
> > situation, but once you're there the symptoms are the same. Vacuum
> fails
> > to truncate it and causing a 1 second hiccup every minute, while
> vacuum
> > is holding the exclusive lock until the deadlock detection code of
> > another transaction kills it.
> >
> > My patch doesn't change the logic how we ensure that we don't zap any
> > data by accident with the truncate and Tom's comments suggest we
> should
> > stick to it. It only makes autovacuum check frequently if the
> > AccessExclusiveLock is actually blocking anyone and then get out of
> the
> > way.
> >
> > I would rather like to discuss any ideas how to do all this without 3
> > new GUCs.
> >
> > In the original code, the maximum delay that autovacuum can cause by
> > holding the exclusive lock is one deadlock_timeout (default 1s). It
> > would appear reasonable to me to use max(deadlock_timeout/10,10ms) as
> > the interval to check for a conflicting lock request. For another
> > transaction that needs to access the table this is 10 times faster
> than
> > it is now and still guarantees that autovacuum will make some progress
> > with the truncate.
> 
>   One other way could be to check after every few pages for a
> conflicting
> lock request.
> 
> > The other two GUCs control how often and how fast autovacuum tries to
> > acquire the exclusive lock in the first place. Since we actively
> release
> > the lock *because someone needs it* it is pretty much guaranteed that
> > the immediate next lock attempt fails. We on purpose do a
> > ConditionalLockRelation() because there is a chance to deadlock. The
> > current code only tries one lock attempt and gives up immediately. I
> > don't know from what to derive a good value for how long to retry,
> 
>   Can't we do something like, after nap check for conditional lock and
> if it
> didn't get
>   then get lock unconditionally.
>   The reason why after your implementation it might be okay to have lock
> unconditionally after one try is that
>   anyway after every few pages or after small time, it will release the
> lock
> if there is any waiter.

I am sorry, at this point trying to take unconditional X lock can lead to
deadlock, so above is not possible.

> > but
> > the nap time in between tries could be a hardcoded 20ms or using the
> > cost based vacuum nap time (which defaults to 20ms).
> 
> I think using cost based vacuum nap time or default value is good.
> 
> Adding new parameters might have user/administrator overhead, it is
> always
> better if it can be intelligently decided by database itself.
> However if you feel these are parameters which can vary based on
> different
> kind of usage, then I think it is better to expose it through
> configuration
> parameters to users.
> 
> With Regards,
> Amit Kapila.
> 
> 
> 
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers