Skip site navigation (1) Skip section navigation (2)

Performance Problem

From: "Frank Smith" <frank(dot)smith(at)aeat(dot)co(dot)uk>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Performance Problem
Date: 2004-05-27 15:00:04
Message-ID: s0b610a3.089@uk.aeat.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

Some facts:

Rebooting does not remove the problem.
Restarting Apache and/or Postgres makes no difference.
Other that the ANALIZE mentioned above the system has not been tuned in
anyway.
The following sequence does however cure the problem;

pg_dump database > temp.db
dropdb database
createdb database
psql -e database < temp.db

I have not tried leaving out the dropdb and createdb but I do not need
to stop postgres.

Has anybody any idea how to fix my problem is it something I have or
have not done? Please do not tell me to upgrade to the latest version of
Postgres unless it is a clearly identifiable Postgres problem that is at
the rot of my dilemma.

Thanks

Frank

I get balder by the handful, daily.



***********************************************************************
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.


Responses

pgsql-admin by date

Next:From: Bruce MomjianDate: 2004-05-27 15:10:06
Subject: Re: What is safe to exclude when doing a UFS backup?
Previous:From: Marty ScholesDate: 2004-05-27 14:42:04
Subject: What is safe to exclude when doing a UFS backup?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group