Skip site navigation (1) Skip section navigation (2)

autovacuum not prioritising for-wraparound tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: autovacuum not prioritising for-wraparound tables
Date: 2013-01-24 21:57:15
Message-ID: 20130124215715.GE4528@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

I have a bug pending that autovacuum fails to give priority to
for-wraparound tables.  When xid consumption rate is high and dead tuple
creation is also high, it is possible that some tables are waiting for
for-wraparound vacuums that don't complete in time because the workers
are busy processing other tables that have accumulated dead tuples; the
system is then down because it's too near the Xid wraparound horizon.
Apparently this is particularly notorious in connection with TOAST
tables, because those are always put in the tables-to-process list after
regular tables.

(As far as I recall, this was already reported elsewhere, but so far I
have been unable to find the discussion in the archives.  Pointers
appreciated.)

So here's a small, backpatchable patch that sorts the list of tables to
process (not all that much tested yet).  Tables which have the
wraparound flag set are processed before those that are not.  Other
than this criterion, the order is not defined.

Now we could implement this differently, and maybe more simply (say by
keeping two lists of tables to process, one with for-wraparound tables
and one with the rest) but this way it is simpler to add additional
sorting criteria later: say within each category we could first process
smaller tables that have more dead tuples.

My intention is to clean this up and backpatch to all live branches.
Comments?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Attachment: autovacuum-toast-wraparound.patch
Description: text/x-diff (6.0 KB)

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2013-01-24 22:09:51
Subject: Re: COPY FREEZE has no warning
Previous:From: Tom LaneDate: 2013-01-24 21:51:04
Subject: Re: [HACKERS] BUG #6572: The example of SPI_execute is bogus

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group