Re: "Vacuum Full Analyze" taking so long - Mailing list pgsql-performance

Nothing was running except the job. The server did not look stressed out
looking at top and vmstat. We have seen slower query performance when
performing load tests, so I run the re-index on all application indexes
and then issue a full vacuum. I ran the same thing on a staging server
and it took less than 12 hours. Is there a possibility the DB pages are
corrupted. Is there a command to verify that. (In Oracle, there's a
dbverify command that checks for corruption on the data files level).

The other question I have. What would be the proper approach to rebuild
indexes. I re-indexes and then run vacuum/analyze. Should I not use the
re-index approach, and instead, drop the indexes, vacuum the tables, and
then create the indexes, then run analyze on tables and indexes??

Thanks,


--
 Husam

-----Original Message-----
From: Luke Lonergan [mailto:llonergan@greenplum.com]
Sent: Monday, July 25, 2005 3:49 PM
To: Tomeh, Husam; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] "Vacuum Full Analyze" taking so long

Vacuum full takes an exclusive lock on the tables it runs against, so if
you have anything else reading the table while you are trying to run it,
the vacuum full will wait, possibly forever until it can get the lock.

What does the system load look like while you are running this?  What
does vmstat 1 show you?  Is there load on the system other than the
database?

Do you really need to run vacuum full instead of vacuum?

- Luke


On 7/25/05 2:30 PM, "Tomeh, Husam" <htomeh@firstam.com> wrote:

>
> I have an 8.02 postgresql database with about 180 GB in size, running
> on
> 2.6 RedHat kernel with 32 GB of RAM and 2 CPUs. I'm running the vacuum

> full analyze command, and has been running for at least two
> consecutive days with no other processes running (it's an offline
> loading server). I tweaked the maintenanace_mem to its max (2 GB) with

> work_mem of 8M. I have no issues with my checkpoints. I can still I/O
> activities against the physical files of the "property" table and its
> two indexes (primary key and r index). The property files are about
> 128GB and indexes are about 15 GB. I have run the same maintenance job

> on a different box
> (staging) with identical hardware config (except with 64 GB instead of
> 32) and took less than 12 hours.   Any clue or tip is really
> appreciated.
>
> Also read a comment by Tom Lane, that terminating the process should
> be crash-safe if I had to.
>
> Thanks,
>



**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged.  If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited.  If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************