Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3811B6325A1 for ; Mon, 22 Mar 2010 18:06:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46141-08 for ; Mon, 22 Mar 2010 21:06:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by mail.postgresql.org (Postfix) with ESMTP id 6B65E6321C4 for ; Mon, 22 Mar 2010 18:06:02 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 19so655780fgg.1 for ; Mon, 22 Mar 2010 14:06:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=VTKCgs807bJZb2c15VvpmU68qWLp1b3uyu4kn/gPPY8=; b=bujv4o8FUfkgb1ASOcm6qv9t9wDKmxre9+dYwFdQd3T37woveXEGQszMVuUAl4p+RG u4QgUWcOOHg6Ytl2jUyVKZKtAWtKowmyyZ0L1J1oUupyT0VcUpjZIOVMZynqIpcjxc46 BzutvWStrzcXXMyVPgwXoCnqwWeB77LnQVY/Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=rQVqhvdW276/Xby3evmCwPB258ZKI0jlxlmUAtphfvbS57j8kjZJcXgVnHxjhifvOP f/LJIJurnl9fVKfgbMh/G1wrEXZJmM5ctE+2/7tgMTT5eGBsYa/GyUoPyCEYcm7ljBC4 bqZB5/8E/4dyHmYYn3iuxr/1bRNX3gcYailV0= MIME-Version: 1.0 Received: by 10.204.134.211 with SMTP id k19mr4605250bkt.48.1269291960245; Mon, 22 Mar 2010 14:06:00 -0700 (PDT) In-Reply-To: References: <20100317001405.GL3037@alvh.no-ip.org> <407d949e1003170252y72d223c3kcc18266140a60997@mail.gmail.com> <4BA10648.6010609@emolecules.com> Date: Mon, 22 Mar 2010 16:06:00 -0500 Message-ID: Subject: Re: Block at a time ... From: Dave Crooke To: Pierre C Cc: Scott Carey , Craig James , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=0015174c3e84681f2304826a10cd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/283 X-Sequence-Number: 38108 --0015174c3e84681f2304826a10cd Content-Type: text/plain; charset=ISO-8859-1 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 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 > --0015174c3e84681f2304826a10cd Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 th= at 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 t= han relying on its autoextender when performance has been a concern.

Cheers
Dave

On Mon, Mar 22, 2010 a= t 3:55 PM, Pierre C <lists@peufeu.com> wrote:

This is one of the more-or-less solved problems in Unix/Linux. =A0Ext* file= systems have a "reserve" usually of 10% of the disk space that n= obody except root can use. =A0It's not for root, it's because with = 10% of the disk free, you can almost always do a decent job of allocating c= ontiguous blocks and get good performance. =A0Unless Postgres has some weir= d problem that Linux has never seen before (and that wouldn't be unprec= edented...), there's probably no need to fool with file-allocation stra= tegies.

Craig

Its fairly easy to break. =A0Just do a parallel import with say, 16 concurr= ent tables being written to at once. =A0Result? =A0Fragmented tables.

Delayed allocation (ext4, XFS) helps a lot for concurrent writing at a medi= um-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 pre= tty sure ext4/XFS would pass your parallel import test.

However if you have files like tables (and indexes) or logs that grow slowl= y 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

--0015174c3e84681f2304826a10cd--