Re: postgres eating CPU on HP9000

On Tue, 23 Mar 2004, Fabio Esposito wrote:

>
> 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

This almost sounds like a problem (fixed in 7.4 I believe) where some
system catalog indexes would get huge over time, and couldn't be vacuumed
or reindexed while the database was up in multi-user mode.

I'll defer to Tom or Bruce or somebody to say if my guess is even close...