aspose file tools*
The moose likes Tomcat and the fly likes tomcat 7 connection pool empty after database restarts Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Tomcat
Bookmark "tomcat 7 connection pool empty after database restarts" Watch "tomcat 7 connection pool empty after database restarts" New topic
Author

tomcat 7 connection pool empty after database restarts

Lyan Fu
Greenhorn

Joined: Mar 11, 2004
Posts: 3
I'd like to get some advice on how to configure tomcat 7 connection pool to deal with empty pool issue when database restarts.
With my current configuration (see below), the connection pool contains no connection after a database is restarted, as previous connections are all invalidated. Ideally, the connection pool should create new connections according to the description of property "minidle" in doc, but that is not happening (of course new connection will be created upon application request). Is this acceptable behavior of a connection pool? How is everyone dealing with this situation? Thanks!

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
<property name="url"><value>jdbc:mysql://mydbserver:3306/mydb</value></property>
<property name="username"><value>user</value></property>
<property name="password"><value>password</value></property>
<property name="initialSize"><value>10</value></property>
<property name="maxActive"><value>100</value></property>
<property name="maxIdle"><value>20</value></property>
<property name="minIdle"><value>10</value></property>
<property name="testWhileIdle"><value>true</value></property>
<property name="timeBetweenEvictionRunsMillis"><value>300000</value></property>
<property name="testOnBorrow"><value>true</value></property>
<property name="validationQuery"><value>SELECT 1</value></property>
<property name="defaultAutoCommit"><value>true</value></property>
<property name="jmxEnabled"><value>false</value></property>
</bean>
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16236
    
  21

Welcome to the JavaRanch, Lyan!

Actually, the critical parameter for you is "initialSize".

Have you switched on trace-level logging to see what's really happening in the pool and when? It's possible that the pool is being properly initialized but that the pool itself is being constructed on-demand rather than at initial startup, since the pool is a resource and not a process (thread).


Customer surveys are for companies who didn't pay proper attention to begin with.
Lyan Fu
Greenhorn

Joined: Mar 11, 2004
Posts: 3
Thanks for your response, Tim.

What I experienced is exactly like what you said - "The pool is being properly initialized but that the pool itself is being constructed on-demand" after database is restarted. I also checked the source code for tomcat jdbc connection pool, and found it made no effort to make sure the number of idle connections in the pool is above minimal setting. So can I say what I experienced is normal behavior of the connection pool?

BTW, there is already a sweeping thread implemented in the connection pool to clean up the pool. I wonder what makes it hard to scale up the pool when idle connection number drops below minimal.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16236
    
  21

Sometimes it takes a while for things to sink in. I was thinking the usual "tomcat restart" and you were saying database restart.

I can well understand how you'd end up with an empty pool in the case where the database was restarted. One doesn't normally restart the database while an app is running unless things are really dire. Just aside from the pooling issues, there's the question of what's going to happen to any transactions-in-progress within the app itself. Few apps are designed with that occasion in mind.

If database restarts are a routine occurrence - for example, you yank down the server for nightly backups - then you have to decide if having your pool sucked dry is really a problem. After all, it will replenish itself in time. If you positively must have a minimal pool ready and waiting, consider adding an app function that does nothing but request a quota of connections and then returns them to the pool to re-prime the pump (so to speak).

Incidentally, no quality DBMS should have to be shut down just for backup purposes. Even the major free open-source ones have support for hot backups.
Lyan Fu
Greenhorn

Joined: Mar 11, 2004
Posts: 3
Thanks for the detailed explanation. It absolutely cleared my doubts, and hopefully it will help others who may be puzzled by the very same issue. Very much appreciate your time as well as your contribution to the community.
 
 
subject: tomcat 7 connection pool empty after database restarts