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 <li...@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 >
