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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-01-29 05:19:15 | Re: pg_ctl idempotent option |
Previous Message | David Rowley | 2013-01-29 05:01:47 | Re: Hm, table constraints aren't so unique as all that |