Skip site navigation (1) Skip section navigation (2)

Re: Help with performance problems

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Chris Hoover" <revoohc(at)sermonaudio(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help with performance problems
Date: 2004-04-23 18:15:23
Message-ID: 200404231115.23198.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Chris,

> Sorry for the confusion here.  I can't run any sort of vacuum durin the day
> due to performance hits.  However, I have run vacuums at night.  Several
> nights a week I run a vacuumdb -f -z  on all of the clusters.  I can take
> serveral hours to complete, but it does complete.

Well, here's your first problem:  since your FSM pages is low, and you're only 
vacuuming once a day, you've got to have some serious table and index bloat.   
SO you're going to need to do VACUUM FULL on all of your databases, and then 
REINDEX on all of your indexes.

After that, raise your max_fsm_pages to something useful, like 1,000,000.   Of 
course, data on your real rate of updates would help more.

If you're getting severe disk choke when you vacuum, you probably are I/O 
bound.   You may want to try something which allows you to vacuum one table 
at a time, either pg_autovacuum or a custom script.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-performance by date

Next:From: Shea,Dan [CIS]Date: 2004-04-23 18:19:04
Subject: Re: Why will vacuum not end?
Previous:From: Chris HooverDate: 2004-04-23 18:01:29
Subject: Re: Help with performance problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group