December 27th, 2011, 03:57 PM
I agree with this conclusion. The Firebird manual/references are saying nothing about reindexing processes during restart.
Originally Posted by mIRCata
Other possible suggestions of the programmer:
In case the driver is the problem, the programmer has to deal with this problem. Certainly when it becomes to memory leaks, mather of good code. Firebird is not related to this performance problem. Next step: imporving the code.
Thank you mIRCata for sharing your opinion.
February 16th, 2012, 12:29 AM
We migrated the Firebird server to brand new (superfast) hardware.
Instead of Linux we are using Windows server 2008.
This because a developer said that he tested it and that the transaction numbers looking good. He is not a good tester because he is only starting the application with one single user.
Also upgraded from 2.0.4 to latest 2.5 release.
Performance is crap, difference between Oldest en next transaction are increasing by 600.000 on avarage a day.
Today I will return to the programmers to tell them...
Any comments on this post are most welcome.
February 16th, 2012, 02:32 AM
In my company we've made some tests with Windows Server 2008 and Linux (CentOS) Backup/restore circle was about 50% slower under Windows OS, and we had to restart Firebird once every few days.
The OS DOESN'T matter how your applications work with the transactions. It's not the OS that keeps the transactions alive, it's the applications that use the database. The OS matters in other ways but it's NOT responsible about transactions and their work.
About the OS - there was a presentation from Firebird's developers (I can't find the link) where they suggest to use 64 bit Linus distribution and if it's possible and if you have the RAM to support the connections to use Classic server.
If you are using Super server on Windows - set CpuAffinityMask in firebird.conf - because there is a problem that can drop down the performance.
Now, after you are running 2.5 it's easier to catch the problems.
You can use the MON$* tables to see your active connections - from where, from how long they are active, what transactions are running and what queries. So this can help you to point the problems and to find what part of the applications need to be fix.
Last edited by mIRCata; February 16th, 2012 at 02:37 AM.
February 16th, 2012, 02:42 AM
Check out FB TraceManager V2.5 (http://www.upscene.com/go/?go=fbtm ) to identify possible bottlenecks. Beside support for the Firebird 2.5 Trace API, FB TraceManager V2.5 now also integrates the Firebird monitoring (MON$) tables. You can also monitor the database header page to e.g. watch transaction counter gaps etc.
March 9th, 2012, 07:37 AM
I also have the same problem with my database. The gap between the next transaction id and the oldest transaction id grows bigger and bigger, at a rate of about 1 million transactions per day.
I have associated this with a synchronisation of data between this database and another database. Every 20mins this sync runs and updates around 40,000 records in my firebird database.
Sinatica monitor shows that a lot of transactions are building up for garbage collection, but never get cleared out, even if I run an manual sweep.
I was just wondering if you were performing some action that was updating large amounts of records in a similar way?
btw I have not found a fix for my problem either.
March 9th, 2012, 07:53 AM
Is the Oldest Active Transaction way behind as well?
March 9th, 2012, 08:51 AM
In my case the OIT, OAT and OST are all way behind the next transaction id.
Originally Posted by tsteinmaurer
March 9th, 2012, 09:41 AM
If you can use the MON$* tables see from where is the oldest transaction. And from the attachment ID you can find what application started it and what statements are running in this transaction. So this can point you where to search for problems.
March 10th, 2012, 01:09 PM
@srayner: Then you have a long-running active transaction. As suggested, use the MON$TRANSACTIONS table to spot the transaction and statements running in context of this transaction.
Btw: FB TraceManager V2.5, beside the Trace API, also has support (integrates) for header page monitoring, database statistics monitoring and MON$ tables integration. This entire tool set might help you to detect and diagnose the problem and possibly gives you a clue if there is some kind of transaction management problem in your application.
March 12th, 2012, 11:11 AM
long running transactions
I beleive the long running transactions are because users are perfoming a query with the data displayed on screen, then leaving the application running.
The old transactions are not that old, only a couple of working days. The big difference between oldest transaction and next transaction is because the fast rate at which new transactions are occuring (1million per day).
I'm wondering should i focus my efforts reducing the large number of new transaction, or upon closing the old transactions?
If after performing a query i issue a commit, will i stll retain the data onscreen or will the connection to data be closed? If it is of any interest I am using Delphi for the client application.
March 13th, 2012, 02:44 AM
Try to reduce the number of old transactions. Firebird works in that way that these transactions are leading to performance drop.
I'm also using Delphi and I use one memory table component to load the data from the queries. After that I commit the query transaction. I'm trying to keep transactions open only when I need to load data from the database or when I'm doing update/insert or delete. After that I close the transactions.
I've witnessed how after stopping one app Firebird freed over 700 mb of ram. That was after 2 days work with that app.
Last edited by mIRCata; March 13th, 2012 at 02:47 AM.
Hi srayer and mIRCata,
I am using Delphi too.
Question: You posted "I'm trying to keep transactions open only when I need to load data from the database or when I'm doing update/insert or delete."
So actually this means every kind of database user interaction, right? I mean UPDATE, SELECT, DELETE, SELECT.
Also 700 MB Ram is al lot, but in these times acceptable.
RAM is not expensive any more. A server should have as much as possible.
I like this discussion but the problem is not solved yet unfortunately. Anybody have a suggestion where I can find a Firebird Expert in West-Europe? Thanks.
I'm located in Austria and offer commercial Firebird services. If you are interested, send an email to: ts AT iblogmanager DOT com
July 14th, 2012, 06:55 AM
Hi Thomas (and other readers),
Thanks for you tip concerning: FB TraceManager V2.5 (http://www.upscene.com/go/?go=fbtm )
This seems to be the key.
We are now able to look in to the behaviour of our application and the interaction with Firebird. This interaction was a dark not understand part in our development. TraceManager puts some light on this behaviour.
Like you and others told us before, commit retaining keeps the transaction active. Every transaction should be closed gracefully.
Thanks a lot and I would like to advice other people to read this whole thread and use TraceManager.
Comments on this post
July 16th, 2012, 03:21 AM
That's exactly the intention of the Trace API and FB TraceManager. Glad that you find it useful.
Comments on this post