6 Replies Latest reply on Jun 20, 2012 1:28 PM by shirishmaurya

Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

shirishmaurya Jun 16, 2012 2:31 AM

issue with connection management for jboss 5.1 + hibernate, my hibrenate uses jndi name define for connection pooling,

below is the hibernate configuration

03:29:45,068 INFO [SettingsFactory] Automatic flush during beforeCompletion(): disabled

03:29:45,068 INFO [SettingsFactory] Automatic session close at end of transaction: disabled

03:29:45,070 INFO [SettingsFactory] Scrollable result sets: enabled

03:29:45,070 INFO [SettingsFactory] JDBC3 getGeneratedKeys(): disabled

03:29:45,071 INFO [SettingsFactory] Connection release mode: auto

03:29:45,072 INFO [SettingsFactory] Default batch fetch size: 4

03:29:45,072 INFO [SettingsFactory] Generate SQL with comments: disabled

03:29:45,072 INFO [SettingsFactory] Order SQL updates by primary key: disabled

03:29:45,073 INFO [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory

03:29:45,078 INFO [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory

03:29:45,079 INFO [SettingsFactory] Query language substitutions: {}

03:29:45,079 INFO [SettingsFactory] JPA-QL strict compliance: disabled

03:29:45,079 INFO [SettingsFactory] Second-level cache: disabled

03:29:45,079 INFO [SettingsFactory] Query cache: disabled

03:29:45,079 INFO [SettingsFactory] Optimize cache for minimal puts: disabled

03:29:45,079 INFO [SettingsFactory] Structured second-level cache entries: disabled

03:29:45,092 INFO [SettingsFactory] Statistics: disabled

i have also made a connection release mode to on close and track transation track statement but no where it helps

ds file config

min-pool-size>8</min-pool-size>

<max-pool-size>100</max-pool-size>

<blocking-timeout-millis>50000</blocking-timeout-millis>

<use-strict-min>true</use-strict-min>

<track-statements>true</track-statements>

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

<type-mapping>Oracle11g</type-mapping>

</metadata>

</local-tx-datasource>

</datasources>

after certain duration of time say 30 mins the connection problem arises, this happens as i increase the concuurent users. the connection are getting closed out from the code.

also i see a lot of inactive session at DB end increase duration the test period.

org.hibernate.exception.GenericJDBCException: Cannot open connection

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)

at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)

at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)

at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)

at org.hibernate.loader.Loader.doQuery(Loader.java:696)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)

at org.hibernate.loader.Loader.loadCollectionBatch(Loader.java:2057)

at org.hibernate.loader.collection.BatchingCollectionInitializer.initialize(BatchingCollectionInitializer.java:70)

at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:587)

at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventL

istener.java:83)

at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1744)

at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)

at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)

at org.hibernate.collection.PersistentBag.iterator(PersistentBag.java:272)

Caused by: org.jboss.util.NestedSQLException: Unable to get managed connection for jdbc/mya/bonusPool; - nested throwable: (javax.re

source.ResourceException: Unable to get managed connection for jdbc/mya/bonusPool)

at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:95)

at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java

:82)

at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)

... 84 more

Caused by: javax.resource.ResourceException: Unable to get managed connection for jdbc/mya/bonusPool

at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:441)

at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:424)

at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)

at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManag

er2.java:941)

at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)

... 86 more

Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 50000 [ms] )

at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:311)

at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:69

at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)

... 90 more

is there any particular setting i m missing for hibernate or jboss.

1. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

wdfink Jun 16, 2012 5:47 AM (in response to shirishmaurya)

The reason is logged as the cause:
No ManagedConnections available within configured blocking timeout ( 50000 [ms] )

You have set a maximum of 100 connections and a blocking timeout of 5000ms.
If you increase the nummer of parallel throughput the getConnection might wait for the next free one.
As the handling is not fair it might happen that one is waiting longer as others and hit the timeout.
Also if your transactions run for longer and block the connections.

You might check this in the jmx.console, there are several counters for the connection pool.

In fact you have to increase the max-pool-size.
Actions
2. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

shirishmaurya Jun 16, 2012 3:51 PM (in response to wdfink)

@ Wolf-Dieter Fink thanks for the reply , but as i increase the load it caused a issue , i there a some setting between hibernate and jboss to be tunned out

i use the jndi name in the hibernate propetry file while directly takes connection from ds file configured , some transcation commiting or auto commit after the transaction are completed
Actions
3. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

wdfink Jun 17, 2012 5:29 AM (in response to shirishmaurya)

Do you checked the connection pool statistics of your datasource configuration?
If you have reached the 100 connections parallel the next thread/transaction/db-access must wait for a free one.

The possible solutions are:
reduce the duration of transaction where the database is used, that is only a small posibility because you need to read first and commit very late.
So the effort to optimize here is very high and depend to the application maybe not possible.

Other thing is to increase the number of parallel connections to the database, in that case you might reach the limit of one JBoss instance and you have to cluster the application.
Actions
4. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

shirishmaurya Jun 17, 2012 9:30 AM (in response to wdfink)

ys i though the same and did clustering for it now i have 4 JVM 2 on each server, when i run the test the connection exhasut after say 50 mins.

its happing for the test for application where the users will view some stats and accept it , this acceptation will updates some values in table if user exist in table else insert it ,

i think heavy insert of update is causing the problem and not able to find that excatly needs to be tuuned up to help easier way for this update or insert.

if i test for senarios where user will jus do some view of content and log out thats works fine
Actions
5. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

wdfink Jun 17, 2012 2:20 PM (in response to shirishmaurya)

It sounds like you have a connection leak.
You should monitor the pool statistic and see whether the number of used connections will grow ovber the time.

Also you might have a look into these:
https://community.jboss.org/wiki/CanJBossTellMeWhenIDontCloseAConnection
https://community.jboss.org/wiki/WhatDoesTheMessageDoYourOwnHousekeepingMean
Actions
6. Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate 3.3.2.GA

shirishmaurya Jun 20, 2012 1:28 PM (in response to wdfink)

i jus checked with setting as mentioned in the docs this setting are there with debug true and track statement but i dont see any msg as closing a connection.

i need some help hibernate looks like hibernate helds the connection.

does the drive class i m using affect this ?

<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

<driver-class>oracle.jdbc.Pool.OracleDataSource</driver-class>
Actions

Go to original post