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

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