I'm having a problem with postgresql slowing down a lot and giving a
huge load averages (2-7 at any given time and taking up to 40 seconds to
return simple join queries). I am running three databases of a
dedicated machine they can have up to 10 clients connecting to the
computer at a time. I've done optimisations for both linux and
postgresql which I could find to try and get some more speed out of them
but its not helping enough. Things like VACUUM ANALYZE,
increasing memory available to postgresql, checking all the queries on
the database and adding more indexing etc.
If I dump the databases and recreate them from the dumps the load
average on the server returns back to normal for a week or two before it
starts to get really slow again. The load
average after recreating the databases from the dumps is between 0.00 to
0.5 which is a
significant performance increase. Queries are returned in less than
second and the server becomes responsive again.
Is this normal getting huge performance increase from dumping a database
and then recreating it? Is there another way to improve performance
other than this weekly task I do? Has anyone else run into this problem
before?
The machine specs are a AthlonXP 1400, 512MB Ram, Linux 2.4.19, xfs file
system.
Postgresql version is 7.2.1. The databases are less than 1MB.