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.
FWIW, it seems the omission is actually the other way 'round. Autovacuum
always forces a full-scanning vacuum, making the visibility map useless
for autovacuum. This obviously needs to be fixed.
> 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...
But we already have that problem, don't we? When you initially load your
database, all tuples will have the same xmin, and all tables will have
more or less the same relfrozenxid. I guess you can argue that it
becomes more obvious if vacuums are otherwise cheaper, but I don't think
the visibility map makes that much difference to suddenly make this
issue urgent.
Agreed that it would be nice to do something about it, though.
> 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.
It allows you to truncate clog. If I did my math right, 200M
transactions amounts to ~50MB of clog. Perhaps we should still raise it,
disk space is cheap after all.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com