X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AC8F93A620E for ; Tue, 7 Dec 2004 15:08:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00935-02 for ; Tue, 7 Dec 2004 15:07:58 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 51D323A6193 for ; Tue, 7 Dec 2004 15:08:01 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iB7F7tZv015132; Tue, 7 Dec 2004 10:07:55 -0500 (EST) To: Greg Stark Cc: Bryan Vest , pgsql-performance@postgresql.org Subject: Re: Config review In-reply-to: <87r7m2zgar.fsf@stark.xeocode.com> References: <41B46F52.4000607@bright.net> <87r7m2zgar.fsf@stark.xeocode.com> Comments: In-reply-to Greg Stark message dated "07 Dec 2004 03:03:08 -0500" Date: Tue, 07 Dec 2004 10:07:54 -0500 Message-ID: <15131.1102432074@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/134 X-Sequence-Number: 9509 Greg Stark writes: > Bryan Vest writes: >> Currently the database is at a size of 87.6Gig. A Vacuum Analyze runs every >> night and has been taking 4 or 5 hours to complete. Everything seems to run >> fine for a while, then at some point the load goes through the roof and the >> iowait % also goes way up. It will recover after a little bit and then do the >> same thing all over again. > While others have pointed out problems with the config I don't think any of > them explains this irregular behaviour. As Greg says, it might be checkpoints or a background query. If it actually is the vacuum itself causing the variation in load, the theory that comes to mind is that the performance tanks when the vacuum run switches from find-dead-tuples to clean-indexes mode; clean-indexes is usually a lot more I/O intensive. ISTM it actually doesn't matter much which of these explanations is correct, because all three imply the same thing: not enough disk I/O bandwidth. The disk is near saturation already and any increase in demand drives response time over the knee of the curve. If you are using a RAID configuration it might just be that you need to adjust the configuration (IIRC, there are some RAID setups that are not very write-friendly). Otherwise you may have little alternative but to buy faster disks. regards, tom lane