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

Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

From: Christopher Browne <cbbrowne(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Maxim Boguk <Maxim(dot)Boguk(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Date: 2011-03-25 15:58:46
Message-ID: AANLkTimiCkmZ5NL=iPUG08TRAd-OJp_M1sR2SCm6c2BL@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Mar 25, 2011 at 11:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Maxim Boguk" <Maxim(dot)Boguk(at)gmail(dot)com> writes:
>> In my case vacuum tried to truncate last 10-15GB from 100Gb relation, and
>> each time (3) it was cost 10+ minutes of service downtime (because that
>> table was completely locked).
>
>> Is  that correct behaviour? Are here any way to speedup that process or at
>> least allow read-only queries during that time?
>
> Use autovacuum --- if there's something that wants to access the table,
> autovac will get kicked off the lock.  (Of course, the table may never
> get truncated then, but maybe you don't care.)

I could see this turning out somewhat unhappily...

If there's almost always something that wants to access the table,
which would be hardly surprising, in view that the table is being
updated with sufficient regularity that it's got 10GB+ of dead space
at the end, then you're eventually going to *HAVE* to vacuum it.
Transaction rollover, and what not.

The piper will need to get paid, eventually :-(.

So I don't think leaving it to autovacuum quite suffices, because
eventually there's a 10 minute "glut", or more, that needs to get
paid.
-- 
http://linuxfinances.info/info/linuxdistributions.html

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-03-25 16:10:21
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Previous:From: Alex LaiDate: 2011-03-25 15:34:20
Subject: Re: postgres 9 streaming replication

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