Commons Dbcp
  1. Commons Dbcp
  2. DBCP-156

[dbcp] Specifying the maximum lifetime of a connection

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.1
    • Fix Version/s: 2.0
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

      Description

      It would be excellent if BasicDataSource had a "maxLifetime" property, for
      specifying the maximum lifetime of a connection in the pool, no matter how long
      it's been active or idle, and no matter whether it still seems to be alive.
      Commons DBCP does not support this yet; it just offers various connection
      validation means, which is not the same.

      This is particularly relevant for MySQL Connector/J 3.0.x which tries to apply
      some weird automatic recovery when a MySQL connection has timed out, resulting
      in all sorts of issues. The best way to deal with this is to set the maximum
      lifetime of a connection to 4 hours or the like: If it's older, simply kill it,
      even if it's still alive at that point of time.

      Note that Proxool (http://proxool.sourceforge.net) does have such a property,
      namely "maximum-connection-lifetime". Resin's connection pool has a similar
      property named "max-pool-time" (http://www.caucho.com/resin-2.1/ref/db-
      config.xtp). I tend to prefer Commons DBCP, but the lack of such a property
      forces me to look for alternative pools.

      Juergen

        Activity

        Hide
        Mark Thomas added a comment -

        Better (very) late than never, this feature has been added to DBCP trunk for inclusion in DBCP 2.0 onwards.

        Show
        Mark Thomas added a comment - Better (very) late than never, this feature has been added to DBCP trunk for inclusion in DBCP 2.0 onwards.
        Hide
        Garick Hamlin added a comment -

        I am completely new to dbcp, so perhaps I am barking up the wrong tree here, but this feature is exactly what I was just now looking for in the documentation.

        BTW, I wanted this for a postgres setup, not mysql so the feature is generic.

        There are good reasons to have a maximum connection life.
        + It is handy for graceful downtime free maintenance by allowing tomcat tcp sessions to be migrated one at a time
        + It help deals with things that shouldn't happen like memory leaks in a database backend. This is not a problem
        I am currently experiencing, but it a good practice to have a maximium lifetime in case a leak was introduced this
        would make it non-critical to operations.

        A connect costs a few milliseconds. Having a lifetime of 10 seconds would be a huge benefit for failover. A connect costs a milliseconds, re-prepares are often cheap and may actually improve performance for me on this app.

        Basically the high level design was looking like this:
        [ App ] -> [ DBCP ] -> [ pgbouncer ] -> [ postgres cluster ]

        (I could eliminate pgbouncer here and replace it with stateful NAT but by having pgbouncer there I can have pgbouncer allow connect / logins to succeed and cause things to merely block briefy during switchover so the change is more transparent.)

        So, I think this is useful.

        If dbcp had it ... I would probably use dbcp. I may or may not use it as it stands now..

        Show
        Garick Hamlin added a comment - I am completely new to dbcp, so perhaps I am barking up the wrong tree here, but this feature is exactly what I was just now looking for in the documentation. BTW, I wanted this for a postgres setup, not mysql so the feature is generic. There are good reasons to have a maximum connection life. + It is handy for graceful downtime free maintenance by allowing tomcat tcp sessions to be migrated one at a time + It help deals with things that shouldn't happen like memory leaks in a database backend. This is not a problem I am currently experiencing, but it a good practice to have a maximium lifetime in case a leak was introduced this would make it non-critical to operations. A connect costs a few milliseconds. Having a lifetime of 10 seconds would be a huge benefit for failover. A connect costs a milliseconds, re-prepares are often cheap and may actually improve performance for me on this app. Basically the high level design was looking like this: [ App ] -> [ DBCP ] -> [ pgbouncer ] -> [ postgres cluster ] (I could eliminate pgbouncer here and replace it with stateful NAT but by having pgbouncer there I can have pgbouncer allow connect / logins to succeed and cause things to merely block briefy during switchover so the change is more transparent.) So, I think this is useful. If dbcp had it ... I would probably use dbcp. I may or may not use it as it stands now..
        Hide
        Mark Thomas added a comment -

        Commons-pool does not record the creation time of objects. Adding this would be non-trivial as currently pool does not keep tabs on all the objects it creates. It tracks idle objects but only keeps a count of active objects. Implementing this would require changing pool to keep tabs on all objects.

        I'm tempted to close this as won't fix. The underlying premise - that long lived database connections are bad - doesn't seem right. I agree long lived idle connections are bad and DBCP has ways to handle those. I don't see what is inherently wrong with long lived active connections.

        Pushing this to 2.0 as if this is implemented, I don't see it happening any earlier due to the extent of the changes required to pool.

        Show
        Mark Thomas added a comment - Commons-pool does not record the creation time of objects. Adding this would be non-trivial as currently pool does not keep tabs on all the objects it creates. It tracks idle objects but only keeps a count of active objects. Implementing this would require changing pool to keep tabs on all objects. I'm tempted to close this as won't fix. The underlying premise - that long lived database connections are bad - doesn't seem right. I agree long lived idle connections are bad and DBCP has ways to handle those. I don't see what is inherently wrong with long lived active connections. Pushing this to 2.0 as if this is implemented, I don't see it happening any earlier due to the extent of the changes required to pool.
        Hide
        Mark Matthews added a comment -

        [snip]
        > This is particularly relevant for MySQL Connector/J 3.0.x which tries to
        apply
        > some weird automatic recovery when a MySQL connection has timed out,
        resulting
        > in all sorts of issues.

        Juergen,

        By default Connector/J doesn't do any "automatic recovery" because of the
        issues you speak of. Unfortunately just about every FAQ and third-party
        component has decided to latch on to using autoReconnect=true instead of
        dealing with the real issue of handling stale connections, which is the same
        for any JDBC vendor (i.e. either have the pool test connections before
        handing them out and take the performance hit, or do the "right" thing and
        actually handle SQLExceptions, because that's why they exist).

        > The best way to deal with this is to set the maximum
        > lifetime of a connection to 4 hours or the like: If it's older, simply kill

        Most people running MySQL in heavy-load production set it to a few seconds,
        because it only takes a few ms to create a connection, but an idle connection
        takes resources that could be used by some other active process.

        Unfortunately, as you note, DBCP has no straightforward way to enforce a max-
        connection lifetime or idle period, so you have to use maxIdle connections as
        a "crutch" of sorts (other pools such as Proxool (as you mention) or C3P0 have
        these features).

        Show
        Mark Matthews added a comment - [snip] > This is particularly relevant for MySQL Connector/J 3.0.x which tries to apply > some weird automatic recovery when a MySQL connection has timed out, resulting > in all sorts of issues. Juergen, By default Connector/J doesn't do any "automatic recovery" because of the issues you speak of. Unfortunately just about every FAQ and third-party component has decided to latch on to using autoReconnect=true instead of dealing with the real issue of handling stale connections, which is the same for any JDBC vendor (i.e. either have the pool test connections before handing them out and take the performance hit, or do the "right" thing and actually handle SQLExceptions, because that's why they exist). > The best way to deal with this is to set the maximum > lifetime of a connection to 4 hours or the like: If it's older, simply kill Most people running MySQL in heavy-load production set it to a few seconds , because it only takes a few ms to create a connection, but an idle connection takes resources that could be used by some other active process. Unfortunately, as you note, DBCP has no straightforward way to enforce a max- connection lifetime or idle period, so you have to use maxIdle connections as a "crutch" of sorts (other pools such as Proxool (as you mention) or C3P0 have these features).
        Hide
        Juergen Hoeller added a comment -

        I actually looked into DBCP's implementation to see where such a check could
        go. Unfortunately, it seems that this requires changes to both Commons Pool and
        Commons DBCP...

        However, for someone who actually knows Commons Pool and Commons DBCP
        internals, this should be straightforward to add.

        Juergen

        Show
        Juergen Hoeller added a comment - I actually looked into DBCP's implementation to see where such a check could go. Unfortunately, it seems that this requires changes to both Commons Pool and Commons DBCP... However, for someone who actually knows Commons Pool and Commons DBCP internals, this should be straightforward to add. Juergen
        Hide
        Yoav Shapira added a comment -

        If you suggest a code patch for this, preferably accompanied by a test case,
        then this issue will get more and quicker attention.

        Show
        Yoav Shapira added a comment - If you suggest a code patch for this, preferably accompanied by a test case, then this issue will get more and quicker attention.

          People

          • Assignee:
            Unassigned
            Reporter:
            Juergen Hoeller
          • Votes:
            4 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development