Re: Block at a time ... - Mailing list pgsql-performance

Greg is correct, as usual.  Geometric growth of files is A Bad Thing in an  Oracle DBA's world, since you can
unexpectedly(automatically?) run out of file system space when the database determines it needs x% more extents than
lasttime. 

The concept of contiguous extents, however, has some merit, particularly when restoring databases.  Prior to parallel
restore,a table's files were created and extended in roughly contiguous allocations, presuming there was no other
activityon your database disks.  (You do dedicate disks, don't you?)  When using 8-way parallel restore against a
six-diskRAID 10 group I found that table and index scan performance dropped by about 10x.  I/O performance was restored
byeither clustering the tables one at a time, or by dropping and restoring them one at a time.  The only reason I can
comeup with for this behavior is file fragmentation and increased seek times. 

If PostgreSQL had a mechanism to pre-allocate files prior to restoring the database that might mitigate the problem.

Then if we could only get parallel index operations ...

Bob Lunney

--- On Wed, 3/17/10, Greg Stark <gsstark@mit.edu> wrote:

> From: Greg Stark <gsstark@mit.edu>
> Subject: Re: [PERFORM] Block at a time ...
> To: "Pierre C" <lists@peufeu.com>
> Cc: "Alvaro Herrera" <alvherre@commandprompt.com>, "Dave Crooke" <dcrooke@gmail.com>,
pgsql-performance@postgresql.org
> Date: Wednesday, March 17, 2010, 5:52 AM
> On Wed, Mar 17, 2010 at 7:32 AM,
> Pierre C <lists@peufeu.com>
> wrote:
> >> I was thinking in something like that, except that
> the factor I'd use
> >> would be something like 50% or 100% of current
> size, capped at (say) 1 GB.
>
> This turns out to be a bad idea. One of the first thing
> Oracle DBAs
> are told to do is change this default setting to allocate
> some
> reasonably large fixed size rather than scaling upwards.
>
> This might be mostly due to Oracle's extent-based space
> management but
> I'm not so sure. Recall that the filesystem is probably
> doing some
> rounding itself. If you allocate 120kB it's probably
> allocating 128kB
> itself anyways. Having two layers rounding up will result
> in odd
> behaviour.
>
> In any case I was planning on doing this a while back. Then
> I ran some
> experiments and couldn't actually demonstrate any problem.
> ext2 seems
> to do a perfectly reasonable job of avoiding this problem.
> All the
> files were mostly large contiguous blocks after running
> some tests --
> IIRC running pgbench.
>
>
> > Using fallocate() ?
>
> I think we need posix_fallocate().
>
> --
> greg
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>