Re: [SQL] Questions about vacuum analyze

From: "Steven M(dot) Wheeler" <swheeler(at)sabre(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)hub(dot)org
Subject: Re: [SQL] Questions about vacuum analyze
Date: 1999-10-01 19:47:32
Message-ID: 37F50FD3.F269B020@sabre.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)10:06:57
Finish: 09/30/1999(at)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(at)sabre(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Momjian 1999-10-01 20:14:17 Re: [SQL] Questions about vacuum analyze
Previous Message Clayton Cottingham 1999-10-01 16:40:13 Re: [SQL] How about a postgreSQL cookbook?