Hello,

It is not a new problem, I have seen this problem in a lot of forum but I did not found a clear answer in these forums.

I am using :
MySql 4.0.18
Hibernate 2.1.2
connector/j 3.0.11
JBoss 3.0.8

I simulate around 120 concurrent users. Each user is simulated by a Java Thread. When I looking to MySql administrator I have around 100 connection open. I did for these users a small request at first to try to identify the problem. I suspect that I forgot something in the configuration of Hibernate.

1- the SessionFactory is obtain from the JDNI (lookup) for the first user and keep in a static variable. So one lookup is done for all the users.

2- I open a session (openSession())

3- I do my request

4- I close my session (close())


The set up of hibernate is done with a JBOSS service (MBean).

<server>
<mbean code="net.sf.hibernate.jmx.HibernateService"
name="jboss.jca:service=Hibernate">
<depends>jboss.jca:service=RARDeployer</depends>
<depends>jboss:service=Naming</depends>
<depends>jboss.jca:service=LocalTxCM,name=MySqlDS</depends>
<depends>jboss.j2ee:jndiName=ejb/util/SequenceGen,service=EJB</depends>
<attribute name="MapResources">mappings/controller.hbm.xml,mappings/lamsld.hbm.xml,mappings/content.hbm.xml</attribute>
<attribute name="JndiName">java:/HibernateFactory</attribute>
<attribute name="Datasource">java:/MySqlDS</attribute>
<attribute name="Dialect">net.sf.hibernate.dialect.MySQLDialect</attribute>

<attribute name="TransactionStrategy">net.sf.hibernate.transaction.JTATransactionFactory</attribute>
<attribute name="TransactionManagerLookupStrategy">net.sf.hibernate.transaction.JBossTransactionManagerLookup</attribute>
<attribute name="UseOuterJoin">false</attribute>
<attribute name="ShowSql">false</attribute>
<attribute name="UserTransactionName">java:/UserTransaction</attribute>
</mbean>
</server>


The connection pool set up is done in a JBOSS service (mysql-service.xml)

<?xml version="1.0" encoding="UTF-8"?>

<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->

<server>

<!-- ==================================================================== -->
<!-- New ConnectionManager setup for mysql using 2.0.11 driver -->
<!-- Build jmx-api (build/build.sh all) and view for config documentation -->
<!-- ==================================================================== -->

<mbean code="org.jboss.resource.connectionmanager.LocalTxConnectionManager" name="jboss.jca:service=LocalTxCM,name=MySqlDS">
<depends optional-attribute-name="ManagedConnectionFactoryName">
<!--embedded mbean-->
<mbean code="org.jboss.resource.connectionmanager.RARDeployment" name="jboss.jca:service=LocalTxDS,name=MySqlDS">
<attribute name="JndiName">MySqlDS</attribute>
<attribute name="ManagedConnectionFactoryProperties">
<properties>
<config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost/lams</config-property>
<config-property name="DriverClass" type="java.lang.String">com.mysql.jdbc.Driver</config-property>
<!--set these only if you want only default logins, not through JAAS -->
<config-property name="UserName" type="java.lang.String">shwaa</config-property>
<config-property name="Password" type="java.lang.String">pshwaa</config-property>
</properties>
</attribute>
<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper</depends>
</mbean>
</depends>

<depends optional-attribute-name="ManagedConnectionPool">
<!--embedded mbean-->
<mbean code="org.jboss.resource.connectionmanager.JBossManagedConnectionPool" name="jboss.jca:service=LocalTxPool,name=MySqlDS">
<attribute name="MinSize">20</attribute>
<attribute name="MaxSize">180</attribute>
<attribute name="BlockingTimeoutMillis">5000</attribute>
<attribute name="IdleTimeoutMinutes">15</attribute>
<attribute name="Criteria">ByContainer</attribute>
</mbean>
</depends>
<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager</depends>

<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager</depends>

<attribute name="TransactionManager">java:/TransactionManager</attribute>
<!--make the rar deploy! hack till better deployment-->
<depends>jboss.jca:service=RARDeployer</depends>

</mbean>


</server>


My questions are :

1) Do I need to do a special set up with Hibernate ?

2) How Hibernate now that it must used the connection pool ?

3) Can I use JBoss connection pool with Hibernate ?

4) What is the role of connector/j in that process ?


I not sure that I am clear, but I have the feeling that I forgot to specify something in the configuration files somewhere.

Any help would be appreciate, I have already asked two questions in this forum, and I have received a good answer each time. So I know that there are some really good programmers in this forum and I am confident to have an answer.

Thanks.

Just configure Hibernate to look up the datasource from JNDI, and the JBoss pool will be used.

Hi Micheal,

How you do that exactly to configure Hibernate to look up the datasource from JDNI.

Remark : My configuration of Hibernate is done only with a service (MBean of JBOSS)


Thanks you.

<attribute name="Datasource">java:/jdbc/DataSource</attribute>

in the mbean service.xml gives the datasource name to use.

Hi Micheal,

My Datasource was already configured by the following information in the jboss-service.xml (see my first message)

<attribute name="Datasource">java:/MySqlDS</attribute>


So do you mean that I have to setup another one. Sorry I don't want to upset you.

Thanks

No, if you use a datasource, you are already using a pool.

OK Micheal,

I will still search, why I have so many connection still open. I have a look in the forum and I am not the only one with this issue ans unfortunately I didn't found any clear answer.

But as soon as I have got one I will post it.

I am using connector/J as JDBC I think it is not a problem ?


Thanks again.

You know that this:

<attribute name="MinSize">20</attribute>
<attribute name="MaxSize">180</attribute>

means you have between 20 and 180 connections depending on load? So 100 is perfectly okay ...

Hi Micheal,

