Re: autovacuum not prioritising for-wraparound tables

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