From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven M. Wheeler <swheeler@sabre.com>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
Date: Mon, 30 Aug 1999 10:20:18 -0400
Message-ID: <5487.936022818@sss.pgh.pa.us> (raw)
In-Reply-To: <37CA7ABA.7CFFC6F5@sabre.com>
"Steven M. Wheeler" <swheeler@sabre.com> writes:
> Does anyone have recommendations regarding vacuum analyze?
> Specifically:
> 1) Should it be run on a system that is quiet or will it run acceptably
> with other processes accessing the DB?
Vacuum will run fine, but it obtains an exclusive lock on each table
while it is working on that table. You are likely to find your other
processes waiting for the lock...
> 2) How often should it be run?
How fast does your database change?
I'd doubt that running vacuum analyze, as opposed to a plain vacuum,
needs to be done very often --- it's unlikely that the statistics
vacuum analyze measures are changing that fast, especially not in
million-row tables. The other function of vacuum is reclaiming space
from dead tuples, and the need for that depends on how often you update
or delete tuples.
I'm just guessing here, but a rule of thumb might be that it's worth
vacuuming when something like 20% of the tuples in your table are
dead, ie, the number of updates/deletes you've done since last vacuum
is about 20% of the table row count.
6.5 seems to have some performance problems with vacuuming large
indexes, BTW. We are looking into that, but in the meantime you might
experiment with dropping indexes on a table, vacuum table, recreating
indexes to see if that's faster than a straight vacuum.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, swheeler@sabre.com, pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <5487.936022818@sss.pgh.pa.us>
* 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