Re: autovacuum truncate exclusive lock round two

On Wed, Oct 24, 2012 at 4:20 PM, Jan Wieck <JanWieck(at)yahoo(dot)com> wrote:
> This patch does introduce three new postgresql.conf parameters, which I
> would be happy to get rid of if we could derive them from something else.
> Something based on the deadlock timeout may be possible.
>
> autovacuum_truncate_lock_check = 100ms # how frequent to check
> # for conflicting locks
> autovacuum_truncate_lock_retry = 50 # how often to try acquiring
> # the exclusive lock
> autovacuum_truncate_lock_wait = 20ms # nap in between attempts

+1 for this general approach.

As you suggested downthread, I think that hard-coding
autovacuum_truncate_lock_check to one-tenth of the deadlock timeout
should be just fine. For the other two parameters, I doubt we need to
make them configurable at all. It's not exactly clear what to set
them to, but it does seem clear that the down side of setting them
incorrectly isn't very much as long as the defaults are roughly sane.
Personally, I'd be inclined to retry less frequently but over a
slightly longer time period - say twenty retries, one after every
100ms. But I wouldn't be upset if we settled on what you've got here,
either. We just don't want to let the total time we spend waiting for
the lock get too long, because that means pinning down an auto-vacuum
worker that might be critically needed elsewhere. So the product of
autovacuum_truncate_lock_retry and autovacuum_truncate_lock_wait
probably should not be more than a couple of seconds.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company