Message-ID: <4CB48EF4.5030502@krogh.cc>
Date: Tue, 12 Oct 2010 18:38:12 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: Scott Carey <scott@richrelevance.com>
CC: Samuel Gendler <sgendler@ideasculptor.com>,
	Greg Smith <greg@2ndquadrant.com>, Joshua Tolley <eggyknap@gmail.com>,
	Craig Ringer <craig@postnewspapers.com.au>,
	"mladen.gogala@vmsinfo.com" <mladen.gogala@vmsinfo.com>,
	Neil Whelchel <neil.whelchel@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow count(*) again...
References: <201010091626.18636.neil.whelchel@gmail.com>
	<4CB11CC7.8070300@vmsinfo.com>	<4CB167CA.9070109@postnewspapers.com.au>
	<4CB1F46E.80205@vmsinfo.com>	<4CB2410C.6010603@postnewspapers.com.au>
	<4cb25a0e.2a978e0a.7797.33d6@mx.google.com>
	<4CB3C598.2010302@2ndquadrant.com>
	<AANLkTi=WE-CsGVC3j4CX9ddGGsRZ3Mmk6aJGKk4NT=g6@mail.gmail.com>
	<E2D86063-1FD0-48D0-BB0D-0E78F4A77FD2@richrelevance.com>
	<AANLkTimjsbV6W5JcMKs02b1wJLhhXaxHxq7=mURY50nG@mail.gmail.com>
	<0CA660FA-F59C-494E-BFA9-98CE8E527378@richrelevance.com>
In-Reply-To: <0CA660FA-F59C-494E-BFA9-98CE8E527378@richrelevance.com>
Content-Type: multipart/alternative;
	boundary="------------080709050900060204090305"

This is a multi-part message in MIME format.
--------------080709050900060204090305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2010-10-12 18:02, Scott Carey wrote:
>  However, for large reporting queries and sequential scans, XFS will
>  win in the long run if you use the online defragmenter.  Otherwise,
>  your sequential scans won't be all that sequential on any file system
>  over time if your tables aren't written once, forever, serially.
>  Parallel restore will result in a system that is fragmented -- ext4
>  will do best at limiting this on the restore, but only xfs has online
>  defragmentation.  We schedule ours daily and it noticeably improves
>  sequential scan I/O.
>
>  Supposedly, an online defragmenter is in the works for ext4 but it
>  may be years before its available.

If some clever postgres hacker could teach postgres to allocate blocks
using posix_fallocate in quite large batches, say .. something like:
fallocate(min(current_relation_size *0.1,1073741824));

So if you have a relations filling 10GB allready, they the next "file" 
for the
relations is just fully allocated on the first byte by the filesystem. That
would ensure that large table is sitting efficiently on the filesystem 
level with
a minimum of fragmentation on ext4(and other FS's supporting 
posix_fallocate)
and for small systems it would only fill 10% more of diskspace...   ..

.. last night I spend an hour looking for where its done but couldnt 
find the
source-file where extention of an existing relation takes place.. can
someone give directions?

-- 
Jesper


--------------080709050900060204090305
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 2010-10-12 18:02, Scott Carey wrote:<br>
<span style="white-space: pre;">&gt; However, for large reporting
queries and sequential scans, XFS will<br>
&gt; win in the long run if you use the online defragmenter.&nbsp; Otherwise,<br>
&gt; your sequential scans won't be all that sequential on any file
system<br>
&gt; over time if your tables aren't written once, forever, serially.<br>
&gt; Parallel restore will result in a system that is fragmented -- ext4<br>
&gt; will do best at limiting this on the restore, but only xfs has
online<br>
&gt; defragmentation.&nbsp; We schedule ours daily and it noticeably improves<br>
&gt; sequential scan I/O.<br>
&gt; <br>
&gt; Supposedly, an online defragmenter is in the works for ext4 but it<br>
&gt; may be years before its available.<br>
</span><br>
If some clever postgres hacker could teach postgres to allocate blocks<br>
using posix_fallocate in quite large batches, say .. something like:<br>
fallocate(min(current_relation_size *0.1,1073741824)); <br>
<br>
So if you have a relations filling 10GB allready, they the next "file"
for the <br>
relations is just fully allocated on the first byte by the filesystem.
That <br>
would ensure that large table is sitting efficiently on the filesystem
level with <br>
a minimum of fragmentation on ext4(and other FS's supporting
posix_fallocate)<br>
and for small systems it would only fill 10% more of diskspace...&nbsp;&nbsp; ..<br>
<br>
.. last night I spend an hour looking for where its done but couldnt
find the <br>
source-file where extention of an existing relation takes place.. can <br>
someone give directions? <br>
<br>
-- <br>
Jesper<br>
<br>
<span style="white-space: pre;"> </span><br>
<br>
<br>
</body>
</html>

--------------080709050900060204090305--