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 our country, the software now 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, and we also need the GSD32.dll library to make the software to work. 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.
What I would like to know if there is a way to configure IBDAC to avoid the retaining method so I could ask the programmer to change the component behaviour and test. As you can imagine at this point, the programmer is not helping much on solving this problem.
Thank you!!
Awaiting GC transacctions
Re: Awaiting GC transacctions
Performance loss can be due to transactions started in the client application, that remain active for a long time. As a result, the Firebird Garbage Collector can't collect garbage effectively in the database.
The solution depends on the transaction management in your application. We can recommend several common approaches:
1. To commit transactions, that are explicitly started in the application using the StartTransaction method, use the Commit and Rollback methods instead of CommitRetaining and RollbackRetaining.
2. Set the AutoCommit property to False for TIBCConnection and datasets. The AutoCommit property is True by default, which leads to automatic CommitRetaining or RollbackRetaining on any data modification. By setting the AutoCommit property to False, you will avoid such behavior, however, you will have to manage transactions manually.
The solution depends on the transaction management in your application. We can recommend several common approaches:
1. To commit transactions, that are explicitly started in the application using the StartTransaction method, use the Commit and Rollback methods instead of CommitRetaining and RollbackRetaining.
2. Set the AutoCommit property to False for TIBCConnection and datasets. The AutoCommit property is True by default, which leads to automatic CommitRetaining or RollbackRetaining on any data modification. By setting the AutoCommit property to False, you will avoid such behavior, however, you will have to manage transactions manually.
Re: Awaiting GC transacctions
Thank you Viktor for your reply. I will send your suggestions to the software programmer.
Thanks!
Thanks!
Re: Awaiting GC transacctions
Feel free to contact us if you have any further questions about IBDAC.
Re: Awaiting GC transacctions
Another question...Do you know if it's possible to get the awaiting gc transactions per user connection or attachment. This would be very useful in our scenario as we have several software application connecting to the same database.
Thanks.
Thanks.
Re: Awaiting GC transacctions
This question is not related to IBDAC functionality, it is related to Firebird functionality. You can get the needed data using requests to Firebird system tables. See more details in the Firebird documentation.