From: Steven M. Wheeler <swheeler@sabre.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
Date: Fri, 01 Oct 1999 14:47:32 -0500
Message-ID: <37F50FD3.F269B020@sabre.com> (raw)
References: <5487.936022818@sss.pgh.pa.us>
System: Compaq ProLiant 3000 with 2 300MHz PentPro, 512MB RAM, 32GB hardware
based stripe-set for the DB area.
OS: Linux kernel 2.2.12
DB: version 6.5
Thanks for the info, thought I'd send a followup regarding my ongoing
problems with vacuum.
After the last email, I vacuumed the DB a couple of times, with indexes
dropped. The first time it completed in 2-3 hours, the next time was
appreciably longer. After this I stopped doing anything except for inserts,
trying to catch up on the incoming data. This went on for a little over a
week until I had inserted several million additional rows (its now up to
31M+). I then dropped the indexes and started vacuum again.
Start: 09/28/1999@10:06:57
Finish: 09/30/1999@19:13:14
33 hours - WOW!
This morning, I rebuilt the indexes and tried to do a "select distinct
statdate from currnt;" This select statement has been running for several
hours now. The only output has been the following message, repeated twice:
"NOTICE: BufFileRead: should have flushed after writing". The system is
running at about 50% utilization.
I have checked everything I can think of, and at this point I have very
little hair left to loose. What the heck am I doing wrong?!?!?!?
Have I mentioned how desperate I am;-}
--
Steven Wheeler
Mid-Range UNIX Engineering
Sabre Inc.
(918) 292-4119
Tom Lane wrote:
> "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: swheeler@sabre.com, tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <37F50FD3.F269B020@sabre.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