Yes I am using min=20 and max=120. But I don't think that the number of connection used around 100 for 120 users is a correct rate. Because as soon as I set up more users of course the maximum number of connection is reached.

I use a thread safe pattern to retrieve the session. Also I am really sure to close my connection after my select. It is WebApp.

Each client call a struts action.
The Action call via a delegate class a stateless session bean method.
this methode call the hibernate persistance.

Which is a common practice with Hibernate.

May It is the point with Hibernate because a lot of personns have the same problem.

But Micheal I don't to take your time, I will try to found that in the forum maybe a clear answer is some where.

Thanks.

I don't think you understand how a pool works. The pool having 100 connections open does not mean all 100 connections are used at the same time. I suggest you experiment some more and you will find out how many users your pool-config can handle.

Hi Micheal,

Ok I thing I will do some work on it. It just because I watch also the Mysql administrative datas and it seems to indicate how many connection is open. And some time if I increase the number of users to 120 witch a max connection pool size to 100. I have a message that indicate me that I cannot have a new connection (no connection available).

But you rigth anyway I will study a bit more how the pool work.

Is there some tools to manage the number of connection open at same time ?


Thanks.

Is the database set to allow 120 seperate connections?

Hi,

What do you mean ? Can you explain me what your idea.

The database has a parameter for a maximum numbr of conenctions, the pool has a maximum number of connections. If pool # > db # the max size of your pool is really the same as your db.

This seems a good thread to follow on from and ask some questions about transactional connection pooling;

I have a basic Oracle data source (oracle-nontx-ds.xml) which looks like;

<?xml version="1.0" encoding="UTF-8"?>

<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->

<!-- $Id: oracle-ds.xml,v 1.1.2.4 2003/09/17 03:46:01 ejort Exp $ -->
<!-- ==================================================================== -->
<!-- Datasource config for Oracle originally from Steven Coy -->
<!-- ==================================================================== -->


<datasources>
<local-tx-datasource>
<jndi-name>OracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@localhost:1521:sean92db</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>testxmldb</user-name>
<password>testxmldb</password>
<!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
<!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
<!-- Checks the Oracle error codes and messages for fatal errors -->
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->

<!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered
<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
-->

</local-tx-datasource>

</datasources>


If I integrate this into my jboss-service.xml file within my Hibernate SAR File, JBoss fires up fine and I can access the database;

<server>
<mbean code="net.sf.hibernate.jmx.HibernateService" name="jboss.jca:service=HibernateFactoryOracle">
<depends>jboss.jca:service=RARDeployer</depends>
<depends>jboss.jca:service=LocalTxCM,name=OracleDS</depends>
<!-- Make it deploy ONLY after DataSource had been started -->
<attribute name="MapResources">mappings/AuctionItem.hbm.xml, mappings/Bid.hbm.xml, mappings/User.hbm.xml</attribute>
<attribute name="JndiName">java:/HibernateFactoryOracle</attribute>
<attribute name="Datasource">java:/OracleDS</attribute>
<attribute name="Dialect">net.sf.hibernate.dialect.Oracle9Dialect</attribute>
<attribute name="TransactionStrategy">net.sf.hibernate.transaction.JTATransactionFactory</attribute>
<attribute name="TransactionManagerLookupStrategy">net.sf.hibernate.transaction.JBossTransactionManagerLookup</attribute>
<attribute name="UseOuterJoin">false</attribute>
<attribute name="ShowSql">false</attribute>
<attribute name="UserTransactionName">UserTransaction</attribute>
</mbean>
</server>


However, if I switch to a transactional data source, ie

<!-- ===================================================================== -->

<!-- $Id: oracle-xa-ds.xml,v 1.1.2.13 2003/09/18 23:51:39 ejort Exp $ -->

<!-- ===================================================================== -->
<!-- ATTENTION: DO NOT FORGET TO SET Pad=true IN transaction-service.xml -->
<!-- ===================================================================== -->

<datasources>
<xa-datasource>
<jndi-name>XAOracleDS</jndi-name>
<track-connection-by-tx>true</track-connection-by-tx>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:oracle:thin:@localhost:1521:sean92db</xa-datasource-property>
<xa-datasource-property name="User">testxmldb</xa-datasource-property>
<xa-datasource-property name="Password">testxmldb</xa-datasource-property>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<min-pool-size>5</min-pool-size>
<max-pool-size>100</max-pool-size>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>

<!-- <xa-datasource-property name="transaction-isolation">TRANSACTION_REPEATABLE_READ</xa-datasource-property> -->

<!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
<!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
<!-- Checks the Oracle error codes and messages for fatal errors -->
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa -->
<no-tx-separate-pools/>
</xa-datasource>

<mbean code="org.jboss.resource.adapter.jdbc.xa.oracle.OracleXAExceptionFormatter"
name="jboss.jca:service=OracleXAExceptionFormatter">
<depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends>
</mbean>

</datasources>


and put XAOracleDS into the jboss-service.xml where the OracleDS was, I have this happen when JBoss fires up;

2004-05-13 13:52:23,449 ERROR [org.jboss.deployment.scanner.URLDeploymentScanner] MBeanException: Exception in MBean operation 'checkIncompleteDeployments()'
Cause: Incomplete Deployment listing:
Packages waiting for a deployer:
<none>
Incompletely deployed packages:
<none>
MBeans waiting for classes:
<none>
MBeans waiting for other MBeans:
[ObjectName: jboss.jca:service=HibernateFactoryOracle
state: CONFIGURED
I Depend On: jboss.jca:service=RARDeployer
jboss.jca:service=LocalTxCM,name=XAOracleDS

Depends On Me: ]

(which doesn't tell me anything...)

Is this a configuration error somewhere, or Hibernate not handling transactional connection pooling ?


Cheers