Loading ...
Sorry, an error occurred while loading the content.

Re: [firebird-support] OIT / NT

Expand Messages
  • Michael Ludwig
    Tiberiu, consider the comments on COMMIT RETAINING in the quotes from Helen s and Ann s messages below - maybe there s one place where you re running with the
    Message 1 of 62 , Mar 31, 2012
    • 0 Attachment

      consider the comments on COMMIT RETAINING in the quotes from Helen's and
      Ann's messages below - maybe there's one place where you're running with
      the default and it's only triggered on the installation you're seeing
      the problem on.


      Tiberiu Horvath schrieb am 31.03.2012 um 10:03 (+0300):
      > My BIG concern is that my program (Delphi XE with good old IBX
      > components) run with the exactly same release, on 4 different clients
      > generating diferent results.

      FB performance - Helen Borrie 06.01.06

      Then look closely at how transactions are being committed. The IBX
      default for AutoCommit is to use CommitRetaining. This is handy for
      Delphi client apps and disastrous for the server. It causes garbage to
      build up steadily. You are seeing better performance on a clean
      database and degrading performance as garbage builds up and doesn't
      get cleared.

      > 3 of these are in normal parameters (while working, the gap between
      > OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec)
      > but when that particular client (same program, same FB client, same
      > OS, same ISP) starts my program, the mentioned values increase in an
      > alarming rate of 100...200 / 5 sec, so that at the end of a normal
      > working day, the gap is arround 1.5 mil transactions. People using the
      > program at this company complain about the slowdown in the afternoon
      > compared with the morning.

      > From: Ann Harrison
      > Sent: Friday, March 30, 2012 7:07 PM

      > OK, time for my lecture on OAT and OIT.

      > The OAT is the oldest transaction that the system considers to be
      > active. It blocks garbage collection and induces database bloat.
      > Transactions that commit using "commit retaining" do not advance the
      > OAT. Transactions that are left open for hours - even transactions
      > that have not changed the database - leave the OAT stuck. Once the OAT
      > is stuck, Firebird must keep old versions of records that transaction
      > might read if it ever wakes up and starts working again.
    • Mark Rotteveel
      ... ;) ... If an application developer should ever be concerned with it, he should isolate that concern to a separate layer. Preferably by implementing a
      Message 62 of 62 , Apr 7, 2012
      • 0 Attachment
        On 2-4-2012 8:58, Norman Dunbar wrote:
        >> I disagree, the application (or application developer) should do that
        >> (preparing statements every time it needs it). It is the driver and/or
        >> connection pool that should keep a pool of prepared statements (see
        >> section 11.6 of JDBC 4.1) for reuse.
        > I disagree with your disagreement! ;-)


        > I think the developer should know exactly what the application is doing,
        > otherwise the developer doesn't know what they should be coding for or
        > protecting against surely? Especially when the cache isn't caching, for
        > some reason?

        If an application developer should ever be concerned with it, he should
        isolate that concern to a separate layer. Preferably by implementing a
        proxying JDBC datasource (or use one of a thirdy party) which allows for
        that pooling/caching. He should not have code all around his application
        to make that caching work.

        >> It also duplicates what
        >> connection pools (including statement pools) could be doing for you.
        > Not really. Oracle natively supplies connection pools and cursor caching
        > for you, if you know how to use them. And this brings me back to my
        > initial point above, the developer *should* know how to use them.

        As a developer, I would expect the Oracle JDBC driver or UCP driver to
        take care of most of those details for me (and it does, eg see
        My concern as a developer is getting the application to work and add
        features, not getting muddled in the intricacies of the database(s) I use.

        I am exaggerating, but my point is to have a separation of concerns: my
        application code should not be concerned with this, doing that will
        complicate my code, make it harder to debug and couple it tightly to a
        specific database/driver implementation.

        > I mean, take the example linked to above. What happens when this
        > application is moved to a system that doesn't have the external
        > connection pooling?

        This pooling is either part of the application itself (by way of the
        driver and its config) or of the application server running the
        application. The driver taking care of the pooling would either be part
        of the application, or be configured on the system according to the
        deployment instructions of the application.

        > On an Oracle system, you get lots of parsing, granted most of it will be
        > soft parsing which isn't as resource intensive as hard parsing, but
        > regardless, each parse takes a latch (two actually) and there is a queue
        > of other statements, needing to be parsed waiting, for all these
        > unnecessary parses. Performance suffers.
        > Do it (what I call) right - prepare the statements as few times as
        > possible (ie once) and use it as many times as possible, and you save
        > resources, improve performance and make the user's life a whole lot better.

        Most Java applications work like this:

        1) Open connection from datasource (usually: obtain logical connection
        from connection pool behind that datasource)
        2) Prepare statement
        3) Set parameters
        4) execute
        5) (optional) traverse resultset
        6) close statement
        7) repeat steps 3-5 or 2-6
        8) Close connection (usually: close logical, physical is returned to pool)

        The lifetime of steps 1-8 is usually very short (eg one request/response
        cycle in a web application).

        In this situation a developer cannot easily keep track of the
        connection, and he shouldn't as he only 'owns' the connection for the
        duration of steps 1-8!
        For all intents and purposes to the application developer it will be as
        if he retrieves a new connection every time (and depending on the
        configuration and implementation class of the datasource it could be)!
        So for the application developer there is no way to track statements
        over multiple invocations of steps 1 to 8.
        That is where statement pooling comes in. If statement pooling is
        available/enabled, step 2 will check the statement pool of the physical
        connection and retrieve the prepared statement if available (and
        otherwise prepare a new one), while step 6 will return the statement to
        the statement pool.

        > Agree to disagree?

        No problem.

        Mark Rotteveel
      Your message has been successfully submitted and would be delivered to recipients shortly.