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