Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

tomcat 7 connection pool empty after database restarts

 
Lyan Fu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 18020
47
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Lyan Fu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 18020
47
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic