Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id BE8D91337B55 for ; Tue, 12 Oct 2010 13:43:02 -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 06660-02 for ; Tue, 12 Oct 2010 16:42:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id 48B721337B6C for ; Tue, 12 Oct 2010 13:42:54 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Tue, 12 Oct 2010 09:42:54 -0700 From: Scott Carey To: Dan Harris CC: pgsql-performance Date: Tue, 12 Oct 2010 09:44:02 -0700 Subject: Re: Slow count(*) again... Thread-Topic: [PERFORM] Slow count(*) again... Thread-Index: ActqLIQjvUBJNqSLRwG88kHtTwLGUA== Message-ID: <8BE535FE-F9DB-4CBC-A932-EB4F07905698@richrelevance.com> 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> In-Reply-To: <4CB48127.6090200@drivefaster.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 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/165 X-Sequence-Number: 40584 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 de= fragmentation. I have found that a longer lived postgres DB will get extre= me >> file fragmentation over time and sequential scans end up mostly random. = On-line file defrag helps tremendously. >>=20 > We just had a corrupt table caused by an XFS online defrag. I'm scared=20 > to use this again while the db is live. Has anyone else found this to=20 > be safe? But, I can vouch for the fragmentation issue, it happens very=20 > quickly in our system. >=20 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...=20 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 th= e other one. This should be safe provided the journaling and storage is sa= fe, etc. > -Dan >=20 > --=20 > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance