Performance Problem

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.