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 <gsst...@mit.edu> wrote: > From: Greg Stark <gsst...@mit.edu> > Subject: Re: [PERFORM] Block at a time ... > To: "Pierre C" <li...@peufeu.com> > Cc: "Alvaro Herrera" <alvhe...@commandprompt.com>, "Dave Crooke" > <dcro...@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 <li...@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 > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
