User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Mon, 25 Jul 2005 15:48:39 -0700
Subject: Re: "Vacuum Full Analyze" taking so long
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Tomeh, Husam" <htomeh@firstam.com>,
	pgsql-performance@postgresql.org
Message-ID: <BF0ABA57.9EF6%llonergan@greenplum.com>
In-Reply-To: 
 <C45835824D00A844BBD0F032D5CDED920116DC10@pisgana01sxch01.ana.firstamdata.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

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,
>