Gregory Stark wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
