pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (7+ messages)  latest in thread

Message-ID: <44F5D91E.8000806@antics.com>
Permalink:  ../44F5D91E.8000806@antics.com/
Also on:    postgresql.org/message-id/44F5D91E.8000806@antics.com

 · 

reply

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