Nico Callewaert wrote:

>
> A while ago I mentioned some customers have poor performance, especially
> when > 20 connections. I'm aware of the fact that there is a design
> problem, I just can't figure out what exactly.
> I checked with gstat and saw the following (probably disturbing numbers) :
>
> Oldest transaction : 91891
> Oldest active : 2956068
> Oldest snapshot : 2956068
> Next transaction : 2956086

Is the customer using classic?

>
> I guess the gap between Oldest en Next transaction is way too big ?

Yes, it's big. All transactions older than this "oldest" are known to
be committed. Firebird keeps the state of every transaction between
the oldest and the next so it can tell if the transaction that created
the record committed and the record is valid. Transactions have four
states, so each transaction between the oldest and next uses two bits
of memory - four transactions to the byte. So, this database used
about 700Kb of memory to track transaction state.

In superserver, one 700Kb vector isn't a big deal, given modern memory
sizes. In classic, each connection gets its own vector, and that might
be significant.

> Am I right ? After doing a sweep, the Oldest and Next become equal.

Good. You don't have a transaction stuck in limbo. Transactions can be
active (0), committed (1 or 2), rolled back (whichever committed isn't),
or limbo (3). Limbo is the state of a transaction that has prepared
itself for a two phase commit, but hasn't gone forward or back, and
really shouldn't be forced one way or the other until it hears from
its partners. So limbo transactions require intervention.

> I've read some articles on IBPhoenix, but I don't quite understand it.
> And I don't know what is causing that giant gap between Oldest and Next.
> All queries are set to AutoCommit, so when a record is inserted/updated,
> the transaction is committed. Or am I missing something ?

(Grumbles about autocommit suppressed). Autocommit doesn't insure that
transactions are committed. If the system crashes while a transaction
is active, the transaction will roll back - and that's probably what
happened to transaction 91891 - either the system or the server crashed.

Has the performance improved?


Good luck,

Ann