Re: Major Performance decrease after some hours

2006/10/1, Peter Bauer <peter.m.bauer@gmail.com>:
> Hi all,
>
> i have a Tomcat application with PostgreSQL 8.1.4 running which
> performs about 10000 inserts/deletes every 2-4 minutes and updates on
> a database and after some hours of loadtesting the top output says
> 0.0% idle, 6-7% system load, load average 32, 31, 28 and there are
> many exceptions at statement execution like:
> An I/O error occured while sending to the backend.
>
> There are 10-15 postmaster processes running which use all the CPU power.
> A restart of tomcat and then postgresql results in the same situation.
> Some postgres processes are in DELETE waiting or SELECT waiting.
> VACUUM runs through in just about 1-2 seconds and is run via cron
> every minute and reports that the fsm setting are high enough.
>
> The situation just gets better if tomcat is stopped and postgresql is
> restarted -> 90%idle
>
> The tomcat application talks to another application on the server
> which uses much CPU cycles at startup of tomcat and my observation is
> that after this startup with 0.0% idle, PostgreSQL can't recover from
> this situation.
>
> Dual Xeon Server
> machines (Dell PowerEdge 2850) using Heartbeat1. Each server has only
> one harddisk, no RAID configuration is used.
> - Each Cluster has 4 drbd Devices, one for the PostgreSQL data
> - Two of these clusters are using the same PostgreSQL installation to
> share the data, the database can be moved from one cluster to the
> other in case of failure
> - OS: Debian Sarge with postgresql 8.1.4
> - Two cronjobs are configured to perform a "vacuumdb --all"
> every 1 minute and a "vacuumdb --all--analyze" every 23
> minutes
> - There are 3 applications using the PostgreSQL installation, each
> with their own database.
> - The main application is based on Tomcat 4.1-30 partly a
> Web-Application, partly a terminal login protocol based on http and
> XML, so the database access is of course done using JDBC
> - A cronjob is configured to perform a pg_dump of the main database
> every 4 hours
>
>
> Any ideas are welcome.
>
> thx,
> Peter
>

Hi again,

i decreased the load of the test and the system is stable for some
hours now, no Exceptions so far, top says 80-90% idle.
The question is what are the reasons for this behaviour? Is a hardware
upgrade required? How can i check how bad the condition of the
database is?

thx,
Peter