Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Originally Posted by mIRCata
    He is an idiot!

    I repeat:

    He is an idiot with very little knowledge about Firebird. (I hope that I'm not the real idiot here talking like that)

    Restarting servers coses all connections and transactions. That is why your OAT and next tr. are with close numbers. Because all open connections and active transactions your software kept are force closed when you stoped the server.

    I don't know is there such thing as re-indexation in Firebird, but I know about index statistic and index selectivity. Even if that is what your programmer mean by "re-indexed" it's still wrong.
    For every one index Firebird keeps information that shows how unique are values in the index and it uses to do queries' plan, But! These statistics are calculated only when the index is created (manually with DDL or on database restore) and if you manually use SET STATISTIC INDEX. Not on stopping/starting the server.

    So I'm still saying that there is big problem in your software code about the work with the database and the transactions.
    I agree with this conclusion. The Firebird manual/references are saying nothing about reindexing processes during restart.

    Other possible suggestions of the programmer:
    - Maybe it's the driver.

    - Maybe it's a memory leak.
    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.
  2. #17
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    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.
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    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.
  6. #19
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    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.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    24
    Rep Power
    0
    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.
  10. #21
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    Is the Oldest Active Transaction way behind as well?
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    24
    Rep Power
    0
    Originally Posted by tsteinmaurer
    Is the Oldest Active Transaction way behind as well?
    In my case the OIT, OAT and OST are all way behind the next transaction id.
  14. #23
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    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.
  16. #24
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    @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.
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Posts
    24
    Rep Power
    0

    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.
  20. #26
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    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.
  22. #27
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    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.
  24. #28
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    I'm located in Austria and offer commercial Firebird services. If you are interested, send an email to: ts AT iblogmanager DOT com
  26. #29
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    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.

    R.

    Comments on this post

    • mariuz agrees
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    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.
    That's exactly the intention of the Trace API and FB TraceManager. Glad that you find it useful.

    Comments on this post

    • mariuz agrees
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo