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

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5

    Big difference in Oldest active and next transaction


    Hi,

    I was reading this manual (PDF):

    http://www.firebirdsql.org/pdfmanual/Firebird-gstat.pdf

    Trying to understand gfix and gstat.

    By executing the following command on my Linux Debian with Firebird Superserver
    version 2.0.4. installed (fbstat is gstat in debian):

    # fbstat -h [database location + name]

    I get the following output:

    Code:
    Database header page information:
            Flags                   0
            Checksum                12345
            Generation              15004646
            Page size               8192
            ODS version             11.0
            Oldest transaction      11020802 (OIT)
            Oldest active           11020803
            Oldest snapshot         11020803 (OST)
            Next transaction        15004637
            Bumped transaction      1
            Sequence number         0
            Next attachment ID      0
            Implementation ID       19
            Shadow count            0
            Page buffers            10001
            Next header page        0
            Database dialect        1
            Creation date           Oct 10, 2011 21:05:10
    
        Variable header data:
            Sweep interval:         20000
            *END*
    So OST - OIT is smaller than the sweep interval, so I assume automatic maintenance is working normal (right?).

    In all examples and my test databases the numbers "Oldest active" and "next transaction" are somewhat the same.

    My Question: Do I have to bother the difference of 4 mln transactions between "Oldest active" and "next transaction".

    It's worries me a bit...

    (BTW.: At Oct 10 I did a full database backup/restore. At that moment I was not aware of other maintenance mechanisms.)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Hi,

    Please read this (source:

    http://www.firebirdsql.org/manual/gstat-example-header.html

    )


    If you discover that the difference between the OAT and the Next Transaction ID seems to be growing larger and larger, something in your database is not committing properly and as such, an increasing number of garbage records may be building up. Eventually, you will see that the database startup times take longer and longer and the performance becomes slower and slower. Check the figures and if a problem is detected, you may be wise to run gfix to manually run a database sweep to clear out the garbage and restore normal working to the database.
    The manual reports also:

    Many web sites, books, manuals (previously including this one) explain that the automatic sweep is activated when OAT - OIT is greater than the sweep interval. This is not the case as explained by Vlad Khorsun, one of the Firebird developers, who explained that it is when OST - OIT is greater than the threshold that the sweep is activated.
    Last Wednesday I was logging this:

    Code:
    Oldest transaction      11020802
     Oldest active           11020803
     Oldest snapshot         11020803
     Next transaction        16768937

    Then Saturday (and in the meantime everyone complains about the performance)

    Code:
     
    Oldest transaction      11020802
    Oldest active           11020803
    Oldest snapshot         11020803
    Next transaction        18867660
    So 2 mln transactions in 4 days...

    But still the manual is not clear, no clear solution is provided.

    Code:
    gfix --sweep


    So automatic Gfix controls my OST-OIT and
    other Gfix operations has to be manual by the database admin?

    I also suppose that the database design is not correct.

    And again: should I be worried about the numbers?
    Last edited by rapgame; November 19th, 2011 at 12:52 PM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    gfix doesn't do anything with your transaction. It just performs automatic sweep. And yes, you should worry. Firebird keeps information about the changes in database between transactions because when you commit/rollback some of them he needs to know what to show - if I can say that. This leads to big memory usage and as you quoted this thing is slowing the performance.
    If you can - use MON$* tables to determine which is the oldest active attachment, from where and what statements it has. Try to use transactions for short time - when you change the data of the database. But maybe the old transactions are from some sort of reports that users keep open for a long time and don't close the transaction/connection to the database.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Thanks for your reply.

    The programmer declares that connections are automatically closed at commit, also that we are not talking about the same thing.

    But when I programming on a database I use always a commit and a close statement.



    Is a transaction ID the same as a connection?

    The connections connects and the transactions could be a SELECT / UPDATE / DELETE / REMOVE, right?

    Lets reread the manual:

    There are two commits - commit and commit retaining. Only the first of these is a hard commit, which,
    when executed renders the transaction as no longer interesting. Commit retaining leaves the transaction as
    still interesting. Some database utilities and/or tools that commit actually perform a commit retaining which
    can leave your database with a lot of still interesting transactions.
    Source: Firebird SQL manual


    So I can imagine the database keeps interesting connections after a transaction. Some automatic inbound and outbound programs can not be closed in our 24/7 situation.

    Still who have to deal with those connections the programmer or the database? In my opinion the programmer but his opinion is: database.

    Numbers count, I rest my case.

    If you have comments on this conclusion please let me know.

    --edit--

    #gfix -sweep

    +

    # services firebird-superservice restart

    And the mentioned transaction numbers are equally again (suppose equal is good).

    I can not use MON$ because I am running Firebird 2.0.4 Superserver (Yes I should update but I have no approval yet).

    Regards

    R.
    Database admin and (junior) software developer
    Last edited by rapgame; November 21st, 2011 at 01:46 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Connections and transactions are different things - you can have one connection to the database and many transactions trough this connection. For Firebird the transactions are the interesting thing, not the connections. Commit Retaining keeps the transaction active - it just commit the data in the database so other transactions to see the changes but the transaction is still active and you can continue to work with it and it still interesting for Firebird. Commit/rollback closes the transaction and it is no longer active.

    Maybe your programmer closes the connection on commit, but when he does this commit? How long he keeps the transaction active? 4,5 days?!?!

    You can have an active connection for days this is not a problem, but the transaction that use this connection especially for changing data have to be with short live. We have an application that receive GPS coordinates from many trackers and writes them in a database - this server have an active connection 17 days, but the interval between OIT and next transaction is less than 5000 - we have some apps that use the data from the database but we are trying to close their transactions ASAP. Also we use Read Only transactions when there is no need of changing data - in this case Firebird uses less resources for those transactions.

    So I thing that your programmer have to see what parts from the program are keeping active transactions and why.

    The restarting of firebird closes all connections and the transactions - it's normal to be equal after that. But after few days work I guess that the numbers are going to be with big difference again. There is something wrong in the applications that use the database.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Thank you mIRCata!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by rapgame
    Thank you mIRCata!
    Did you find the problem? I'm curious what happened. Very often it is something very small that makes big problems
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Hi mIRCata,

    I would like to describe our ERP application like a software application that fullfills the needs of a regular company.

    On avarage 10-20 people are logged in and performing regular tasks like search a record and update it.

    The current statistics:

    Code:
            Oldest transaction      20670542
            Oldest active           20670543
            Oldest snapshot         20670543
            Next transaction        29976092
    Manual sweeps are not working.
    I do not want to restart my database.
    The senior programmer stated: "this are just ID's and is not involving performance issues. I do not know how to improve this."

    I was just happy that somebody agrees with me that these statistics should worry us. So far, thank you.

    I am arguing the software design because:

    Just only opening one record creates ~40 transactions on the database.

    My simulations are not resulting in the same statistics.

    I agree with serveral sources that stated:

    Sadly to say, these situations happen. And again, example:
    Some system installed for ~15 users. Periodically performance is so bad, that DBA needs to restart server. After server restart everything work fine for some time, then performance gets bad again. Statistics showed that average daily transactions is about 75,000, and there are active transactions running from the start of day to the moment when performance getting down.
    Unfortunately, applications were written with BDE and with no transactions using at all; i.e. all transaction handling was automatic and used by BDE itself. This caused some transactions to stay active for a long time, and garbage (record versions) accumulated until DBA restarted server. After restart automatic sweep run, and garbage became collected (eliminated).
    All these was caused by applications, because they were tested only with 2-3 concurrent users, and when they became ~15, applications started to make very high load.
    Need to say that in that configuration 70% of users were only reading data, and other 30% were inserting and updating some (!) data.
    In this situation the only thing that can make performance

    better is to redesign applications completely.
    http://www.ib-aid.com/articles/item65
    http://www.firebirdsql.org/manual/gstat.html

    I say: "oke, lets redesign"

    The programmer: "I do not like what you are saying. You are impling that I can't write good code. I do not want to rewrite my code. Therefore I think it is not true what you are saying and I do not want to share more information about transaction time etc."

    I am asking kindly every programmer to be a software developer. Work together on code, design, unittests and documentation. Be open and lets try to be a team.

    R.
    Last edited by rapgame; December 14th, 2011 at 11:53 AM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    You have a long-running transaction here or you are using Commit Retaining without doing regular "hard" commits. Also, if you are in some kind of AutoCommit mode, this might use Commit Retaining behind the scene.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by rapgame
    Manual sweeps are not working.
    It doesn't clear active transactions - sweep cleans old record versions so the data pages they use to be used again. It doesn't do anything with the transactions or the real data in the database.

    Originally Posted by rapgame
    The programmer: "I do not like what you are saying. You are impling that I can't write good code. I do not want to rewrite my code. Therefore I think it is not true what you are saying and I do not want to share more information about transaction time etc."
    R.
    What he likes or not, is not the thing that makes something right or wrong. From the statistics you showed it's clear that there is transactions that stay active for days (the example with OAT 11020803 and Next Tr. 18867660)!!!

    In the beginning when we've started to use Firebird we had similar problems, we've read, we've searched for solutions and after that we redesigned our software.

    For example our GPS database that works on our servers at the moment OAT is 52564891, Next Tr is 52564893 + 10..15 and we have 33 users that use it and there are over 300+ devices sending data in a minute. The DB size is over 48GB.

    So your programmer maybe writes good code, but he can't work with the database.

    Here I'll post some quotes from Helen Borrie's "The Firebird Book - A Reference For Database Developers"
    “Interesting Transactions”
    Server and client transaction accounting routines use TIDs to track the states of
    transactions. “Housekeeping” routines take the age of transactions into account when
    deciding which old record versions are “interesting” and which are not. Uninteresting
    transactions can be flagged for removal. The server remains “interested in” every trans-
    action that has not been hard-committed by a COMMIT statement.
    Active, limbo, rolled back, and “dead” transactions are all interesting. Trans-
    actionsthat have been committed using COMMIT WITH RETAIN (aka soft commit or
    CommitRetaining) remain active until they are hard-committed and are thus interest-
    ing. Conditions can develop in which interesting transactions become “stuck” and
    inhibit performance.
    If neglected, stuck transactions will become the source of serious performance
    degradation.
    A stuck OIT will cause the number of transaction inventory pages to
    grow. The server maintains a bitmapped working table of the transactions stored in
    the TIPs. The table is copied and written to the database at the start of each transac-
    tion. As it becomes bloated by stuck transactions, it uses progressively more memory
    resources and memory becomes fragmented from constant reallocation of the
    resources.
    Oldest Interesting Transaction
    The OIT is the lowest numbered transaction in the TIPs that is in a non-committed
    state.
    Oldest Active Transaction
    The OAT is the lowest numbered transaction in the TIPs that is active. A transaction is
    active as long as it is not hard-committed, not rolled back, and not in limbo.
    Last edited by mIRCata; December 19th, 2011 at 05:21 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    In the meantime....

    Oldest transaction 20670542
    Oldest active 20670543
    Oldest snapshot 20670543
    Next transaction 32477647

    I am considering to reboot the Firebird database...

    If performance returns and numbers corrected than performance is correlated to this...

    The programmer is not aware of the difference in hard commit and commit retaining... in his perception: commit is a commit, i dont have to deal with retaining....



    But thanks for this nice feedback.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    If the programmer doesn't know the difference between hard/soft commits, then the programmer really needs to get used to Firebird concepts. Believe me, if you don't fix your client transaction management, then you won't fix your performance problem.

    Comments on this post

    • mIRCata agrees
    • mariuz agrees
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Using IBexpert we where able to produce an overview of thousands maybe millions of users currently logged in.

    So finally I restarted the Firebird server.

    Difference between oldest active and next transaction changed to 1 instead of ±12 million...

    After 30 minutes users noticed return of normal performance.

    The programmer: "...this is because all tables are re-indexed..."

    But IBanalyst keeps indicating problems with indexes... Firebird is reindexing but the programmer has to state what columns are indexed right?

    I think performance was stuck because of the discussed abnormal difference in number of active transactions. To many transactions like the Firebird manual describes.

    I was reading more about Firebird, about atomicity, consistency, durability, isolation and more. A database is not just a program that writes data to disk, its way more than that. Very interesting.

    But still without sourcecode it is hard to find out what is going wrong. I still hope the programmer will agree with me eventually and lets work together on a better design.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Weekly restart of Firebird is not a practical solution on 24/7 production servers.

    Does anyone know how to terminate open transactions without restarting Firebird?

    Actually the programmer should write code that will close transactions after commit.

    In python:

    cursor.close()

    How to do this in Delphi? ( So I can tell the programmer)...

    This resource implies a problem in Firebird that will be solved in Firebird 3....

    http://www.firebirdfaq.org/faq312/

    So I have to wait till the end of the world to solve these performance problems...

    Sorry, I don't like this.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by rapgame
    The programmer: "...this is because all tables are re-indexed..."
    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.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo