I've been through the archives a lot, trying to figure this one out.
We are running Postgresql 7.2.1 on a RedHat 7.2 system with kernel
2.4.9-12smp on a two processor system. What we have noticed is that
performance slowly gets worse over the course of a month to the point
that we have to pg_dump the databases, wipe the data dir, initdb and
restore the databases. This takes as long as five hours (depending on
whether or not we remember to turn off 'fsync' during the pg_restore).
After the restore, performance is again stellar.
Nightly, we're doing a 'vacuumdb -a -z' after stopping and restarting
the database. The performance will get so bad after a month that we
start to see load spikes in excess of 30. Normally, we don't see load
over 2.5 during the heaviest activity and generally less than 1.0 most
of the time.
We are using the database as part of a web application that is used
fairly intensively every day. We are using persistant connections with
the web server that lives on another machine. Apache's
MaxRequestsPerChild is set to 100 to prevent the things from growing to
insane sizes during the course of the day.
We build 7.2.1 from source using the config:
./configure --prefix=/usr/local/pgsql --enable-multibyte
Any insights would be most appreciated.
Thanks...
Bob
--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451