Re: autovacuum not prioritising for-wraparound tables

On 2013-02-02 14:54:10 -0500, Robert Haas wrote:
> On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I think scheduling a table for a partial vacuum every min_freeze * 2
> > xids, even if its insert only, would go a long way of reducing the
> > impact of full-table vacuums. Obviously that would require to retain the
> > last xid a vacuum was executed in...
>
> I'm not sure that min_freeze * 2 is the right value, but otherwise agreed.

Yes, min_freeze * 2 was purely a very first quick guess. My reasoning
was basically that values smaller than that won't really do all that
much work since barely any of the tuples are old enough and values much
bigger won't help much in reducing huge amounts of writes being
submitted at the same time and they might miss the window before the
next anti-freeze vacuum.
I guess something like 5 or so would theoretically be better because it
would reduce the number of tuples that are too young for freezing which
will be marked all-visible nonetheless and thus not scanned again. But
it would not work with the current relevant default settings:

vacuum_freeze_table_age = 150,000,000
vacuum_freeze_min_age = 50,000,000
autovacuum_freeze_max_age = 200,000,000

I guess we will have to think about the default for those values. Adhoc
I am thinking something like:

vacuum_freeze_table_age = 300,000,000
vacuum_freeze_min_age = 20,000,000
autovacuum_freeze_max_age = 800,000,000

and scheduling a vacuum independent from n_dead_tuples every
(freeze_table_age - freeze_max_age ) / 5 or so xids. That would mean
that approx 4/5 (or more on a busy system) of the tuples would get
frozen before a full-table vacuum.
I don't the disk size argument for freeze_max_age = 200000000 is
particularly relevant anymore, especially as we would normally keep the
size at vacuum_freeze_table_age.

To finally fix the issue ISTM that we need an 'age map' to know which
parts to scan again and which parts never need to be scanned again, but
thats a separate, not too small, feature.

> I keep coming back to the idea that vacuum should have a high-priority
> queue and a low-priority queue. When stuff meets the current
> thresholds, it goes into the high-priority queue. But then there
> should be a low-priority queue where we do partial vacuums of things
> that meet some lower threshold - like the unfrozen portions of
> insert-only tables.

I don't think thats the most crucial part atm. Such a queue doesn't
solve the problem that we don't want to do unneccesary, repetitive work.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services