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

Re: autovacuum not prioritising for-wraparound tables

From: Kevin Grittner <kgrittn(at)ymail(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autovacuum not prioritising for-wraparound tables
Date: 2013-01-30 13:39:29
Message-ID: 1359553169.13579.YahooMailNeo@web162903.mail.bf1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-01-29 16:09:52 +1100, Josh Berkus wrote:
>>
>>> 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.
>
> Don't think I did. I was talking about vacuum_freeze_table_age
> because that influences the amount of full-table scans

Not any more than vacuum_freeze_min_age does.

http://www.postgresql.org/docs/9.2/interactive/routine-vacuuming.html#AUTOVACUUM

| a whole table sweep is forced if the table hasn't been fully
| scanned for vacuum_freeze_table_age minus vacuum_freeze_min_age
| transactions.

So reducing vacuum_freeze_min_age not only helps minimize the
writes that are needed when autovacuum needs to scan the entire
heap, but also decreases the frequency of those full-table scans.

-Kevin


In response to

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2013-01-30 13:50:02
Subject: sql_drop Event Trigger
Previous:From: Andres FreundDate: 2013-01-30 09:48:40
Subject: Re: autovacuum not prioritising for-wraparound tables

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