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
connection????

IF THIS IS THE CASE, THEN THIS EXPLAINS WHY MY APPS ARE GETTING TO SLOW.

I will apreciate your comments!!!!.


Many Thanks
Fabian.

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
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
transaction"
> (= "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
it
> 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
and
> 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:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>