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

Re: autovacuum not prioritising for-wraparound tables

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: autovacuum not prioritising for-wraparound tables
Date: 2013-01-29 05:09:52
Message-ID: 510759A0.6070400@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> I have to admit, I fail to see why this is a good idea. There isn't much
> of an efficiency bonus in freezing early (due to hint bits) and vacuums
> over vacuum_freeze_table_age are considerably more expensive as they
> have to scan the whole heap instead of using the visibilitymap. And if
> you don't vacuum the whole heap you can't lower relfrozenxid. So
> changing freeze_min_age doesn't help at all to avoid anti-wraparound
> vacuums.
> 
> Am I missing something?

Yep.  First, you're confusing vacuum_freeze_table_age  and
vacuum_freeze_min_age.  Second, you're not doing any arithmatic.

Let's do this by example.  TableA is a large table which receives an
almost constant stream of individual row updates, inserts, and deletes.

DEFAULTS:

XID 1:  First rows in TableA are updated.
XID 200m: Anti-wraparound autovac of TableA.
     All XIDs older than XID 100m set to FROZENXID.
XID 300m: Anti-wraparound autovac of TableA
     All XIDs older than XID 200M set to FROZENXID.
XID 400m: Anti-wraparound autovac of TableA
     All XIDs older than XID 300M set to FROZENXID.
XID 500m: Anti-wraparound autovac of TableA
     All XIDs older than XID 400M set to FROZENXID.
XID 600m: Anti-wraparound autovac of TableA
     All XIDs older than XID 500M set to FROZENXID.


vacuum_freeze_min_age = 1m

XID 1:  First rows in TableA are updated.
XID 200m: Anti-wraparound autovac of TableA.
     All XIDs older than XID 199m set to FROZENXID.
XID 399m: Anti-wraparound autovac of TableA
     All XIDs older than XID 398M set to FROZENXID.
XID 598m: Anti-wraparound autovac of TableA
     All XIDs older than XID 597M set to FROZENXID.


vacuum_freeze_min_age = 1m, autovacuum_freeze_max_age = 500m

XID 1:  First rows in TableA are updated.
XID 500m: Anti-wraparound autovac of TableA.
     All XIDs older than XID 499m set to FROZENXID.

As you can see, the current default settings cause 80% more wraparound
autovacs per table than vacuum_freeze_min_age of 1m would, and almost
500% more than what I consider sane settings would.  Just so that we can
preserve XIDs which are 90m transactions old.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2013-01-29 05:19:15
Subject: Re: pg_ctl idempotent option
Previous:From: David RowleyDate: 2013-01-29 05:01:47
Subject: Re: Hm, table constraints aren't so unique as all that

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