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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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

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.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Training & Services

Attachment Content-Type Size
autovacuum-toast-wraparound.patch text/x-diff 6.0 KB


Browse pgsql-hackers by date

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