Skip site navigation (1) Skip section navigation (2)

Lock problem with autovacuum truncating heap

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Lock problem with autovacuum truncating heap
Date: 2011-03-26 14:30:38
Message-ID: 4D8DF88E.7080205@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
We have run across a problem with autovacuum that occurs when it can 
truncate off a large amount of empty blocks. It behaves different in 
version 9.0 than previous versions. Both behaviors are bad.

Consider a relation receives constant inserts/updates that are satisfied 
using freespace at the beginning of the heap. Delete operations now have 
removed large amounts of tuples at the end of the relation. The 
following autovacuum will find a large amount of blocks at the end, that 
can be truncated.

Vacuumlazy now takes out an access exclusive lock and scans the relation 
*backwards* to find out if concurrent access has created new tuples in 
the to be truncated space. Apparently such a backward scan in 8K blocks 
isn't really a good access strategy.

Up to 8.4, it simply holds the lock until it is done, which in our case 
stalled a production system for 12 minutes! This is obviously bad.

In 9.0, the autovacuum process will be aborted about 1 second after 
another transaction starts waiting for a lock. The result is that even a 
simple INSERT will take 1 second. The autovacuum restarts shortly after 
and somehow gets to a point, where it will cause this 1 second hiccup 
ever 2 minutes. This is slightly better but still far from optimal in a 
world, where transaction response times are measured in milliseconds.

My current idea for a fix is to modify lazy_truncate_heap(). It does 
acquire and release the exclusive lock, so it should be possible to do 
this in smaller chunks, releasing and reacquiring the lock so that 
client transactions can get their work done as well. At the same time I 
would change count_nondeletable_pages() so that it uses a forward scan 
direction (if that leads to a speedup).


Jan

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

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2011-03-26 14:31:14
Subject: Re: 9.1 Beta
Previous:From: Robert HaasDate: 2011-03-26 13:41:46
Subject: Re: WIP: Allow SQL-language functions to reference parameters by parameter name

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group