> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
