Sender: smw@sabre.com
Message-ID: <37F50FD3.F269B020@sabre.com>
Date: Fri, 01 Oct 1999 14:47:32 -0500
From: "Steven M. Wheeler" <swheeler@sabre.com>
Organization: Mid-range UNIX Engineering, The SABRE Group
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
References: <5487.936022818@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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