X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 38F73D1B1F7 for ; Wed, 2 Jun 2004 12:09:48 -0300 (ADT) 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 77420-02 for ; Wed, 2 Jun 2004 15:09:46 +0000 (GMT) Received: from fava.aeat.co.uk (fava.aeat.co.uk [151.182.140.67]) by svr1.postgresql.org (Postfix) with ESMTP id B5714D1B1DC for ; Wed, 2 Jun 2004 12:09:44 -0300 (ADT) Received: (from uucp@localhost) by fava.aeat.co.uk (8.11.6/8.11.0) id i52F9gC05014 for ; Wed, 2 Jun 2004 16:09:42 +0100 Received: from field.derby.aeat.co.uk(151.182.140.195) via SMTP by fava-inner.derby.aeat.co.uk, id smtpdbePC7V; Wed Jun 2 16:09:39 2004 Received: from aeat.co.uk (french.derby.aeat.co.uk) by field.derby.aeat.co.uk (Content Technologies SMTPRS 4.2.10) with ESMTP id for ; Wed, 2 Jun 2004 16:09:37 +0100 Received: from uk.aeat.com (mcsaeat2gwia.derby.aeat.co.uk [151.182.85.161]) by aeat.co.uk with ESMTP (8.11.0/AEAT-GW-1.42) id i52F9bM15546; Wed, 2 Jun 2004 16:09:37 +0100 sender frank.smith for Received: from AEAT2GWIA-MTA by uk.aeat.com with Novell_GroupWise; Wed, 02 Jun 2004 16:09:36 +0100 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.1 Date: Wed, 02 Jun 2004 16:09:14 +0100 From: "Frank Smith" To: , Cc: Subject: Re: Performance Problem Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/17 X-Sequence-Number: 13677 Thanks to all I will try the various options out and look to see if we can upgrade and let you know the result. It will take a few days as I am not officially here but on holiday?????? Frank >>> Frank Finner 31/05/04 15:03:02 >>> Hi, I had a similiar problem with 7.3.5 some time ago (march 23rd). The query durations increased extremely, as did the time vacuum needed. The database continuously (every 10 minutes) drops and creates tables as replication of a production database. "Vacuum analyze" did obviously not take care of that and did not release space correctly. Because my database is not so big (about 1 GB), I finally decided to dump, drop and recreate it out of the dump every night. This works fine for me. I was told on the list (Tom Lane did), that in 7.4 vacuum does handle these things in a much better way, so dump/recreate is no longer necessary. If possible (alas, not for me with my database), I recommend to upgrade to 7.4. Regards, Frank Finner. On 31 May 2004 12:41:07 +0200 Harald Fuchs sat down, thought long and then wrote: > In article , > "Frank Smith" writes: > > > Hi > > ID:77777 > > > I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a growing > > performance problem. The problem shows through a slowing of queries and > > an increase in the system CPU usage. Queries that took less than 6 > > seconds clime to take more than 5 minutes and as the system is driven by > > Apache through Perl scripts, the web server times out. Clearly I could > > reset the Apache timers, however this would just hide the problem for a > > little longer and of course once the problem starts to happen the system > > tends to cascade because the users try again and the orphaned processes > > continue to use processor time until they complete. > > > I use Cron to 'VACUUM ANALIZE' the system every night and this greatly > > improved the performance but has not stopped the delay from growing. The > > strange thing is that apart from the delay everything seems fine. > > If VACUUM does not stop the delay from growing, you might be suffering > index bloat. Either REINDEX by crontab, or upgrade to 7.4, where > VACUUM seems to take care of that. *********************************************************************** This transmission contains information which may be confidential and which may also be privileged. It is intended for the named addressee only. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please contact the sender. Thank you for your cooperation. *********************************************************************** For more information about AEA Technology please visit our website at http://www.aeat.co.uk AEA Technology plc registered office 329 Harwell, Didcot, Oxfordshire OX11 0QJ. Registered in England and Wales, number 3095862.