--- In firebird-support@yahoogroups.com, "k_smith_000"
<k_smith_000@y...> wrote:

>
> Hello,
>
> Recently I'm having performance problems with quite big database (5GB)
> with many (~80) concurrent users.
>
> My question is whether it is possible to display information about:
> -active transactions
> -who and when started transaction
>

No. There is a current_transaction context variable available, but
Firebird stores no information about the user or client program etc. Of
course you could log this information yourself.

Transactions numbers are chronological (except I think after a backup /
restore they reset), so the older they are, the smaller the number.

> It seems that sometimes firebird hangs and I don't know exactly what
> the database is doing...

You may want to volunteer a bit more information about what you mean by
hang. Does it take a long time to establish a connection, or does the
CPU stay pegged at 100%?

If the CPU is at 100% for an extended period of time, that is normally
a symptom of garbage collection. Garbage is essentially records that
are no longer of interest to anyone. It includes aborted inserts,
rolled back updates, the record before you made the update
(eventually), deleted records (eventually), and probably a handful of
other things.

When you make a change (update or delete) to a record. Firebird creates
a new version of that record, so that those transactions that are still
interested in the old value of the record can still read it, but all
new transactions will see the new value.

Now the big problem (particularly for those not familiar with Firebird)
is transaction management. When you keep transactions open for
excessively long periods of time, you keep old record versions in an
interesting state. This means that there is a chance that those old
transactions may need to read some old record versions.

Now if this transaction suddenly commits / rolls back, then your
database has a lot of records that are of no interest to anyone
(garbage). This needs to be cleaned up because

1) It is using storage space for no good cause
2) It is still inside the index, bloating them for no reason.
3) It is most likely taking up space on data pages geographically close
to the rest of the data for that record, so it would be quicker if
Firebird could store future updates to that record there.

In SS, there is a dedicated thread that does garbage collection, in CS,
it is whatever transaction happens to stumble across it. Due to the way
Firebird 1.5 and earlier handle the index, large sequences of identical
values in an index can really hamper garbage collection. Firebird 2
will fix this to a great extent. For the moment, you could analyse the
output of gstat which may help identify the problem.

Running a nightly sweep (or even a nightly backup) may help too.

Adam