Problem with number of database connections (Oracle)

[michal_rorat]

Hi, I have a problem with configuring JBoss connetion to Oracle database. We are running out of connections on Oracle machine because of this issue.

My configuration in oracle-ds.xml:

  <local-tx-datasource>
    <jndi-name>OracleDS</jndi-name>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
   
    <connection-url>jdbc:oracle:thin:@[dbname:port:sid]</connection-url>
    <user-name>[user_name]<user-name>
    <password>[password]</password>

    <min-pool-size>0</min-pool-size>
    <max-pool-size>60</max-pool-size>

I also have a library which creates its own connection pool with

min-pool-size=0

max-pool-size=10

And 5 MDBs with one connection reserved for each of them.

You might think that with this configuration after starting JBoss you will have 5 connections made to DB but I have 38! Even when I set max-pool-size in oracle-ds.xml to 5 it don't change the number of created connections which I get from Oracle Enterprise Manager.

If I put for example 5 in min-pool-size the numer of connection created is 43.

I also tried to find out the configuration for ManagedConnectionPool MBean with this command:

twiddle.bat invoke jboss.jca:service=ManagedConnectionPool,name=OracleDS listFormattedSubPoolStatistics

Sub Pool Statistics:
Sub Pool Count: 1
---------------------------------

Track By Transaction: true
Available Connections Count: 60
Max Connections In Use Count:1
Connections Destroyed Count:2
Connections In Use Count:0
Total Block Time:0
Average Block Time For Sub Pool:0
Maximum Wait Time For Sub Pool:0
Total Timed Out:2

------------------------------------------------------------------------------------------

My questions are...

- how can I find the place where those connections are created?

- is there any other configuration for connection pools?

- is there a list of connections with a class name which created each one of them?

Regards,

Michal Rorat

[erasmomarciano]

wich os  are you  using?

[michal_rorat]

Vista Business 64bit

[erasmomarciano]

Try try running this command from shell

netstat -an

You check if there are connection on the port 1521

[michal_rorat]

TCP    192.168.4.55:59147     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59148     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59149     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59150     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59151     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59152     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59153     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59154     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59155     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59156     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59157     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59158     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59159     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59160     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59161     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59162     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59163     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59164     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59165     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59166     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59167     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59168     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59169     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59170     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59171     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59172     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59173     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59174     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59175     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59176     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59177     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59178     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59179     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59180     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59181     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59182     192.168.253.31:1521    ESTABLISHED
TCP    192.168.4.55:59183     192.168.253.31:1521    ESTABLISHED

Exactly the same number (37) as I'm getting from Oracle Enterprise Manager.

[adrian.brock]

michal_rorat wrote:

You might think that with this configuration after starting JBoss you will have 5 connections made to DB but I have 38! Even when I set max-pool-size in oracle-ds.xml to 5 it don't change the number of created connections which I get from Oracle Enterprise Manager.

If I put for example 5 in min-pool-size the numer of connection created is 43.

43-5=38

5 is the number of connections that get created when you first use the datasource, if you specify a min-pool-size of 5.

Since that number matches the difference in configuration that suggests to me that there were none in the pool before the change.

Sub Pool Statistics:
Sub Pool Count: 1
---------------------------------

Track By Transaction: true
Available Connections Count: 60
Max Connections In Use Count:1
Connections Destroyed Count:2
Connections In Use Count:0
Total Block Time:0
Average Block Time For Sub Pool:0
Maximum Wait Time For Sub Pool:0
Total Timed Out:2

-----------------------------------------------------------------------------------------

Can you look on the MBean itself at the attributes. There should be a one called "ConnectionsCreatedCount".

Once you find it, substract the "ConnectionsDestroyedCount" (which we know was 2 and they both timed out).

I don't know why the created count is not shown in listStatistics? You might want to report it as a bug?

And 5 MDBs with one connection reserved for each of them

When you say 5 MDBs, do you mean one EJB deployment with 5 JMS Sessions in the pool

or do mean 5 EJBs with 15 (the default)?

If the latter, then each MDB instance has a DB connection, that could reach 5 x 15 = 75 connections if all the sessions/instances get used.

This is probably what you are seeing?

What numbers do you get if you don't deploy the MDB(s)?

P.S. Holding an unshared resource in an EJB instance is an anti-pattern. It doesn't scale (see the calculation above).

P.S.2. It can also lead to problems if your MDB throws a RuntimeException or Error, since them the instance is thrown away

and a new one created without the destroy lifecycle getting invoked.

So you have no opportunity to close() the resource - it won't get cleaned up until garbage collection spots it (assuming it has a finalize method).

[michal_rorat]

[e-mail] wrote:

43-5=38

5 is the number of connections that get created when you first use the datasource, if you specify a min-pool-size of 5.

Since that number matches the difference in configuration that suggests to me that there were none in the pool before the change.

I just tested the application with one MessageDrivenBean removed and started JBoss created 34 connections. Since I have 5 of them with preety much the same configuration we can assume that they reserve 20 connections - but still - what reserve remaining 18 of them?

this is my MDB annotation:

