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... ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])