Hello All,
I'm just goin' thru this thread discussion and have found some tips to tune up my database. Thanks to all.
I've some questions reagarding some more tuning of the fbserver.
The version of the Firebird database I'm using is
ISQL Version: WI-T1.5.0.3481 Firebird 1.5 Release Candidate 3
Firebird/x86/Windows NT (access method), version "WI-T1.5.0.3481 Firebird 1.5 Release Candidate 3"
on disk structure version 10.1

I'm accessing the database thru .NET Provider. As per my client's need, the lifetime of the firebird server is controlled by my application. In my application, 2 threads keep on pumping data in the database and a few other threads keep on accessing it. I always do a commit before any operation related to the database. The application is quite stable on Windows XP .The memory it uses is also not bad, even though the application keeps on running for 2-3 days. But when it executes on Windows 2000, fbserver.exe eats all the RAM available in near about 12-15 hours.
I ne'er knew how to interpret this sweep interval with OIT and NT. Went through ur discussion and came to know that sweep interval can matter a lot for stale versions of records. I tried to look into my database and found that there is barely a difference of 5-10 between OIT and NT. I think that this is because of the commit I issue after every database operation. Can anyone point out is that anything else I'm missing? On Windows 2000 machines it keeps on eating memory and I've to shut down the fbserver. Next time it starts up normally just to be closed after next 15 hours. Is there some known issue with Windows 2000 Professional machines (with IIS also running) and firebird?

Would be looking forward for any help.
Thanx in advance.
Mahesh.

Helen Borrie <helebor@...> 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





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/



---------------------------------
Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now

[Non-text portions of this message have been removed]