Re: autovacuum truncate exclusive lock round two

On 12/9/2012 2:37 PM, Kevin Grittner wrote:
> Jan Wieck wrote:
>
>> Based on the discussion and what I feel is a consensus I have
>> created an updated patch that has no GUC at all. The hard coded
>> parameters in include/postmaster/autovacuum.h are
>>
>>  AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */
>>  AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
>>  AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */
>
> Since these really aren't part of the external API and are only
> referenced in vacuumlazy.c, it seems more appropriate to define
> them there.
>
>> I gave that the worst workload I can think of. A pgbench (style)
>> application that throws about 10 transactions per second at it,
>> so that there is constantly the need to give up the lock due to
>> conflicting lock requests and then reacquiring it again. A
>> "cleanup" process is periodically moving old tuples from the
>> history table to an archive table, making history a rolling
>> window table. And a third job that 2-3 times per minute produces
>> a 10 second lasting transaction, forcing autovacuum to give up on
>> the lock reacquisition.
>>
>> Even with that workload autovacuum slow but steady is chopping
>> away at the table.
>
> Applies with minor offsets, builds without warning, and passes
> `make check-world`. My tests based on your earlier posted test
> script confirm the benefit.
>
> There are some minor white-space issues; for example git diff
> --color shows some trailing spaces in comments.

Cleaned up all of those.


Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin