Subject RE: [firebird-support] OIT / NT
Author Svein Erling Tysvær
>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. 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.
>Anyway, I do a sweep each night, after a backup, so that in the morning things are reset, but I want to figure
>out what goes on wrong.
>I use MON$tables - this way I figured out who (IP) in slowing down the whole thing. Tried different options to
>improve the design of the program.

What should normally happen, it that at the OAT/NT gap should increase at first, but then reach more or less a stable state, where you saw the gap sometimes increasing and sometimes decreasing. The difference of whether it increases by 5 or 500 per second is more an indication of how problematic it is (performance wise) for the user than an indication of whether transactions are handled correctly or not, the gap shouldn't continue to increase indefinitely (well, unless the use of the database continually increased, I've never heard of situations where you have 10000 committed transactions today, 11000 tomorrow, 12000 the day after and continuing to rise for a prolonged period, but I could imagine such a situation if many transactions resulted in more transactions getting started, e.g. if your program recursively creates threads for parallel processing).

The difference between your clients could just be that the problematic client just uses your database a lot more actively than your other clients.

If the OAT doesn't increase, then there's some transaction that doesn't finish (commit or rollback, commit retaining is not a commit in this sense). That might be in your program or it might be a DBA just monitoring the database (read only AND read committed transactions doesn't prevent the OAT from advancing, I think all other transactions must be committed or rolled back for the OAT to advance. If the OAT does increase, but at a slower rate than the NT, then I'd say things are strange and would check to see whether there were thrown requests to Firebird at a higher rate than it could handle. E.g. if your program asks for the count of a subset of a 5000000 record table with no index every minute, then I would expect you to quickly run into trouble - even though it might not be a problem with only one copy of your program running.