Re: autovacuum truncate exclusive lock round two

On 12/6/2012 12:45 PM, Robert Haas wrote:
> On Wed, Dec 5, 2012 at 10:16 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
>> That sort of "dynamic" approach would indeed be interesting. But I fear that
>> it is going to be complex at best. The amount of time spent in scanning
>> heavily depends on the visibility map. The initial vacuum scan of a table
>> can take hours or more, but it does update the visibility map even if the
>> vacuum itself is aborted later. The next vacuum may scan that table in
>> almost no time at all, because it skips all blocks that are marked "all
>> visible".
>
> Well, if that's true, then there's little reason to worry about giving
> up quickly, because the next autovacuum a minute later won't consume
> many resources.

"Almost no time" is of course "relative" to what an actual scan and dead 
tuple removal cost. Looking at a table with 3 GB of dead tuples at the 
end, the initial vacuum scan takes hours. When vacuum comes back to this 
table, cleaning up a couple megabytes of newly deceased tuples and then 
skipping over the all visible pages may take a minute.

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 */

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.


Jan

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