From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: autovacuum not prioritising for-wraparound tables |
Date: | 2013-01-30 15:35:46 |
Message-ID: | CAMkU=1whPguiVH_ySMH9n_zDKKghHnE+g5ecfw+nVEH3XZz=OQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Jan 28, 2013 at 9:09 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> 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.
You seem to be assuming the only reason for vacuums to occur on this
table is autovacuum_freeze_max_age, which doesn't seem likely to be
the case for a busy table.
> 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.
You can get nearly all the benefits of your "sane" settings just by
increasing autovacuum_freeze_max_age and leaving vacuum_freeze_min_age
alone. (Assuming the table doesn't get vacuumed for other reasons)
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2013-01-30 15:44:23 | Re: pg_dump --pretty-print-views |
Previous Message | Andres Freund | 2013-01-30 15:34:53 | Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks) |