From: Antoine <melser.anton@gmail.com>
To: pgsql-performance@postgresql.org
Subject: new to postgres (and db management) and performance already a problem :-(
Date: Mon, 16 Jan 2006 23:07:52 +0100
Message-ID: <43CC1938.7020807@gmail.com> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: melser.anton@gmail.com
Subject: Re: new to postgres (and db management) and performance already a problem :-(
In-Reply-To: <43CC1938.7020807@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox