2006/10/1, Peter Bauer <peter.m.bauer@xxxxxxxxx>:
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
