postgres eating CPU on HP9000 - Mailing list pgsql-performance

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