> I will admit that the "Preventing Transaction ID Wraparound Failures"
> chapter in the manual (and the subsequent autovacuum daemon chapter) make
> my head spin.
The basic idea is pretty simple, really. You have a limited amount of
transaction IDs, so you arrange them into a circle, and you are moving the
'start' from time to time. And of course you must not skip too far the
past suddenly turns into future.
All that 'technical details' about modulo-2^31 arithmetic is just about
this basic idea. Start is equal to the frozen XID.
> Is there some specific query related to relfrozenxid, datfrozenxid that I
> should be utilizing to do some analysis? Obviously SOME threshhold has
> been reached…hence autovacuum stepping in.
I guess the threshold that triggered this is autovacuum_freeze_max_age,
with a default value of 200 million. So if it took you 6 months to reach
this threshold, it shouln't be a problem in the next few years (until you
reach one or two billion XIDs, I'm not sure right now). But if the vacuum
fixes this, then you're safe.
You can see the current frozenxid/age for each database or relation
SELECT datname, age(datfrozenxid) FROM pg_database;
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
> My two real questions are:
>
> 1. Am I in any real danger here? I have run autovacuum continuously
> since the dump-restore that created this database 6 months ago.
I don't think so. The autovacuum starts this VACUUM FREEZE behavior after
one billion XIDs, and there are two billion XIDs available. So if it took
6 months to trigger this, you have about another 6 months to solve it. And
the autovacuum should do that in 6 months I guess.
> 2. These autovacuums have been running for 5 days now. I have a
> database maintenance window tonight where I have to take down the
> server. I assume autovacuum will re-awaken and decide to start
> vacuuming these again. I assume I am effecting performance with these
> needless? vacuums. Any tweak I can/should make to any of the freeze_age
> params to prevent this?
Well, you could play a bit with the vacuum_freeze_min_age/ (increase or
decrease it), but I guess it's not worth it (especially in case of these
growing tables).
What are the (auto)vacuum cost parameters? If there's not a significant
I/O load, you could try to make it more aggresive, so that it finishes
sooner. Just decrease the autovacuum_vacuum_cost_delay to 10 and increase
the autovacuum_vacuum_cost_limit (to 1000 or something like that).
regards
Tomas