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: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Date: 2011-03-25 21:46:21
Message-ID: AANLkTikha-8Mwa1iLhWV5XTRipqFr1xZFrSUhDuoBZ89@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Mar 25, 2011 at 5:09 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Fri, Mar 25, 2011 at 8:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Interesting, but I don't understand/believe your argument as to why this
>> is a bad idea or fixed-size extents are better.  It sounds to me just
>> like the typical Oracle DBA compulsion to have a knob to twiddle.  A
>> self-adjusting enlargement behavior seems smarter all round.
>>
>
> So is it ok for inserting one row to cause my table to grow by 90GB?
> Or should there be some maximum size increment at which it stops
> growing? What should that maximum be? What if I'm on a big raid system
> where that size doesn't even add a block to every stripe element?
>
> Say you start with 64k (8 pg blocks). That means your growth
> increments will be 64k, 70k, 77kl, 85k, 94k, 103k, 113k, 125k, 137k,
> ...
>
> I'm having trouble imagining a set of hardware and filesystem where
> growing a table by 125k will be optimal. The next allocation will have
> to do some or all of a) go back and edit the previous one to round it
> up, then b) add 128k more, then c) still have 6k more to allocate in a
> new allocation.

This is certainly a converse of the problem actually being pointed at
by the bug.

The bug indicates a situation where the table already has an enormous
pile of free space, all of it already sitting at the very end.
There's *at least* 1GB of space free, and in the case spoken of, there
was 10GB free.

The point of the exercise isn't to allocate new space - it is to
*deallocate* the huge quantity of dead space at the end of the table,
without blocking anybody unnecessarily.  I foresee that being somewhat
troublesome, mostly in view that stuff is still going on concurrently,
though it seems pretty plausible that one might *somewhat* safely
"fast-track" removal of all but the first of those empty extensions.

What seems natural-ish to me might include:
- Stomping a bit on the FSM replacement to make sure nobody's going to
be writing to the later extensions;
- Watching free space during the process so the "first" extension gets
re-opened up if the free space in the much earlier parts of the table
(e.g. - that are not planned to be dropped off) is running out.
-- 
http://linuxfinances.info/info/linuxdistributions.html

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-03-25 21:56:22
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Previous:From: Maxim BogukDate: 2011-03-25 21:43:03
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

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