Date: Mon, 27 Feb 2006 11:31:53 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: neverending vacuum
Message-ID: <20060227143153.GC5755@surnet.cl>
Mail-Followup-To: Csaba Nagy <nagy@ecircle-ag.com>,
	postgres performance list <pgsql-performance@postgresql.org>
References: <1141047092.6846.256.camel@coppola.muc.ecircle.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1141047092.6846.256.camel@coppola.muc.ecircle.de>
User-Agent: Mutt/1.5.11+cvs20060126

Csaba Nagy wrote:

> I have a quite big table (about 200 million records, and ~2-3 million
> updates/~1 million inserts/few thousand deletes per day). I started a
> vacuum on it on friday evening, and it still runs now (monday
> afternoon). I used "vacuum verbose", and the output looks like:
> 
> [vacuums list all the indexes noting how many tuples it cleaned, then
> "restarts" and lists all the indexes again, then again ... ad nauseam]

What happens is this: the vacuum commands scans the heap and notes which
tuples need to be removed.  It needs to remember them in memory, but
memory is limited; it uses the maintenance_work_mem GUC setting to
figure out how much to use.  Within this memory it needs to store the
TIDs (absolute location) of tuples that need to be deleted.  When the
memory is filled, it stops scanning the heap and scans the first index,
looking for pointers to any of the tuples that were deleted in the heap.
Eventually it finds them all and goes to the next index: scan, delete
pointers.  Next index.  And so on, until all the indexes are done.

At this point, the first pass is done.  Vacuum must then continue
scanning the heap for the next set of TIDs, until it finds enough to
fill maintenance_work_mem.  Scan the indexes to clean them.  Start
again.  And again.

So one very effective way of speeding this process up is giving the
vacuum process lots of memory, because it will have to do fewer passes
at each index.  How much do you have?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support