Subject Re: [firebird-support] Awaiting Garbage Collector
Author Thomas Steinmaurer
> Hello,
> I'll try explain the situation in which we are in the shortest way
> possible...
> We're using a software in our company made by some people in Spain, the
> software supports 70 simultaneous users and we are experiencing slowness
> using the software so I started the investigation, and not later found
> that our database was increasing awaiting garbage transactions every
> second, as after 4 or 5 hours the number of awaiting transactions are up
> to 90.000. I've been reading about Firebird and its retaining legacy
> commit and performance degradation, but no one tells if 10.000 or 20.000
> or even 100.000 awaiting gc transactions could be the reason of the poor
> software performance. I have confirmation from the programmer that the
> component used to connect to firebird is DEVART IBDAC, with the
> AutoCommit flag set to TRUE. We have a Firebird 2.5 Classic Server,and
> also tried with Super Classic server architecture, but no difference, on
> a linux box, plenty of RAM and CPU and disk speed.

Ask your software vendor to fix their client transaction management.
Using auto commit as default transaction management mechanism is
Firebird's enemy number 1. Not only due to OAT not moving forward, you
may also reach the 32-bit transaction id limit, depending on your load,
in weeks/months until a backup/restore cycle is mandatory.

> I'm using Sintatica to monitor the database. Sinatica only tells the
> amount of transactions that are awaiting GC. I would like to know the
> amount of transactions awaiting gc per user or per connection, so I can
> know which client is piling up the most of transactions. Is there a
> system table or another software monitor that could tell me that
> information?

If the software is the only piece accessing the database, usually it
does not gain any benefit to know which user/connection.

With regards,
Thomas Steinmaurer

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.