[Performance Tuning] - Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB, Hibernate 3.3.2.GA
1:31 a.m.
shirish maurya [https://community.jboss.org/people/shirishmaurya] created the discussion
"Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate
3.3.2.GA"
To view the discussion, visit: https://community.jboss.org/message/742129#742129
--------------------------------------------------------------
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>
<metadata>
<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
0)
at
org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)
... 90 more
is there any particular setting i m missing for hibernate or jboss.
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/742129#742129]
Start a new discussion in Performance Tuning at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]
4:47 a.m.
New subject: [Performance Tuning] - Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB, Hibernate 3.3.2.GA
Wolf-Dieter Fink [https://community.jboss.org/people/wdfink] created the discussion
"Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate
3.3.2.GA"
To view the discussion, visit: https://community.jboss.org/message/742138#742138
--------------------------------------------------------------
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.
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/742138#742138]
Start a new discussion in Performance Tuning at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]
2:51 p.m.
New subject: [Performance Tuning] - Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB, Hibernate 3.3.2.GA
shirish maurya [https://community.jboss.org/people/shirishmaurya] created the discussion
"Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate
3.3.2.GA"
To view the discussion, visit: https://community.jboss.org/message/742164#742164
--------------------------------------------------------------
@ https://community.jboss.org/people/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
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/742164#742164]
Start a new discussion in Performance Tuning at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]
4:29 a.m.
New subject: [Performance Tuning] - Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB, Hibernate 3.3.2.GA
Wolf-Dieter Fink [https://community.jboss.org/people/wdfink] created the discussion
"Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate
3.3.2.GA"
To view the discussion, visit: https://community.jboss.org/message/742181#742181
--------------------------------------------------------------
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.
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/742181#742181]
Start a new discussion in Performance Tuning at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]
8:29 a.m.
New subject: [Performance Tuning] - Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB, Hibernate 3.3.2.GA
shirish maurya [https://community.jboss.org/people/shirishmaurya] created the discussion
"Re: Connection pool problem jboss 5.1 + hibernate oracle 11.2.3.0 DB,Hibernate
3.3.2.GA"
To view the discussion, visit: https://community.jboss.org/message/742193#742193
--------------------------------------------------------------
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 will jus some and view some content thats works fine
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/742193#742193]
Start a new discussion in Performance Tuning at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]
4494
days inactive
4495
days old
4 comments
2 participants
participants (2)
-
shirish maurya -
Wolf-Dieter Fink