Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C69D61337B5A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 11 Oct 2010 23:23:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 31982-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 12 Oct 2010 02:23:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hrndva-omtalb.mail.rr.com (hrndva-omtalb.mail.rr.com
	[71.74.56.122])
	by mail.postgresql.org (Postfix) with ESMTP id 0C1A01337B56
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Oct 2010 23:23:45 -0300 (ADT)
X-Authority-Analysis: v=1.1 cv=QISHQ+x4xS+apxgDHgAvNvlUjyrXCzYsHvuXcbPYiKg=
	c=1 sm=0 a=_6Iw8BWMfOIA:10 a=8nJEP1OIZ-IA:10
	a=RAii6je2cdnAH6ATCOpc6g==:17 a=_Ixf_7qHAAAA:8
	a=T5xtw5fnGpJ-vVFjAVkA:9 a=Ps6Y4F3R3JAYiGivjiwiKogFvNYA:4
	a=wPNLvfGTeEIA:10 a=ryAo9hy1-OkA:10 a=6vsjEBoLwlAA:10
	a=0tfO7MgyNwYA:10 a=RAii6je2cdnAH6ATCOpc6g==:117
X-Cloudmark-Score: 0
X-Originating-IP: 74.73.87.196
Received: from [74.73.87.196] ([74.73.87.196:49543] helo=[192.168.1.103])
	by hrndva-oedge04.mail.rr.com (envelope-from
	<mladen.gogala@vmsinfo.com>) (ecelerity 2.2.3.46 r()) with ESMTP
	id 91/E0-11377-2B6C3BC4; Tue, 12 Oct 2010 02:23:46 +0000
Message-ID: <4CB3C6B2.8050501@vmsinfo.com>
Date: Mon, 11 Oct 2010 22:23:46 -0400
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
	rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4
MIME-Version: 1.0
To: Scott Carey <scott@richrelevance.com>
CC: "pgsql-performance@postgresql.org >> \"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>
	<90E30938-1E5E-4DCA-A1FB-AEECEB76A70D@richrelevance.com>
In-Reply-To: <90E30938-1E5E-4DCA-A1FB-AEECEB76A70D@richrelevance.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201010/123
X-Sequence-Number: 40542

  On 10/11/2010 10:02 PM, Scott Carey wrote:
> Did you tune the linux FS read-ahead first?  You can get large gains by doing that if you are on ext3.
> blockdev --setra 2048<device>

Actually, I have blockdev --setra 32768

> would give you a 1MB read-ahead.  Also, consider XFS and its built-in defragmentation.  I have found that a longer lived postgres DB will get extreme
> file fragmentation over time and sequential scans end up mostly random.  On-line file defrag helps tremendously.

I agree, but I am afraid that after the demise of SGI, XFS isn't being 
developed. The company adopted the policy of using only the plain 
vanilla Ext3, which is unfortunate, but I can't do much about it. There 
is a lesson to be learned from the story of ReiserFS. I am aware of the 
fact that Ext3 is rather basic, block oriented file system which doesn't 
perform well when compared to HPFS, VxFS or JFS2 and has no notion of 
extents, but I believe that I am stuck with it, until the advent of 
Ext4. BTW, there is no defragmenter for Ext4, not even on Ubuntu, which 
is rather bleeding edge distribution.

-- 
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com