From: Dave Crooke <dcrooke@gmail.com>
To: Pierre C <lists@peufeu.com>
Cc: Scott Carey <scott@richrelevance.com>
Cc: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Block at a time ...
Date: Mon, 22 Mar 2010 16:06:00 -0500
Message-ID: <ca24673e1003221406x7195fe2fka3be8a12df36330c@mail.gmail.com> (raw)
In-Reply-To: <op.u9zlqp0reorkce@localhost>
References: <ca24673e1003161658g6a0fa30dg2c158db395ac08@mail.gmail.com>
<20100317001405.GL3037@alvh.no-ip.org>
<op.u9pa7vzleorkce@localhost>
<407d949e1003170252y72d223c3kcc18266140a60997@mail.gmail.com>
<4BA10648.6010609@emolecules.com>
<D254FE0F-9B3A-432D-AA38-C8B526A7ECEB@richrelevance.com>
<op.u9zlqp0reorkce@localhost>
This is why pre-allocation is a good idea if you have the space ....
Tom, what about a really simple command in a forthcoming release of PG that
would just preformat a 1GB file at a time? This is what I've always done
scripted with Oracle (ALTER TABLESPACE foo ADD DATAFILE ....) rather than
relying on its autoextender when performance has been a concern.
Cheers
Dave
On Mon, Mar 22, 2010 at 3:55 PM, Pierre C <lists@peufeu.com> wrote:
>
> This is one of the more-or-less solved problems in Unix/Linux. Ext* file
>>> systems have a "reserve" usually of 10% of the disk space that nobody except
>>> root can use. It's not for root, it's because with 10% of the disk free,
>>> you can almost always do a decent job of allocating contiguous blocks and
>>> get good performance. Unless Postgres has some weird problem that Linux has
>>> never seen before (and that wouldn't be unprecedented...), there's probably
>>> no need to fool with file-allocation strategies.
>>>
>>> Craig
>>>
>>
>> Its fairly easy to break. Just do a parallel import with say, 16
>> concurrent tables being written to at once. Result? Fragmented tables.
>>
>
> Delayed allocation (ext4, XFS) helps a lot for concurrent writing at a
> medium-high rate (a few megabytes per second and up) when lots of data can
> sit in the cache and be flushed/allocated as big contiguous chunks. I'm
> pretty sure ext4/XFS would pass your parallel import test.
>
> However if you have files like tables (and indexes) or logs that grow
> slowly over time (something like a few megabytes per hour or less), after a
> few days/weeks/months, horrible fragmentation is an almost guaranteed result
> on many filesystems (NTFS being perhaps the absolute worst).
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dcrooke@gmail.com, lists@peufeu.com, scott@richrelevance.com, craig_james@emolecules.com
Subject: Re: Block at a time ...
In-Reply-To: <ca24673e1003221406x7195fe2fka3be8a12df36330c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox