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
Date: Mon, 27 Feb 2006 11:31:53 -0300
Message-ID: <20060227143153.GC5755@surnet.cl> (raw)
In-Reply-To: <1141047092.6846.256.camel@coppola.muc.ecircle.de>
References: <1141047092.6846.256.camel@coppola.muc.ecircle.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, nagy@ecircle-ag.com
Subject: Re: neverending vacuum
In-Reply-To: <20060227143153.GC5755@surnet.cl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox