postgres eating CPU on HP9000

Hello fellow PostgreSQL users.

We've been working on this interesting issue for some time now, and we're
hoping that someone can help.

We've recently integrated postgres into an existing mature app. Its a
time sensitive 24x7 system. It runs on HP9000, a K370 Dual Processor
system. Postgres is version 7.3.2. Its spawned as a child from a parent
supervisory process, and they communicate to eachother via shared memory.

We preform 9-12K selects per hour
6-8K inserts per hour (a few updates here as well)
1-1.5K Deletes per hour.

It maintains 48hours of data, so its not a large database; roughly
<600mbs. We do this by running a housekeeping program in a cron job.
It deletes all data older then 48hours, then vaccuum analyzes. It will
also preform a reindex if the option is set before it vaccuum's.

Postgres initially worked wonderfully, fast and solid. It
preformed complex joins in 0.01secs, and was able to keep up with our
message queue. It stayed this way for almost a year during our
development.

Recently it started eating up the cpu, and cannot keepup with the system
like it used to. The interesting thing here is that it still runs great
on an older system with less ram, one slower cpu, and an older disk.

We tried the following with no success:

running VACCUUM FULL
dropping all tables and staring anew
reinstalling postgres
tweaking kernel parameters (various combos)
tweaking postgres parameters (various combos)
a number of other ideas

A final note, we have our app on two systems ready for hot backup. The
hot backup system is that older slower system that I mentioned before. The
two communicate with eachother via rpc's.

Any help anyone can give to steer us in the right direction would be much
appreciated.

Thanks again

Fabio E.

Just in case:

vmstat
procs memory page
faults cpu
r b w avm free re at pi po fr de sr
in sy cs us sy id
1 0 0 7631 124955 30 31 1 0 0 0 1
566 964 138 25 2 73

top

System: prokyon Tue Mar 23 19:12:54
2004
Load averages: 0.36, 0.33, 0.31
170 processes: 169 sleeping, 1 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.07 8.9% 0.0% 0.0% 91.1% 0.0% 0.0% 0.0% 0.0%
1 0.72 71.3% 0.0% 1.0% 27.7% 0.0% 0.0% 0.0% 0.0%
2 0.29 29.7% 1.0% 5.0% 64.4% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.36 36.3% 1.0% 2.0% 60.8% 0.0% 0.0% 0.0% 0.0%

Memory: 33180K (22268K) real, 38868K (28840K) virtual, 499708K free Page#
1/17

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU
COMMAND
0 pty/ttyp1 18631 am 154 20 6096K 2412K sleep 3:17 93.84 93.68
postg
0 rroot 18622 am 154 20 1888K 1192K sleep 0:01 0.78 0.78
amcodecon

ipcs

IPC status from /dev/kmem as of Tue Mar 23 19:19:19 2004
T ID KEY MODE OWNER GROUP
Message Queues:
q 0 0x3c180239 -Rrw--w--w- root root
q 1 0x3e180239 --rw-r--r-- root root
Shared Memory:
m 0 0x2f100002 --rw------- root sys
m 1 0x4118020d --rw-rw-rw- root root
m 2 0x4e0c0002 --rw-rw-rw- root root
m 3 0x4114006c --rw-rw-rw- root root
m 4 0x4118387e --rw-rw-rw- am am
m 3805 0x0052e2c1 --rw------- postgres postgres
m 8606 0x0c6629c9 --rw-r----- root sys
m 407 0x06347849 --rw-rw-rw- root sys
Semaphores:
s 0 0x2f100002 --ra-ra-ra- root sys
s 1 0x4118020d --ra-ra-ra- root root
s 2 0x4e0c0002 --ra-ra-ra- root root
s 3 0x4114006c --ra-ra-ra- root root
s 4 0x00446f6e --ra-r--r-- root root
s 5 0x00446f6d --ra-r--r-- root root
s 6 0x01090522 --ra-r--r-- root root
s 7 0x61142e7c --ra-ra-ra- root root
s 8 0x73142e7c --ra-ra-ra- root root
s 9 0x70142e7c --ra-ra-ra- root root
s 10 0x69142e7c --ra-ra-ra- root root
s 11 0x75142e7c --ra-ra-ra- root root
s 12 0x63142e7c --ra-ra-ra- root root
s 13 0x64142e7c --ra-ra-ra- root root
s 14 0x66142e7c --ra-ra-ra- root root
s 15 0x6c142e7c --ra-ra-ra- root root
s 1168 0x0052e2c1 --ra------- postgres postgres
s 401 0x0052e2c2 --ra------- postgres postgres
s 402 0x0052e2c3 --ra------- postgres postgres