Message-ID: <4936939C.9080001@hagander.net>
Date: Wed, 03 Dec 2008 15:11:40 +0100
From: Magnus Hagander <magnus@hagander.net>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Gregory Stark <stark@enterprisedb.com>
CC: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Visibility map, partial vacuums
References: <4905AE17.7090305@enterprisedb.com>
	<491D376B.9000608@enterprisedb.com>	<491D7F52.6070908@enterprisedb.com>
	<4925664C.3090605@enterprisedb.com>
	<26361.1227467112@sss.pgh.pa.us>	<492A6032.6080000@enterprisedb.com>
	<18086.1227537479@sss.pgh.pa.us>	<492D4460.1000809@enterprisedb.com>
	<5856.1227705135@sss.pgh.pa.us>	<492EF88F.9050709@enterprisedb.com>
	<4936884B.6050205@enterprisedb.com>
	<87tz9lqs2a.fsf@oxford.xeocode.com>
In-Reply-To: <87tz9lqs2a.fsf@oxford.xeocode.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Gregory Stark wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> 
>> Hmm. It just occurred to me that I think this circumvented the anti-wraparound
>> vacuuming: a normal vacuum doesn't advance relfrozenxid anymore. We'll need to
>> disable the skipping when autovacuum is triggered to prevent wraparound. VACUUM
>> FREEZE does that already, but it's unnecessarily aggressive in freezing.
> 
> Having seen how the anti-wraparound vacuums work in the field I think merely
> replacing it with a regular vacuum which covers the whole table will not
> actually work well.
> 
> What will happen is that, because nothing else is advancing the relfrozenxid,
> the age of the relfrozenxid for all tables will advance until they all hit
> autovacuum_max_freeze_age. Quite often all the tables were created around the
> same time so they will all hit autovacuum_max_freeze_age at the same time.
> 
> So a database which was operating fine and receiving regular vacuums at a
> reasonable pace will suddenly be hit by vacuums for every table all at the
> same time, 3 at a time. If you don't have vacuum_cost_delay set that will
> cause a major issue. Even if you do have vacuum_cost_delay set it will prevent
> the small busy tables from getting vacuumed regularly due to the backlog in
> anti-wraparound vacuums.
> 
> Worse, vacuum will set the freeze_xid to nearly the same value for all of the
> tables. So it will all happen again in another 100M transactions. And again in
> another 100M transactions, and again...
> 
> I think there are several things which need to happen here.
> 
> 1) Raise autovacuum_max_freeze_age to 400M or 800M. Having it at 200M just
>    means unnecessary full table vacuums long before they accomplish anything.
> 
> 2) Include a factor which spreads out the anti-wraparound freezes in the
>    autovacuum launcher. Some ideas:
> 
>     . we could implicitly add random(vacuum_freeze_min_age) to the
>       autovacuum_max_freeze_age. That would spread them out evenly over 100M
>       transactions.
> 
>     . we could check if another anti-wraparound vacuum is still running and
>       implicitly add a vacuum_freeze_min_age penalty to the
>       autovacuum_max_freeze_age for each running anti-wraparound vacuum. That
>       would spread them out without being introducing non-determinism which
>       seems better.
> 
>     . we could leave autovacuum_max_freeze_age and instead pick a semi-random
>       vacuum_freeze_min_age. This would mean the first set of anti-wraparound
>       vacuums would still be synchronized but subsequent ones might be spread
>       out somewhat. There's not as much room to randomize this though and it
>       would affect how much i/o vacuum did which makes it seem less palatable
>       to me.

How about a way to say that only one (or a config parameter for <n>) of
the autovac workers can be used for anti-wraparound vacuum? Then the
other slots would still be available for the
small-but-frequently-updated tables.


> 3) I also think we need to put a clamp on the vacuum_cost_delay. Too many
>    people are setting it to unreasonably high values which results in their
>    vacuums never completing. Actually I think what we should do is junk all
>    the existing parameters and replace it with a vacuum_nice_level or
>    vacuum_bandwidth_cap from which we calculate the cost_limit and hide all
>    the other parameters as internal parameters.

It would certainly be helpful if it was just a single parameter - the
arbitraryness of the parameters there now make them pretty hard to set
properly - or at least easy to set wrong.


//Magnus