@MessageDriven(name = "abcQueueListener",
        activationConfig =
        {
            @ActivationConfigProperty(propertyName = "ProviderAdapterJNDI", propertyValue = "OAQJMSProvider"),   
            @ActivationConfigProperty(propertyName = "UseDLQ", propertyValue = "false"), 
            @ActivationConfigProperty(propertyName = "destinationType", propertyValue = "javax.jms.Queue"), 
            @ActivationConfigProperty(propertyName = "destination", propertyValue = "queue/abc"), 
            @ActivationConfigProperty(propertyName = "acknowledgeMode", propertyValue = "Auto-acknowledge"),
            @ActivationConfigProperty(propertyName = "Durable", propertyValue = "false"),
            @ActivationConfigProperty(propertyName = "SessionTransacted", propertyValue = "false"),
            @ActivationConfigProperty(propertyName = "MessageSelector", propertyValue = "JMSType='abc_BLOB'"),
            @ActivationConfigProperty(propertyName = "maxSession", propertyValue="3")
        })

Can you look on the MBean itself at the attributes. There should be a one called "ConnectionsCreatedCount".

Once you find it, substract the "ConnectionsDestroyedCount" (which we know was 2 and they both timed out).

I don't know why the created count is not shown in listStatistics? You might want to report it as a bug?

c:\jboss-4.2\bin>twiddle.bat invoke jboss.jca:service=ManagedConnectionPool,name=OracleDS listFormattedSubPoolStatistics
Sub Pool Statistics:
Sub Pool Count: 1
------------------------------------------------------

Track By Transaction: true
Available Connections Count: 60
Max Connections In Use Count:1
Connections Destroyed Count:1
Connections In Use Count:0
Total Block Time:0
Average Block Time For Sub Pool:0
Maximum Wait Time For Sub Pool:0
Total Timed Out:1

------------------------------------------------------

c:\jboss-4.2\bin>twiddle.bat get jboss.jca:service=ManagedConnectionPool,name=OracleDS ConnectionCreatedCount
ConnectionCreatedCount=1
c:\jboss-4.2\bin>twiddle.bat get jboss.jca:service=ManagedConnectionPool,name=OracleDS ConnectionDestroyedCount
ConnectionDestroyedCount=1

and listStatistics throws exception like this:

c:\jboss-4.2\bin>twiddle.bat invoke jboss.jca:service=ManagedConnectionPool,name=OracleDS listStatistics
15:47:07,318 ERROR [Twiddle] Exec failed
java.lang.reflect.UndeclaredThrowableException
        at $Proxy0.invoke(Unknown Source)
        at org.jboss.console.twiddle.command.InvokeCommand.invoke(InvokeCommand.java:235)
        at org.jboss.console.twiddle.command.InvokeCommand.execute(InvokeCommand.java:291)
        at org.jboss.console.twiddle.Twiddle.main(Twiddle.java:306)
Caused by: java.lang.ClassNotFoundException: org.jboss.resource.statistic.pool.JBossManagedConnectionPoolStatistics (no security manager: RMI cla
ss loader disabled)
        at sun.rmi.server.LoaderHandler.loadClass(LoaderHandler.java:375)
        at sun.rmi.server.LoaderHandler.loadClass(LoaderHandler.java:165)
        at java.rmi.server.RMIClassLoader$2.loadClass(RMIClassLoader.java:620)
        at java.rmi.server.RMIClassLoader.loadClass(RMIClassLoader.java:247)
        at sun.rmi.server.MarshalInputStream.resolveClass(MarshalInputStream.java:197)
        at java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1575)
        at java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1496)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1732)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1329)
        at java.io.ObjectInputStream.readObject(ObjectInputStream.java:351)
        at java.rmi.MarshalledObject.get(MarshalledObject.java:142)
        at org.jboss.invocation.jrmp.interfaces.JRMPInvokerProxy.invoke(JRMPInvokerProxy.java:134)
        at org.jboss.invocation.InvokerInterceptor.invokeInvoker(InvokerInterceptor.java:365)
        at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:197)
        at org.jboss.jmx.connector.invoker.client.InvokerAdaptorClientInterceptor.invoke(InvokerAdaptorClientInterceptor.java:66)
        at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:70)
        at org.jboss.proxy.ClientMethodInterceptor.invoke(ClientMethodInterceptor.java:74)
        at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:100)
        ... 4 more

and listUnderlyingNativeConnectionStatistics returns nothing.

c:\jboss-4.2\bin>twiddle.bat invoke jboss.jca:service=ManagedConnectionPool,name=OracleDS listUnderlyingNativeConnectionStatistics

[adrian.brock]

The ConnectionsCreatedCount is 1 (and that has been destroyed - see ConnectionDestroyedCount).

So it is not the connection pool. It has zero connections open.

You have maxSessions=3 which means there could be three instances for that MDB concurrently.

With 5 mdbs (if they are all the same) that would account for 5x3=15

As to what else is creating connections, it must be somewhere else in your code.

It isn't the JBoss connection pool.

Like I suggested for the MDB, try removing some of your deployments one at a time,

until you find it.

Failing that, the only thing I can suggest is to use a debugger or profiler to determine what

stacktraces are invoking the OracleConnection constructor.

[adrian.brock]

            @ActivationConfigProperty(propertyName = "ProviderAdapterJNDI", propertyValue = "OAQJM

Do you also have connections to Oracle for JMS?

[michal_rorat]

I know this thread is really, really old but I can still see a lot of activity so people are probably still having problems like I had.

So, the solution is: always check your MDB configuration! because even with missing or invalid value in maxSession JBoss will create by default 20 connections!

@ActivationConfigProperty(propertyName = "maxSession", propertyValue="3")

We were setting this value automatically during build and deployment process and maxSession configuration for one MDB got missing.

I would expect to see some warning or even error when JBoss can't parse maxSession value to integer but it fails silently instead.

Hope this helps, and sorry for late solution.