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(at)yahoo(dot)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