Subject Re: fb 2.1 on Windows: how to use more RAM?
Author Svein Erling
--- In, Aage Johansen wrote:
> Stefan wrote:
> <<
> Database header page information:
> ...
> Oldest transaction 288095
> Oldest active 288096
> Oldest snapshot 76544
> Next transaction 501810
> ...
> >>
> This _could_ indicate that garbage is accumulating in the database,
> and no tuning of Firebird will fix this. The problem would then be
> the programs which probably don't handle transactions properly.
> Using "CommitRetaining" (with hardly any "Commit") is a prime suspect.
> --
> Aage J.

I'll elaborate a bit on what Aage wrote. You have a gap of over 200000 between oldest and next transaction. Your database was created 8:38 pm Wednesday and since then 500000 transactions have been performed. My guess is that transaction no. 288096 was started early Friday and it was still running at the time you retrieved the statistics Saturday morning.

Firebird modifies records by creating new record versions, old versions are kept until all transactions that may be interested in them commit or rollback. All updates & deletes done since Friday morning must be kept in your database until the oldest transaction finishes (and then the oldest transaction moves on until it finds another still active transaction), because the old transaction(s) may still be interested in knowing old values (depending on transaction settings, you may want to know values that were present at the time your transaction started).

Databases that respond quick when they're started, and then gradually slow down until almost coming to a halt, are a typical sign of poor transaction handling in one or more applications. The gap between oldest transaction and next transaction is often the 'proof'.

If you have one or a couple of time consuming (batch) tasks that was started yesterday and hadn't yet finished 10 AM today, then you may not have a transaction problem. If there is no reason for transactions to be active for a long time, then you probably have such a problem and it can only be fixed in the application(s) - tuning the database can at best postpone the time it takes before users start complaining. Poor transaction handling one place in your system could be enough to slow everything down (it could even be you keeping an admin tool open with a running transaction for a long time) - though it could also be due to not thinking about transactions at all during development.