Hi,

We have a web application with a database connection. We're using JPA, Hibernate and Spring. Below is our spring application xml file:


<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

<bean id="pollService" class="se.aftonbladet.poll.PollServiceManager">
<property name="pollDAO" ref="pollDAO" />
</bean>

<bean id="pollDAO" class="se.aftonbladet.poll.JPAPollDAO">
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql..." />
<property name="username" value="poll" />
<property name="password" value="poll" />
<!-- Added below properties to deal with connection timeout (to db) when pollService hasnt been used for a long time -->
<!-- Max number of connections that can remain idle in pool, without extra conns being released -->
<property name="maxIdle" value="2" />
<!-- Max number of active connections in the pool -->
<property name="maxActive" value="8" />
<!-- In milliseconds - wait no longer for a connection to be returned (when no conns available in pool) -->
<property name="maxWait" value="10000" />
<!-- Validation Query to be used to validate connections from pool -->
<property name="validationQuery" value="SELECT 1" />
<!-- Validate objects before being returned to pool (true/false) -->
<property name="testOnBorrow" value="true" />
<!-- Below methods are deprecated in Commons DBCP 1.3 (should be removed from here?) -->
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="30" />
<property name="logAbandoned" value="true" />
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="pollPersistenceUnit" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="database" value="MYSQL" />
<property name="showSql" value="false" />
</bean>
</property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />


First question I have: The datasource we use - is it a good one to use? We will have lots of simultaneous user requests (maybe 50-100) to our database (MySQL 5.0).

When load-testing our application the first time we got errors like the following in our logs:


2008-09-29 18:08:38,864 ERROR [JDBCExceptionReporter.java:78] : Too many connections

2008-09-29 18:10:31,605 ERROR [JDBCExceptionReporter.java:78] : Data source rejected establishment of connection, message from server: "Too many connections"


This is just the message from the exception - but since we added printout of the full stacktrace in the logs we haven't been able to reproduce the exception.

Very grateful for help with configuring the datasource (or possibly with configuring the actual database?) !

/best regards, Håkan Jacobsson
[/b]

Hi Håkan,

dbcp might not be the best connection pool to use. Per default Hibernate uses c3p0. It is hard to say which pool would be the best in your particular situation. The best thing is to test the different implementation in production like environments.

The maximum active connection count of 8 might be a little low for your usecase, but it might also be that your problem was not caused be the pool at all. MySQL can run out of connections as well - http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html.

--Hardy

Okay,

Thanx for you reply. If we get this error again we will have the full stacktrace which might provide some additional info. I'll try out c3p0 too.
Regarding the max-setting for active connections - can this really be the cause of the exception? Is it possible that the application tries to create more than the allowed number of active connections, in spite of the restriction?

I have problem with teh configuration of c3po hibernate poolded datasource...it hangs when initializing HibernateTransactionManager

INFO - ibernateTransactionManager - Using DataSource [com.mchange.v2.c3p0.Combo
PooledDataSource@d0af9b[ acquireIncrement -> 3, acquireRetryAttempts -> 30, acqu
ireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, b
reakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassNam
e -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, description -> null, driv
erClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnres
olvedTransactions -> false, identityToken -> d0af9b, idleConnectionTestPeriod ->
-1, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/biblestudy?aut
oReconnect=true&amp;relaxAutoCommit=true, loginTimeout -> 0, maxIdleTime -> 0, m
axPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSi
ze -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=
******, password=******}, propertyCycle -> 300, testConnectionOnCheckin -> false
, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ]
] of Hibernate SessionFactory for HibernateTransactionManager

My spring configuration file of datasoucre is following:

<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/b ... ns-2.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/a ... op-2.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/t ... tx-2.0.xsd">
<tx:annotation-driven transaction-manager="transactionManager"/>
<bean id="placeholderConfig"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>classpath:application.properties</value>
</property>
<property name="systemPropertiesModeName">
<value>SYSTEM_PROPERTIES_MODE_OVERRIDE</value>
</property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<!-- Configuring automatic schema generation in Spring -->
<property name="namingStrategy" ref="namingStrategy" />
<property name="annotatedClasses">
<list>
<value>fi.passiba.services.group.persistance.Groupservice</value>
<value>fi.passiba.services.group.persistance.Groups</value>

</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
${hibernate.dialect}
</prop>
<prop key="hibernate.hbm2ddl.auto">
update
</prop>
<!-- <prop key="current_session_context_class">managed
</prop>-->
<prop key="hibernate.transaction.flush_before_completion">true
</prop>
<prop key="hibernate.transaction.auto_close_session">true
</prop>
<prop key="hibernate.show_sql">true
</prop>
<prop key="hibernate.format_sql">true
</prop>
<prop key="hibernate.max_fetch_depth">3
</prop>
<!-- <prop key="hibernate.default_batch_fetch_size">4</prop>-->
</props>
</property>
<property name="entityInterceptor">
<ref bean="entityAuditInterceptor"/>
</property>
</bean>
<!-- Configuring automatic schema generation in Spring -->
<bean id="namingStrategy" class="org.hibernate.cfg.ImprovedNamingStrategy"/>
<bean id="entityAuditInterceptor" class="fi.passiba.hibernate.AuditInterceptor"/>
<!-- setup transaction manager -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>

maven dependencies are following

<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-annotations</artifactId>
<version>3.3.0.ga</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-commons-annotations</artifactId>
<version>3.3.0.ga</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.0.4</version>
</dependency>
<dependency>
<groupId>javax.transaction</groupId>
<artifactId>jta</artifactId>
<version>1.0.1B</version>
</dependency>

Helpful advice kindly appreciated


I have also added javax.
what is the problem