Thread

1. Re: autovacuum truncate exclusive lock round two

   Kevin Grittner <kgrittn@mail.com> — 2012-12-04T18:51:13Z

   Jan Wieck wrote:
   
   > [arguments for GUCs]
   
   This is getting confusing. I thought I had already conceded the
   case for autovacuum_truncate_lock_try, and you appeared to spend
   most of your post arguing for it anyway. I think. It's a little
   hard to tell. Perhaps the best thing is to present the issue to the
   list and solicit more opinions on what to do. Please correct me if
   I mis-state any of this.
   
   The primary problem this patch is solving is that in some
   workloads, autovacuum will repeatedly try to truncate the unused
   pages at the end of a table, but will continually get canceled
   after burning resources because another process wants to acquire a
   lock on the table which conflicts with the one held by autovacuum.
   This is handled by the deadlock checker, so another process must
   block for the deadlock_timeout interval each time. All work done by
   the truncate phase of autovacuum is lost on each interrupted
   attempt. Statistical information is not updated, so another attempt
   will trigger the next time autovacuum looks at whether to vacuum
   the table.
   
   It's obvious that this pattern not only fails to release
   potentially large amounts of unused space back to the OS, but the
   headbanging can continue to consume significant resources and for
   an extended period, and the repeated blocking for deadlock_timeout
   could cause latency problems.
   
   The patch has the truncate work, which requires
   AccessExclusiveLock, check at intervals for whether another process
   is waiting on its lock. That interval is one of the timings we need
   to determine, and for which a GUC was initially proposed. I think
   that the check should be fast enough that doing it once every 20ms
   as a hard-coded interval would be good enough. When it sees this
   situation, it truncates the file for as far as it has managed to
   get, releases its lock on the table, sleeps for an interval, and
   then checks to see if the lock has become available again.
   
   How long it should sleep between tries to reacquire the lock is
   another possible GUC. Again, I'm inclined to think that this could
   be hard-coded. Since autovacuum was knocked off-task after doing
   some significant work, I'm inclined to make this interval a little
   bigger, but I don't think it matters a whole lot. Anything between
   20ms and 100ms seens sane. Maybe 50ms?
   
   At any point that it is unable to acquire the lock, there is a
   check for how long this autovacuum task has been starved for the
   lock. Initially I was arguing for twice the deadlock_timeout on the
   basis that this would probably be short enough not to leave the
   autovacuum worker sidelined for too long, but long enough for the
   attempt to get past a single deadlock between two other processes.
   This is the setting Jan is least willing to concede.
   
   If the autovacuum worker does abandon the attempt, it will keep
   retrying, since we go out of our way to prevent the autovacuum
   process from updating the statistics based on the "incomplete"
   processing. This last interval is not how long it will attempt to
   truncate, but how long it will keep one autovacuum worker making
   unsuccessful attempts to acquire the lock before it is put to other
   uses. Workers will keep coming back to this table until the
   truncate phase is completed, just as it does without the patch; the
   difference being that anytime it gets the lock, even briefly, it is
   able to persist some progress.
   
   So the question on the table is which of these three intervals
   should be GUCs, and what values to use if they aren't.
   
   -Kevin
   
   
   

