Re: Database Optimization and Peformance - Mailing list pgsql-admin

From Jeff Frost
Subject Re: Database Optimization and Peformance
Date
Msg-id Pine.LNX.4.64.0608301141330.23923@glacier.frostconsultingllc.com
Whole thread Raw
In response to Database Optimization and Peformance  (Joe McClintock <joe.mcclintock@antics.com>)
Responses Re: Database Optimization and Peformance  (Joe McClintock <joe.mcclintock@antics.com>)
Re: Database Optimization and Peformance  (Ray Stell <stellr@cns.vt.edu>)
List pgsql-admin
On Wed, 30 Aug 2006, Joe McClintock wrote:

> 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.

Joe,

I would guess that since the dump/restore yielded good performance once again,
a VACUUM FULL would have also fixed the problem.  How are your FSM settings in
the conf file?  Can you run VACUUM VERBOSE and send us the last 10 or so lines
of output?

A good article on FSM settings can be found here:

http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W

You probably should consider setting up autovacuum and definitely should
upgrade to at least 8.0.8 if not 8.1.4 when you get the chance.

When you loaded the new data did you delete or update old data or was it just
a straight insert?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

pgsql-admin by date:

Previous
From: Joe McClintock
Date:
Subject: Database Optimization and Peformance
Next
From: "Chris Hoover"
Date:
Subject: Re: Why so long between archive calls?