DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=4BPRGvNg2VdBUFT1UZ/11eea8jueCDLfrjSNmkdF9B/+nnBjSUX62Tqe933wc1NMYmQeLMvs4+paZzhPmlaAxzBYgloYUJd+QsiN7igsekqGdFvnDBEfw6pCVy+FxqK/grYTd9LnICC+QNpucvZKPP5dOR1qVjfdzvTLyKONO0o=;
Message-ID: <924336.38365.qm@web39703.mail.mud.yahoo.com>
Date: Wed, 17 Mar 2010 10:02:03 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Block at a time ...
To: pgsql-performance@postgresql.org
In-Reply-To: <407d949e1003170252y72d223c3kcc18266140a60997@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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 last time.

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 activity on your database disks.  (You do dedicate disks, don't you?)  When using 8-way parallel restore against a six-disk RAID 10 group I found that table and index scan performance dropped by about 10x.  I/O performance was restored by either clustering the tables one at a time, or by dropping and restoring them one at a time.  The only reason I can come up 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
>