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

On Fri, Mar 25, 2011 at 4:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> On Fri, Mar 25, 2011 at 7:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I don't recall any particular discussion of making the user contend with
>>> that.  My thought would be to do something like enlarging the table by
>>> 10% anytime we need to extend it.
>
>> Just for reference this is how Oracle *used* to behave. It was widely
>> hated and led to all sorts of problems. Best practice was to pick a
>> reasonable size for your tablespace and pre-allocate that size and set
>> future increments to be that size with 0% growth.
>
> 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.

I think we've had a number of pieces of evidence that suggest that
extending 8kB at a time is too costly, but I agree with Greg that the
idea of extending an arbitrarily large table by 10% at a time is
pretty frightening - that could involve allocating a gigantic amount
of space on a big table. I would be inclined to do something like
extend by 10% of table or 1MB, whichever is smaller. If the main goal
is to avoid extending the file many times, this will reduce the number
of physical file extensions on a table > 10MB in size by more than 99%
compared to the current code, which ought to be enough to eliminate
this as an issue. And a 1MB extension is probably also small enough
that we can do it in the foreground without too much of a hiccup.

Now, there's a second effect here that's worth thinking about:
allocating in really big chunks might help the filesystem lay out the
space more contiguously on disk. But I'd want to see some careful
testing that reveals exactly what is needed to get that benefit before
we go too crazy with it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company