8 Replies Latest reply on Mar 15, 2004 9:11 AM by Adrian Brock

    Problem With Connection Pooling Under Load

    F Zemen Newbie

       

      "jbossuser" wrote:
      "jbossuser" wrote:
      We are having serious connection pooling issues under load. At first, we believed it was our problem, but we have done sufficient load testing to suspect the connection pool itself. Here are the relevant points:

      JBoss 3.2.3 over Oracle 8i using any of the thin drivers (tried all of them) on Solaris.
      Multiple connection pools (printout below).
      Apache to Tomcat JK connection pooling.
      We use CMP but no CMR. We also have DAO that share the connection pools with CMP. We have confirmed proper closing of resources in DAO, we also have no nested querying or other such problems that might confuse resources.

      Symptoms: Protocol Violations (yes, I know, this is a catch all), Bigger type length than Maximum and Refcursor value is invalid exceptions (examples below).

      Observations:

      1. Occurs when a cluster node is heavily loaded, generally when it exceeds 50% of the app. server's cpu capability. We have little or no throttling, except for the infamous jk connectionTimeout that holds on to sockets. If we lower that value or increase max processors, the problem manifests itself sooner than later in the load test. This occurs over a variety of database configurations and environments.

      2. Initially connection pools are moderately used, with a large number of connections being used concurrently. Eventually (a few minutes), only a few connections are used concurrently. At the same time, we start getting the JDBC problems.

      3. Our hunch is that one thread is closing JDBC resources on another thread by sharing the same connection, or that there are stale connections.

      4. As a test, in one area it was happening we created our own connection each time from the driver manager and that works fine. Not a solution but shows that at least the basics work.

      Please help?/Suggestions?

      Sample exceptions:

      15:50:45,194 ERROR [BusinessPartnerBean] Failed to execute SQL query SELECT DISTINCT BP.BUS_PRTNR_ID, BP.NAME, BP.STATUS_LOOKUP_ID FROM BUSPRTNR.BUS_PRTNR BP, BUSPRTNR.BUS_ROLE BR, BUSPRTNR.BUS_PRTNR_ROLE BPR WHERE BP.BUS_PRTNR_ID = BPR.BUS_PRTNR_ID AND BPR.BUS_ROLE_ID = BR.BUS_ROLE_ID AND UPPER(BR.NAME) = ? ORDER BY UPPER(BP.NAME)
      java.sql.SQLException: Protocol violation
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:550)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1819)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2015)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:395)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:339)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
      .....


      Caused by: java.sql.SQLException: Bigger type length than Maximum
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
      at oracle.jdbc.ttc7.MAREngine.buffer2Value(MAREngine.java:1995)
      at oracle.jdbc.ttc7.MAREngine.unmarshalUB4(MAREngine.java:857)
      at oracle.jdbc.ttc7.TTIoer.unmarshal(TTIoer.java:113)
      at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:115)
      at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:466)
      at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:441)
      at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:460)
      at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:185)
      at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:168)
      at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:614)
      at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:492)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:353)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:211)
      ......


      Caused by: java.sql.SQLException: Refcursor value is invalid
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
      at oracle.jdbc.ttc7.TTCStatement.(TTCStatement.java:41)
      at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:469)
      at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:441)
      at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:460)
      at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:185)
      at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:168)
      at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:614)
      at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:492)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:353)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:211)


      Our oracle data source is:

      <?xml version="1.0" encoding="UTF-8"?>

      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- ===================================================================== -->

      <!-- $Id: oracle-xa-ds.xml,v 1.1.2.9 2003/04/04 17:17:26 igorfie Exp $ -->

      <!-- ===================================================================== -->
      <!-- ATTENTION: DO NOT FORGET TO SET Pad=true IN transaction-service.xml -->
      <!-- ===================================================================== -->



      <local-tx-datasource>
      <jndi-name>ArgoUtlDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>argoutl</user-name>
      argoutl
      <min-pool-size>0</min-pool-size>
      <max-pool-size>40</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>BusPrtnrDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>busprtnr</user-name>
      bus
      <min-pool-size>0</min-pool-size>
      <max-pool-size>30</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>CampaignDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>campaign</user-name>
      campaign
      <min-pool-size>0</min-pool-size>
      <max-pool-size>15</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>DeployDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>system</user-name>
      argo1
      <min-pool-size>0</min-pool-size>
      <max-pool-size>1</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>DupIdentificationDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>dupuser</user-name>
      dupuser
      <min-pool-size>0</min-pool-size>
      <max-pool-size>15</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>MailBatchDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>mailbatch</user-name>
      mail
      <min-pool-size>0</min-pool-size>
      <max-pool-size>25</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>SecurityDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>security</user-name>
      security
      <min-pool-size>0</min-pool-size>
      <max-pool-size>40</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>SubmissionDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>submission</user-name>
      submission
      <min-pool-size>0</min-pool-size>
      <max-pool-size>25</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <!-- <local-tx-datasource>
      <jndi-name>TPartyDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>tparty</user-name>
      tparty
      <min-pool-size>0</min-pool-size>
      <max-pool-size>3</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource> -->

      <local-tx-datasource>
      <jndi-name>ThirdPartyDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>tparty</user-name>
      tparty
      <min-pool-size>0</min-pool-size>
      <max-pool-size>3</max-pool-size>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>ArgoTransDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>argotrans</user-name>
      argotrans
      <min-pool-size>0</min-pool-size>
      <max-pool-size>15</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>PTSdatasource</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:load</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>rebate</user-name>
      rebate
      <min-pool-size>0</min-pool-size>
      <max-pool-size>25</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>MassRerateDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>massrerate</user-name>
      massrerate
      <min-pool-size>0</min-pool-size>
      <max-pool-size>5</max-pool-size>
      <blocking-timeout-millis>60000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>BatchUserDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>batchuser</user-name>
      batchuser
      <min-pool-size>0</min-pool-size>
      <max-pool-size>5</max-pool-size>
      <blocking-timeout-millis>20000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      </local-tx-datasource>


        • 1. Re: Problem With Connection Pooling Under Load
          Adrian Brock Master

           

          "adrian@jboss.org" wrote:
          "adrian@jboss.org" wrote:
          Neither of those messages looks like a jboss problem.
          This looks like a problem inside your jdbc driver.

          The first says it received invalid data from the server.
          I've no idea what the second one means, we certainly have no control
          over Refcursor.

          On concurrency, if you are doing the pooling correctly at no point will a connection
          be used on multiple threads. If you break this, you are likely to see problems
          with transactions rather than the jdbc connection.

          Even if you do access a jdbc connection concurrently it should be able to handle this.
          Concurrent access is supported in the spec, but it is not recommended because
          of the transaction problem mentioned above and the scalability problem of lots
          of threads trying to share one socket connection.

          If you suspect a stale connection, you can check a connection before it
          is handed out from the pool.
          <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
          or you can try the <valid-connection-checker-class-name> see
          docs/examples/jca/oracle-ds.xml for more info

          Regards,
          Adrian


          • 2. Re: Problem With Connection Pooling Under Load
            Chad La Joie Newbie

             

            "clajoie" wrote:
            "clajoie" wrote:
            Another thing to check is to make sure you have the correct JDBC driver version. Oracle ties thier JDBC releases pretty closely with database patch levels. We've seen some errors (not those) when we had a driver/db version mismatch. This was true even though we were using the thin client. Just something quick to check.


            • 3. Re: Problem With Connection Pooling Under Load
              F Zemen Newbie

               

              "jbossuser" wrote:
              "jbossuser" wrote:
              We have already tried all the oracle drivers, thin & OCI. Depending on the driver, the problems remain or worse happens.

              Also, we have tried the ping database technique. Under heavy load/concurrency when the problem occurs, it does pick up a large number of bad connections (this is disconcerting...why are they bad is any engineer's question), but some connections still go "bad" during the normal transactions (less than a few seconds).

              Currently, I'm preparing a wrapped version of the OracleDriver and OracleConnection, as well as extending some JCA classes to log the connection usage and determine if they are truly not re-used.

              One thing is clear, spec or not, load or not, when an Oracle connection is used concurrently all kinds can crop up.


              • 4. Re: Problem With Connection Pooling Under Load
                F Zemen Newbie

                 

                "jbossuser" wrote:
                "jbossuser" wrote:
                "jbossuser" wrote:
                My previous post is misleading. What we are trying to determine is that Connections aren't re-used concurrently - we fully expect re-use otherwise.


                • 5. Re: Problem With Connection Pooling Under Load
                  Jayasudan.M Newbie

                  Hi jbossuser,

                  Even I am facing the same problem described by you couple of months back.

                  Can I know if you have got any answers to your posting? Pl throw some light on this issue I have been behind this for quite sometime :(

                  • 6. Re: Problem With Connection Pooling Under Load
                    Adrian Brock Master

                    Make sure you have
                    Debug == false
                    in deploy/transaction-service.xml

                    Failing that, make sure you aren't leaking connections across threads,
                    e.g. storing them static fields.

                    Regards,
                    Adrian

                    • 7. Re: Problem With Connection Pooling Under Load
                      Jayasudan.M Newbie

                      Hi Adrian,

                      Thanks for the answer.

                      Yes the Debug tag is true in my test setup.
                      I am not able to understand why Debug tag being true should cause those exceptions. Can you pl explain?

                      I have reviewed the code. There was one code path that did not close the used connections. (connections created in a loop!) Hence jboss threw '[org.jboss.resource.connectionmanager.CachedConnectionManager] Closing a connection for you. Please close them yourself' exception.
                      Could this result in non-availability of DB resource and hence delay the container managed bean transactions?

                      Pl clarify ASAP.

                      regards,
                      Jayasudan.

                      • 8. Re: Problem With Connection Pooling Under Load
                        Adrian Brock Master

                        There is no obligation to "clarify asap" this is a free forum. You get answers out
                        of the goodness of people's hearts.

                        Errors committed so far:
                        1) Running a stress test in debug mode (granted you didn't know you were doing it)
                        2) Hijacking somebody else's thread (this is jbossuser's thread - actually about a
                        different issue)
                        3) Bad manners.

                        Why don't you go answer somebody else's question "ASAP"?

                        Regards,
                        Adrian