Overload after some minutes, please help!

Hi all,

we are struggling for some time now with PostgreSQL 8.1.4 and the
situation is pretty critical so please help with whatever comes to
your mind.
We even did an upgrade from version 7.4.13, tried different vacuum
configurations and optimized the configuration.

There is a table called tableregistrations where per day about
1 million rows are INSERTed
20000 SELECTs should be performed on it
10000 UPDATEs should be performed where about 100 rows are updated
with each execution
10000 DELETEs should be performed every 10 seconds
in such a way that the table constantly contains about 20000 entries.

I appended examples for these statements and the table structure.

A vaccum of the whole database is performed every 10 minutes with
cron, autovacuum is enabled too. We also tried to vaccum every minute
which improved the performance somewhat but it just defers the freeze
situation:

After starting the system such a DELETE statement takes 300-500ms but
after about 15 minutes the DELETE statements take about 45 seconds to
complete and top shows that the postgres process which performs the
DELETE takes almost 100% of one CPU. Some minutes later the database
is unusable (all tables of the database are affected) and if the
application is stopped, the load is still as high as before. The
SELECT statements then also hang.

All in all there are about 3 million other statements executed per day
on other tables which are rather simple and don't hang in the database
as the statements for the tableregistration do.

System Specification:
Dual Xeon Server
machines (Dell PowerEdge 2850) using Heartbeat1. Each server has only
one SCSI 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
- OS: Debian Sarge with postgresql 8.1.4

Other tasks are also performed on the system like logrotate of some GB
sized files which need one CPU for some time.

Is it possible that this is just too much load for the database on
such a hardware setup?

Where could i see what is constantly getting worse over time so that
the DELETE statements take so long after some minutes?


thx,
Peter