----- Original Message -----
Sent: Monday, November 24, 2003 4:03
PM
Subject: [PERFORM] Where to start for
performance problem?
I've scanned some of the archives and have
learned a lot about different performance tuning practices. I will be
looking into using many of these ideas but I'm not sure they address the issue
I am currently experiencing.
First, I'm a total newb with postgresql.
Like many before me, I have inherited many responsibilities outside of my
original job description due to layoffs. I am now the sole
developer/support for a software product. *sigh* This product uses
postgresql. I am familiar with the basics of sql and have worked on the
database and code for the software but by no means am I proficient with
postgresql.
The archives of this list provides many ideas for
improving performance, but the problem we are having is gradually degrading
performance ending in postgres shutting down. So it's not a matter of
optimizing a complex query to take 5 seconds instead of 60 seconds.
From what I can tell we are using the VACUUM command on a schedule but it
doesn't seem to prevent the database from becoming "congested" as we refer to
it. :] Anyway, the only way I know to "fix" the problem is to
export (pg_dump) the db, drop the database, recreate the database and import
the dump. This seems to return performance back to normal but obviously
isn't a very good "solution". The slowdown and subsequent crash can take
as little as 1 week for databases with a lot of data or go as long as a few
weeks to a month for smaller data sets.
I don't really know where to start looking for a
solution. Any advice on where to start, understanding that I am a newb,
would be greatly appreciated. Thank you.
Nid