Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 735559FBFFC for ; Thu, 26 Apr 2007 19:50:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21616-06-3 for ; Thu, 26 Apr 2007 19:50:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 455DC9FBFF6 for ; Thu, 26 Apr 2007 19:50:29 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 26 Apr 2007 22:50:27 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 26 Apr 2007 17:50:27 -0500 Subject: Re: postgres: 100% CPU utilization From: Scott Marlowe To: Sergey Tsukinovsky Cc: Ron , Mario Weilguni , pgsql-performance@postgresql.org In-Reply-To: <2C20E65455FC964690D89F8C4A2E9B1D03ABA7F9@STRAIGHTFLUSH.cryptologic.com> References: <2C20E65455FC964690D89F8C4A2E9B1D03A669CD@STRAIGHTFLUSH.cryptologic.com> <200704231053.38952.mweilguni@sime.com> <2C20E65455FC964690D89F8C4A2E9B1D03ABA7F9@STRAIGHTFLUSH.cryptologic.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1177627827.15085.87.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 26 Apr 2007 17:50:27 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/446 X-Sequence-Number: 24323 On Tue, 2007-04-24 at 10:30, Sergey Tsukinovsky wrote: > Thanks for this reply, Ron. > This is almost what I was looking for. > > While the upgrade to the latest version is out of the question (which > unfortunately for me became the subject of this discussion) still, I was > looking for the ways to improve the performance of the 7.0.2 version. > > Extensive use of vacuum was almost obvious, though I was hoping to get > some more tips from postrges gurus (or dinosaurs, if you want). > > Anyways, the 8.2.4 was not performing so well without auto-vacuum. It > ramped up to 50% of CPU usage in 2 hours under the load. > With the auto-vacuum ON I've got what I really need and thus I know what > to do next. Could you give us a better picture of how you were testing 8.2.4? My guess is that you were doing something that seemed right to you, but was working against yourself, like constant vacuum fulls and getting index bloat, or something else. Why were you trying to not use autovacuum, btw? I've found it to be quite capable, with only a few situations (high speed queueing) where I needed to manually schedule vacuums. And I've never seen a situation since about 7.4 where regular full vacuums were required. > Just for the record - the hardware that was used for the test has the > following parameters: > AMD Opteron 2GHZ > 2GB RAM > LSI Logic SCSI Nice hardware. I'd really like to hear the logic behind your statement that upgrading to 8.1 or 8.2 is out of the question.