8 Replies Latest reply on Mar 15, 2004 9:11 AM by adrian.brock

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"?>













<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>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 Jan 13, 2004 11:20 AM (in response to jbossuser)

"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
Actions
2. Re: Problem With Connection Pooling Under Load

clajoie Jan 13, 2004 1:24 PM (in response to jbossuser)

"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.
Actions
3. Re: Problem With Connection Pooling Under Load

jbossuser Jan 13, 2004 2:19 PM (in response to jbossuser)

"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.
Actions
4. Re: Problem With Connection Pooling Under Load

jbossuser Jan 13, 2004 2:22 PM (in response to jbossuser)

"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.
Actions
5. Re: Problem With Connection Pooling Under Load

jayasudan Mar 15, 2004 1:45 AM (in response to jbossuser)

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 :(
Actions
6. Re: Problem With Connection Pooling Under Load

adrian.brock Mar 15, 2004 2:57 AM (in response to jbossuser)

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
Actions
7. Re: Problem With Connection Pooling Under Load

jayasudan Mar 15, 2004 8:22 AM (in response to jbossuser)

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.
Actions
8. Re: Problem With Connection Pooling Under Load

adrian.brock Mar 15, 2004 9:11 AM (in response to jbossuser)

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
Actions

Go to original post