2. Re: autovacuum truncate exclusive lock round two

   Jan Wieck <janwieck@yahoo.com> — 2012-12-04T19:05:58Z

   On 12/4/2012 1:51 PM, Kevin Grittner wrote:
   > Jan Wieck wrote:
   >
   >> [arguments for GUCs]
   >
   > This is getting confusing. I thought I had already conceded the
   > case for autovacuum_truncate_lock_try, and you appeared to spend
   > most of your post arguing for it anyway. I think. It's a little
   > hard to tell. Perhaps the best thing is to present the issue to the
   > list and solicit more opinions on what to do. Please correct me if
   > I mis-state any of this.
   >
   > The primary problem this patch is solving is that in some
   > workloads, autovacuum will repeatedly try to truncate the unused
   > pages at the end of a table, but will continually get canceled
   > after burning resources because another process wants to acquire a
   > lock on the table which conflicts with the one held by autovacuum.
   > This is handled by the deadlock checker, so another process must
   > block for the deadlock_timeout interval each time. All work done by
   > the truncate phase of autovacuum is lost on each interrupted
   > attempt. Statistical information is not updated, so another attempt
   > will trigger the next time autovacuum looks at whether to vacuum
   > the table.
   >
   > It's obvious that this pattern not only fails to release
   > potentially large amounts of unused space back to the OS, but the
   > headbanging can continue to consume significant resources and for
   > an extended period, and the repeated blocking for deadlock_timeout
   > could cause latency problems.
   >
   > The patch has the truncate work, which requires
   > AccessExclusiveLock, check at intervals for whether another process
   > is waiting on its lock. That interval is one of the timings we need
   > to determine, and for which a GUC was initially proposed. I think
   > that the check should be fast enough that doing it once every 20ms
   > as a hard-coded interval would be good enough. When it sees this
   > situation, it truncates the file for as far as it has managed to
   > get, releases its lock on the table, sleeps for an interval, and
   > then checks to see if the lock has become available again.
   >
   > How long it should sleep between tries to reacquire the lock is
   > another possible GUC. Again, I'm inclined to think that this could
   > be hard-coded. Since autovacuum was knocked off-task after doing
   > some significant work, I'm inclined to make this interval a little
   > bigger, but I don't think it matters a whole lot. Anything between
   > 20ms and 100ms seens sane. Maybe 50ms?
   >
   > At any point that it is unable to acquire the lock, there is a
   > check for how long this autovacuum task has been starved for the
   > lock. Initially I was arguing for twice the deadlock_timeout on the
   > basis that this would probably be short enough not to leave the
   > autovacuum worker sidelined for too long, but long enough for the
   > attempt to get past a single deadlock between two other processes.
   > This is the setting Jan is least willing to concede.
   >
   > If the autovacuum worker does abandon the attempt, it will keep
   > retrying, since we go out of our way to prevent the autovacuum
   > process from updating the statistics based on the "incomplete"
   > processing. This last interval is not how long it will attempt to
   > truncate, but how long it will keep one autovacuum worker making
   > unsuccessful attempts to acquire the lock before it is put to other
   > uses. Workers will keep coming back to this table until the
   > truncate phase is completed, just as it does without the patch; the
   > difference being that anytime it gets the lock, even briefly, it is
   > able to persist some progress.
   
   That is all correct.
   
   >
   > So the question on the table is which of these three intervals
   > should be GUCs, and what values to use if they aren't.
   
   I could live with all the above defaults, but would like to see more 
   comments on them.
   
   
   Jan
   
   -- 
   Anyone who trades liberty for security deserves neither
   liberty nor security. -- Benjamin Franklin
   
   
   

3. Re: autovacuum truncate exclusive lock round two

   Robert Haas <robertmhaas@gmail.com> — 2012-12-05T15:07:41Z

   On Tue, Dec 4, 2012 at 2:05 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
   >> So the question on the table is which of these three intervals
   >> should be GUCs, and what values to use if they aren't.
   >
   > I could live with all the above defaults, but would like to see more
   > comments on them.
   
   I largely agree with what's already been said.  The only interval that
   seems to me to maybe need its own knob is the total time after which
   the autovacuum worker will give up.  If we set it to 2 *
   deadlock_timeout, some people might find that a reason to raise
   deadlock_timeout.  Since people *already* raise deadlock_timeout to
   obscenely high values (a minute?  an hour???) and then complain that
   things blow up in their face, I think there's a decent argument to be
   made that piggybacking anything else on that setting is unwise.
   
   Against that, FWICT, this problem only affects a small number of
   users: Jan is the only person I can ever remember reporting this
   issue.  I'm not dumb enough to think he's the only person who it
   affects; but my current belief is that it's not an enormously common
   problem.  So the main argument I can see against adding a GUC is that
   the problem is too marginal to justify a setting of its own.  What I
   really see as the key issue is: suppose we hardcode this to say 2
   seconds.  Is that going to fix the problem effectively for 99% of the
   people who have this problem, or for 25% of the people who have this
   problem?  In the former case, we probably don't need a GUC; in the
   latter case, we probably do.
   
   -- 
   Robert Haas
   EnterpriseDB: http://www.enterprisedb.com
   The Enterprise PostgreSQL Company