From: Joe McClintock <joe.mcclintock@antics.com>
To: pgsql-admin@postgresql.org
Subject: Database Optimization and Peformance
Date: Wed, 30 Aug 2006 11:29:50 -0700
Message-ID: <44F5D91E.8000806@antics.com> (raw)
I have inherited the management of a Postgresql 8.0.3 database of around
2.3 GB and growing. I’m still coming up to speed on Postgresql db
administration so I’m not an expert although I have had quite a bit of
experience with Oracle for what that is worth.
Recently we had an issue with extreme performance degradation on a pair
of key tables where a simple “select * from [table name]” had gone for
1.08 mill sec 37 to 45 sec. This caused a very negative impact on the
performance of our web application. We had just loaded about 1.2 M rows
of data to the db on the previous Wednesday and on Thursday and Friday
the database and application performance was good. Weekend activity is
pretty low so it was surprising to find on Monday the db and application
performance in terrible shape. If the data load degraded database
performance why did it take 4 days for the problem to manifest? Regular
nightly backups are done but as far as I can tell vacuum, analyze and
reindex has been done only when needed which to this point has not been
very often.
I ran a vacuum, analyze and reindex on the database with no change in
performance, query time was still 37+ sec, a little worse. On our test
system I found that a db_dump from production and then restore brought
the database back to full performance. So in desperation I shut down the
production application, backed up the production database, rename the
production db, create a new empty production db and restored the
production backup to the empty db. After a successful db restore and
restart of the web application, everything was then up and running like
a top.
My concern is this, doing a backup and restore does not seem an
appropriate way manage database fragmentation and performance. The
documentation I have read indicates that vacuum, analyze reindex are the
tools to use to de-fragment and optimize the database. In my case they
did not work and reindexing made query performance slightly worse. Am I
missing something? As the database grows, will I need to essentially
rebuild the db on a regular basis?
Thanks
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: joe.mcclintock@antics.com
Subject: Re: Database Optimization and Peformance
In-Reply-To: <44F5D91E.8000806@antics.com>
References: <44F5D91E.8000806@antics.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