Subject Re: [firebird-support] Sweep Interval
Author Fabian Chocron
Hi Helen,

UPS!, I think I am begining to undestand a few problems here.

1) I am using autocommit, and in the App the is no Begin Transaction and
Commit at all. If I understood your comments, I MUST execute a "Non Soft
Commit", wich in SQL would be: "CommitRetaining"????

2) Every user that is conected to the database, and is reading some tables,
is considered an OPEN transaction for the Database until he/she closes the


I will apreciate your comments!!!!.

Many Thanks

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <>
Sent: Monday, January 12, 2004 1:15 PM
Subject: Re: [firebird-support] Sweep Interval

> At 11:09 AM 12/01/2004 +1300, you wrote:
> >My app seems to be slowing down as the day progresses. I have looked
> >thru the source code and haven't found any opened transactions. To get
> >around this problem (temporarily) I thought I might set sweep interval
> >to a smaller value (like 100 or 200). Is it ok to do this? Will there be
> >any side effects.
> It won't make any difference to your problem. The side effect if you set
> the sweep interval to 0 would be that *if* your database needed sweeping,
> you'd need to make a point of doing a manual sweep periodically. Since
> sweep is the only way to get rid of backversions related to rolled-back
> transactions, only a sweep will clean these out.
> Your problem is almost certainly being caused by accumulation of garbage,
> for which long-running transactions are usually to blame. If LRTs are
> endemic in your system then sweeping doesn't fix the cause of the problem.
> Use isql SHOW DATABASE to check the difference between "Oldest
> (= "OIT" oldest INTERESTING transaction, which can be more revealing than
> "Oldest Active Transaction" if it's your application that is hanging up
> garbage collection) and Next Transaction. A big difference and/or a gap
> that keeps growing will indicate that GC is being inhibited by a
> combination of user behaviour and application behaviour.
> Sweep interval sets the threshold for the engine to perform a sweep. If
> is 20000 (the default) it says "Don't perform any sweep until the
> difference between the oldest interesting transaction and the newest
> transaction is 20000 or more". *Then* a sweep will kick in at the next
> opportunity and clean out rolled back and obsolete versions that are
> *older* than the OIT. Anything between the OIT and the Next will be
> ignored. It is this bunch that are causing your performance to degrade.
> If you are taking good care of transaction life, i.e. the gap between OIT
> and NT is reasonably correspondent with the number of transactions you
> estimate *should* be in an uncommitted or unrolledback state, then GC will
> look after garbage collection without help and you can happily do any
> occasional manual sweep to muck out the obsolete versions from rollbacks.
> Watch out for:
> Autocommit transactions in your applications. These are murderous for the
> Gap. OK to use Autocommit but you have to make a way to force a hard
> commit occasionally through the day to free up the old versions. As long
> as you keep doing soft commits, you hold the original transaction context
> open and it keeps being interesting.
> Explicit calls to CommitRetaining. Same problem as Autocommit.
> Programmers and others who keep themselves logged into Admin tools all day
> long, with uncommitted read-write transactions tying up the records being
> used by the real users.
> Applications that open datasets involving rows that get a lot of access
> just hold the transaction open. Even if they aren't doing anything to
> change the data, they are interesting as long as a read-write transaction
> is holding them.
> /hb
> Yahoo! Groups Links
> To visit your group on the web, go to:
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to: