Hi,
We have a horribly designed postgres 8.1.0 database (not my fault!). I
am pretty new to database design and management and have really no idea
how to diagnose performance problems. The db has only 25-30 tables, and
half of them are only there because our codebase needs them (long story,
again not my fault!). Basically we have 10 tables that are being
accessed, and only a couple of queries that join more than 3 tables.
Most of the action takes place on two tables. One of the devs has done
some truly atrocious coding and is using the db as his data access
mechanism (instead of an in-memory array, and he only needs an
array/collection).
It is running on an p4 3000ish (desktop model) running early linux 2.6
(mdk 10.1) (512meg of ram) so that shouldn't be an issue, as we are
talking only about 20000 inserts a day. It probably gets queried about
20000 times a day too (all vb6 via the pg odbc).
So... seeing as I didn't really do any investigation as to setting
default sizes for storage and the like - I am wondering whether our
performance problems (a programme running 1.5x slower than two weeks
ago) might not be coming from the db (or rather, my maintaining of it).
I have turned on stats, so as to allow autovacuuming, but have no idea
whether that could be related. Is it better to schedule a cron job to do
it x times a day? I just left all the default values in postgres.conf...
could I do some tweaking?
Does anyone know of any practical resources that might guide me in
sorting out these sorts of problems? Some stuff with pratical examples
would be good so I could compare with what we have.
Thanks
Antoine
ps. I had a look with top and it didn't look like it was going much over
15% cpu, with memory usage negligeable. There are usually about 10 open
connections. I couldn't find an easy way to check for disk accessings.
pps. The db is just one possible reason for our bottleneck so if you
tell me it is very unlikely I will be most reassured!