Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8DA17632F04 for ; Mon, 22 Mar 2010 18:00:35 -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 44842-10 for ; Mon, 22 Mar 2010 21:00:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by mail.postgresql.org (Postfix) with SMTP id BC8BE6325A1 for ; Mon, 22 Mar 2010 18:00:24 -0300 (ADT) Received: (qmail 16724 invoked by uid 503); 22 Mar 2010 21:08:50 -0000 Received: from b9.ovh.net (HELO mail419.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 22 Mar 2010 21:08:50 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 22 Mar 2010 21:00:23 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO localhost) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 22 Mar 2010 21:00:21 -0000 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "Scott Carey" , "Craig James" Cc: "pgsql-performance@postgresql.org" Subject: Re: Block at a time ... References: <20100317001405.GL3037@alvh.no-ip.org> <407d949e1003170252y72d223c3kcc18266140a60997@mail.gmail.com> <4BA10648.6010609@emolecules.com> Date: Mon, 22 Mar 2010 21:55:27 +0100 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: User-Agent: Opera Mail/10.10 (Linux) X-Ovh-Tracer-Id: 15736984474245531286 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/282 X-Sequence-Number: 38107 >> 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).