Problem With Connection Pooling Under Load
jbossuser Jan 9, 2004 1:20 PM
"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>
<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>