[firebird-support] Awaiting Garbage Collector
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. 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? Thank you!!
Re: [firebird-support] Awaiting Garbage Collector
> 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 http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Awaiting Garbage Collector
At 08:09 a.m. 19/03/2015, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] wrote: >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. It is not AutoCommit, per se, that prevents the OAT from moving forward, but another transaction setting, COMMIT WITH RETAIN. Assuming your software is written with Delphi or a derivative, the setting is called CommitRetaining. The hangup can be cleared if the software also periodically performs a hard COMMIT on the offending set. This isn't something you can fix yourself, if you don't have access to the source of the client code. Thomas' comments stand true, regarding AutoCommit eating up the transaction count. There may be another flaw in the transaction management: if a set is being used for display and select but not for writing - such as in a drill-down scenario or a lookup - that runs throughout the user session in a read/write transaction with ReadCommitted visibility, then this will hold the OAT back as well. When such sets run in a read-only transaction, they do not cause the OAT to get stuck. Again, this is for the software vendor to fix. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of "The Firebird Book" and "The Firebird Book Second Edition" http://www.firebird-books.net __
Re: [firebird-support] Awaiting Garbage Collector
> At 08:09 a.m. 19/03/2015, Thomas Steinmaurer t...@iblogmanager.com > [firebird-support] wrote: > >>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. > > It is not AutoCommit, per se, that prevents the OAT from moving forward, but > another transaction setting, COMMIT WITH RETAIN. Yep. What I wanted to say is that AutoCommit of access components internally usually implies using commit retaining. Didn't make that clear enough in my reply. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > Assuming your software is > written with Delphi or a derivative, the setting is called CommitRetaining. > The hangup can be cleared if the software also periodically performs a hard > COMMIT on the offending set. This isn't something you can fix yourself, if > you > don't have access to the source of the client code. Thomas' comments stand > true, regarding AutoCommit eating up the transaction count. > > There may be another flaw in the transaction management: if a set is being > used > for display and select but not for writing - such as in a drill-down scenario > or a lookup - that runs throughout the user session in a read/write > transaction with ReadCommitted visibility, then this will hold the OAT back as > well. When such sets run in a read-only transaction, they do not cause the > OAT > to get stuck. Again, this is for the software vendor to fix. > > > Helen Borrie, Support Consultant, IBPhoenix (Pacific) > Author of "The Firebird Book" and "The Firebird Book Second Edition" > http://www.firebird-books.net > __ > > > > > Posted by: Helen Borrie > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
