Hi,
We have developed a web application using the following technologies

hibernate 3
struts
spring
mysql
tomcat

The system has currently been deployed. I have a question regarding the dataSource connection pooling. As can be seen in the spring configuration below, there is no maxActive specified in the dataSource bean definition, and the value of the hibernate.dbcp.maxActive property has been set to 30 in the sessionFactory bean.

I am displaying the values of the dataSource variables on the screen and I see that the maxActive is showing a value of 8. My questions are:

1. Will the hibernate property overwrite this value? Or do I need to set the maxActive in the dataSource bean definition to 30 in order to set hibernate's value to 30?

2. Can someone tell me what the "best practices" values are for the db pooling properties (max active, maxWait, etc)? Is 8 the default for "maxActive", and does this not seem too low?

Thanks,
-Riz.


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${hibernate.connection.driver_class}" />
<property name="url" value="${hibernate.connection.url}" />
<property name="username" value="${hibernate.connection.username}" />
<property name="password" value="${hibernate.connection.password}" />
</bean>


<!-- Hibernate SessionFactory -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />

<property name="mappingDirectoryLocations">
<list>
<value>classpath:com/cwsi/eshipper/model</value>
<value>classpath:com/cwsi/eshipper/carrier/purolator/model</value>
<value>classpath:com/cwsi/eshipper/carrier/cws/model</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
${hibernate.dialect}
</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.dbcp.maxActive">30</prop>
<prop key="hibernate.dbcp.whenExhaustedAction">20</prop>
<prop key="hibernate.dbcp.maxWait">${hibernate.connection.maxWait}</prop>
<prop key="hibernate.dbcp.maxIdle">10</prop>
</props>
</property>
<property name="eventListeners">
<map>
<entry key="merge">
<bean class="org.springframework.orm.hibernate3.support.IdTransferringMergeEventListener" />
</entry>
</map>
</property>
</bean>

Rizmerc,

I used dbcp only briefly, I have more hands on experience with c3p0. If you see less connections than you expect it may be because they are dropping. That would be a bad thing. They may drop because of some timeout on the server side, some other network related issue or because there is a bug in the application and the server closes the connection. You should dig for it in the logs.

Your number of connections depend on how long your transactions are. If you have very brief transactions you can get away with a small number of connections, if you have longer transactions you will need to increase. I think 10/30 is a good starting point, you should be able to increase the max if you have just a few app servers. You should monitor your server during the busy time and see what are your typical numbers.

Tuning is all related together... you should look in your slow log queries to see if you need to tune the indexes, rewrite queries and so on. This will keep your transactions short. But while you're figuring things out you may need to increase the maximum number of connections, add more app servers and so on.

Good luck :)

Marius

thanks for the reply.

so if i do not set the dbcp value in the bean definition and set hibernate.dbcp.maxActive to 30, which value will be used?

Main question is, does the dataSource bean's maxActive value have to be at least as high as hibernate.dbcp.maxActive value?

I know that for c3p0 the hibernate value for some parameters take precedence over the c3p0 values (hibernate sets the value after c3p0 has been initialized). It should be pretty simple to validate the same thing here, just put a breakpoint in the relevant function or add some logging.

Sorry for not having the exact answer to your question.

Marius

I don't know about spring specifically. But with hibernate on it's own, the connection pooling configuration only has an affect if hibernate is managing it's own connections. So if you're using a datasource it's up to the datasource to manage the connection pooling.

I seen no reason why spring would have needed to change this, but they may have. You can always test it by setting the size to 1 then attempting to open two sessions at the same time.

Hi Edc,
Based on your comment below, it seems I am missing something here.

"I don't know about spring specifically. But with hibernate on it's own, the connection pooling configuration only has an affect if hibernate is managing it's own connections. So if you're using a datasource it's up to the datasource to manage the connection pooling. "

In the code I presented in the original post, it is clear that the bean "dataSource" (which is an instance of BasicDataSource from apache's dbcp), is being plugged into the sessionFactory.
You say that hibernate manages its own connections, but what I understand is that hibernate is delegating pool management to dbcp.

am i wrong? what am i missing??

thanks,
-Riz.

My comment was how hibernate behaves without spring. I don't know enough about Spring to say if it plugs your configured data source into Hibernate by Mimicking a JNDI data source or whether it does something else to get the data source into hibernate.

In the absence of spring, when hibernate is configured to use a JNDI data source the connection pooling properties in the hibernate configuration (as opposed to the data source configuration) have no affect.

As I said, I don't know enough about spring to say whether or not it follows a similar pattern. However given that BasicDataSource from DBCP is an implementation of javax.sql.DataSource it seems likely that Spring is configuring Hibernate to use it as it would a JNDI data source.

If this wasn't the case and Spring was configuring hibernate in such a way that the connection pool properties had some affect, then there would actually be two separate connection pool instances which would be rather bad, so I see no reason Spring would be doing this.

In essence, I think you need to do all the connection pool configuration as part of your data source rather than as part of the hibernate configuration. But as I said, I haven't used Spring much.