Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id ABC041337BC1 for ; Tue, 12 Oct 2010 14:06:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 56938-03 for ; Tue, 12 Oct 2010 17:06:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by mail.postgresql.org (Postfix) with ESMTP id 595431337BBF for ; Tue, 12 Oct 2010 14:06:51 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id 840984E1AB for ; Tue, 12 Oct 2010 11:06:50 -0600 (MDT) X-Virus-Scanned: Debian amavisd-new at coronasolutions.com Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id izxMSoPin2s0 for ; Tue, 12 Oct 2010 11:06:49 -0600 (MDT) Received: from mac.local (unknown [10.1.250.14]) by crestone.coronasolutions.com (Postfix) with ESMTPA id BCBB24E1A0 for ; Tue, 12 Oct 2010 11:06:48 -0600 (MDT) Message-ID: <4CB495A7.6010700@drivefaster.net> Date: Tue, 12 Oct 2010 11:06:47 -0600 From: Dan Harris User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4 MIME-Version: 1.0 To: pgsql-performance 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> <4CB48127.6090200@drivefaster.net> <8BE535FE-F9DB-4CBC-A932-EB4F07905698@richrelevance.com> In-Reply-To: <8BE535FE-F9DB-4CBC-A932-EB4F07905698@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 X-Spam-Level: X-Archive-Number: 201010/170 X-Sequence-Number: 40589 On 10/12/10 10:44 AM, Scott Carey wrote: > On Oct 12, 2010, at 8:39 AM, Dan Harris wrote: > >> On 10/11/10 8:02 PM, Scott Carey wrote: >>> 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. >>> >> We just had a corrupt table caused by an XFS online defrag. I'm scared >> to use this again while the db is live. Has anyone else found this to >> be safe? But, I can vouch for the fragmentation issue, it happens very >> quickly in our system. >> > What version? I'm using the latest CentoOS extras build. > > We've been doing online defrag for a while now on a very busy database with> 8TB of data. Not that that means there are no bugs... > > It is a relatively simple thing in xfs -- it writes a new file to temp in a way that allocates contiguous space if available, then if the file has not been modified since it was re-written it is essentially moved on top of the other one. This should be safe provided the journaling and storage is safe, etc. > I'm not sure how to figure out what version of XFS we're on.. but it's Linux kernel 2.6.24-24 x86_64 on Ubuntu Server 8.04.3. Postgres version 8.3 We're due for an upgrade on that server soon so we'll do some more testing once we upgrade. Right now we are just living with the fragmentation. I'm glad to hear the regular on-line defrag is working successfully, at least that gives me hope we can rely on it in the future. -Dan