Thread: 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.
Centuries ago, Nostradamus foresaw when frank.smith@aeat.co.uk ("Frank Smith") would write:
> 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.
Well, there certainly have been _major_ improvements in performance
between 7.2 and 7.4, so it is more than plausible that that could have
a significant impact on performance.
It sounds as though the traffic on the system is causing query plans
to get out of whack.
But it is not clear what your problem actually is; you have not
explained the nature of the queries that are performing badly.
I would imagine that it would be beneficial to run VACUUM and/or
ANALYZE somewhat more often.
- Let's suppose you run VACUUM on tables getting lots of UPDATEs and
DELETEs (note, I did _not_ say INSERT; tables that only see INSERTs
essentially NEVER need to be vacuumed) once every 4 hours.
- Tables seeing frequent updates of any sort (INSERT/DELETE/UPDATE)
perhaps should get an ANALYZE every hour.
There are significant merits to jumping to a newer version in this
regard:
1. As of 7.3, you can run pg_autovacuum, which will automatically
do VACUUMs and ANALYZEs, which hopefully would prevent things
from going downhill.
2. As of 7.4, there is a new logging parameter that allows logging
just those queries that are running slowly, which should help you
to find just the offending queries.
The sort of information we'd actually need to help fix the problem is
the queries and the output provided by running EXPLAIN ANALYZE on some
of the slow queries.
There are some tables that we have in our applications that we happen
to ANALYZE _and_ VACUUM every couple of minutes, as they are UPDATEd
almost continually. Waiting an hour between VACUUMS would lead to
_really_ bad performance. You might have an instance of a table like
that, but you haven't said anything that would indicate that.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/sgml.html
Rules of the Evil Overlord #149. "Ropes supporting various fixtures
will not be tied next to open windows or staircases, and chandeliers
will be hung way at the top of the ceiling."
<http://www.eviloverlord.com/>
In article <s0b610a3.089@uk.aeat.com>, "Frank Smith" <frank.smith@aeat.co.uk> 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.
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 <postgresql@finner.de> 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 <hf517@protecting.net> sat down, thought long and then wrote: > In article <s0b610a3.089@uk.aeat.com>, > "Frank Smith" <frank.smith@aeat.co.uk> 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.