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

Re: autovacuum not prioritising for-wraparound tables

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 (view raw or flat)
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


In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2013-01-30 15:44:23
Subject: Re: pg_dump --pretty-print-views
Previous:From: Andres FreundDate: 2013-01-30 15:34:53
Subject: Re: backend hangs at immediate shutdown (Re: Back-branch update releases coming in a couple weeks)

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