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

Re: Lock problem with autovacuum truncating heap

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Jan Wieck <JanWieck(at)yahoo(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <gsstark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Lock problem with autovacuum truncating heap
Date: 2011-04-01 19:42:06
Message-ID: 4D962A8E.4080307@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 3/28/2011 12:35 PM, Jan Wieck wrote:
> On 3/27/2011 10:43 PM, Tom Lane wrote:
>
>>  In particular, I thought the direction Jan was headed was to release and
>>  reacquire the lock between truncating off limited-size chunks of the
>>  file.  If we do that, we probably *don't* want or need to allow autovac
>>  to be booted off the lock more quickly.
>
> That is correct.
>
>>>   3) Scanning backwards 8MB at a time scanning each 8MB forwards instead
>>>   of just going back by block backwards.
>>
>>  Maybe.  I'd want to see some experimental evidence justifying the choice
>>  of chunk size; I'm pretty sure this will become counterproductive once
>>  the chunk size is too large.
>
> Me too, which is why that part of my proposal is highly questionable and
> requires a lot of evidence to be even remotely considered for back releases.

Attached is a patch against HEAD that implements the part that truncates 
the heap in small batches (512 pages at a time) without fiddling with 
the scan direction.

It does several retries when attempting to get the exclusive lock. This 
is because when doing it this way I discovered that locks queued up 
behind the exclusive lock held by autovacuum make it too likely that it 
fails after just a few batches.

I am going to see what a similar logic will do to 8.4, where the 
exclusive lock has far more severe consequences to client connections.


Jan

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

Attachment: master-autovac-lock.diff
Description: text/x-patch (6.9 KB)

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2011-04-01 19:50:29
Subject: Re: Bug in autovacuum.c?
Previous:From: Kevin GrittnerDate: 2011-04-01 19:23:00
Subject: Re: trivial patch: show SIREAD pids in pg_locks

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