Subject Re: [firebird-support] OIT / NT
Author Michael Ludwig

consider the comments on COMMIT RETAINING in the quotes from Helen's and
Ann's messages below - maybe there's one place where you're running with
the default and it's only triggered on the installation you're seeing
the problem on.


Tiberiu Horvath schrieb am 31.03.2012 um 10:03 (+0300):
> My BIG concern is that my program (Delphi XE with good old IBX
> components) run with the exactly same release, on 4 different clients
> generating diferent results.

FB performance - Helen Borrie 06.01.06

Then look closely at how transactions are being committed. The IBX
default for AutoCommit is to use CommitRetaining. This is handy for
Delphi client apps and disastrous for the server. It causes garbage to
build up steadily. You are seeing better performance on a clean
database and degrading performance as garbage builds up and doesn't
get cleared.

> 3 of these are in normal parameters (while working, the gap between
> OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec)
> but when that particular client (same program, same FB client, same
> OS, same ISP) starts my program, the mentioned values increase in an
> alarming rate of 100...200 / 5 sec, so that at the end of a normal
> working day, the gap is arround 1.5 mil transactions. People using the
> program at this company complain about the slowdown in the afternoon
> compared with the morning.

> From: Ann Harrison
> Sent: Friday, March 30, 2012 7:07 PM

> OK, time for my lecture on OAT and OIT.

> The OAT is the oldest transaction that the system considers to be
> active. It blocks garbage collection and induces database bloat.
> Transactions that commit using "commit retaining" do not advance the
> OAT. Transactions that are left open for hours - even transactions
> that have not changed the database - leave the OAT stuck. Once the OAT
> is stuck, Firebird must keep old versions of records that transaction
> might read if it ever wakes up and starts working again.