From: Josh Berkus <josh@agliodbs.com>
To: Chris Hoover <revoohc@sermonaudio.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with performance problems
Date: Fri, 23 Apr 2004 11:15:23 -0700
Message-ID: <200404231115.23198.josh@agliodbs.com> (raw)
In-Reply-To: <200404231353.17234.revoohc@sermonaudio.com>
References: <200404230931.17850.revoohc@sermonaudio.com>
<4089561B.8010001@speedfc.com>
<200404231353.17234.revoohc@sermonaudio.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, revoohc@sermonaudio.com
Subject: Re: Help with performance problems
In-Reply-To: <200404231115.23198.josh@agliodbs.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