Thread: Transaction Id wraparounds
Hi, What fast, efficient options are available for preventing transaction Id wraparounds for a growing database (mainly inserts). From the administrator's guide I understand a system wide VACUUM or VACUUM FREEZE would perform the job but for a large database (>10GB and growing) this takes a long time to run. Only selective vacuums on the modified tables tend to be more effcient. However, there are also tables (for example tables that are no long updated but have heavy selects on them) in the DB that aren't touched by a vacuum in weeks (probably never). In such a high activity database what would then be the best way to take care of the transaction id wraparounds (for example, would shutting off and restarting the db do the trick). Also, what should be the criteria to check when this issue needs to be addressed. For example in one of our databases age(datfrozenxid) is 1076913425. Thanks.. Amin
On Thu, Sep 05, 2002 at 11:27:38AM -0400, Amin Abdulghani wrote:
> Hi,
>
> What fast, efficient options are available for preventing
> transaction Id wraparounds for a growing database (mainly
> inserts). From the administrator's guide I understand a
> system wide VACUUM or VACUUM FREEZE would perform the job
> but for a large database (>10GB and growing) this takes a
> long time to run.
As I understand it, _only_ vacuum or dump and initdb will solve this
problem.
But who cares if vacuum takes a long time? The transaction
wraparound stuff is only available in 7.2 and later, so oyu can use
the non-blocking vacuum in 7.2, and let it run for a week if it takes
that long.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Amin Abdulghani <amin@quantiva.com> writes:
> From the administrator's guide I understand a
> system wide VACUUM or VACUUM FREEZE would perform the job
> but for a large database (>10GB and growing) this takes a
> long time to run.
So? You don't have to do it often, and it doesn't lock your
tables against normal operations ... so I don't really see the
objection. I would not bother with FREEZE unless you are trying
to make a template database.
> what should be the criteria to check when this issue needs
> to be addressed.
When age(datfrozenxid) approaches 2 billion, it's time. I believe
vacuum will start warning you well before that.
regards, tom lane
Thanks for the replies. As for the warning message, probably it may be better to put it at the DB connection time. Another option might be to possibly do a forced automatic VACUUM in such cases and thus avoid any potential integrity problem. This could possibly be a configuration option FORCE_VACUUM if age(FrozenXID)>threshold. Thanks.. Amin On Thu, 05 Sep 2002 12:08:04 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: >Amin Abdulghani <amin@quantiva.com> writes: >> From the administrator's guide I understand a >> system wide VACUUM or VACUUM FREEZE would perform the >>job >> but for a large database (>10GB and growing) this takes >>a >> long time to run. > >So? You don't have to do it often, and it doesn't lock >your >tables against normal operations ... so I don't really >see the >objection. I would not bother with FREEZE unless you are >trying >to make a template database. > >> what should be the criteria to check when this issue >>needs >> to be addressed. > >When age(datfrozenxid) approaches 2 billion, it's time. > I believe >vacuum will start warning you well before that. > > regards, tom lane