Hi Helen,

Helen Borrie wrote:

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

Iam reading your comment here and as usual Iam learning a lot from it.
May I ask a few question? In my application I always doing a hard commit
(ib_transaction.commitretainning;) every time a record has been post or
update to make sure that all the other client are viewing the lastest
and updated records. Will there be any bad effect to the Firebird
performance if I always making a hard commit?

Thanks in advance to your comment. :-)

James

>
>
>
>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/
>
>
>
>
>
>