• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

tomcat+spring jdbc connection pooling issue

 
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

Tech stack : Tomcat7 + spring REST framework 4.1.7 + spring JDBC

I developed set of RESTful webservices using above technology stack and deployed on to tomcat ,I noticed that 100+(150 to 200) DB connections are getting created and very few out of them are really used at any given point of time.

Currently , I configured my DataSources in tomcat context and referring them in spring-context using JNDI.

I was under impression that spring takes care of assigning the unused connections back to pool , looks like it is not happening for some reason.


Please let me know if you need more information and any pointers regarding this is much appreciated..

Thanks,
Vijay
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This isn't a Tomcat-specific issue. Moving to the Spring forum.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you need to give us an example of how you're interacting with Spring JDBC. The JDBCTemplate functions should not only be automatically releasing the connections, but automatically acquiring them. In other words, you shouldn't be using JNDI code to get them, that should be configured into your Spring application context. The save is true for the more complex Spring persistence functions as well.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tomcat Context:
--------------------

<Resource name="jdbc/myDs" auth="Container" type="javax.sql.DataSource"
username="dbuser" password="dbpwd" connectionProperties="includeSynonyms=true"
url="jdbc:oracle:thin:@dbhost:1521/SID"
driverClassName="oracle.jdbc.driver.OracleDriver" initialSize="5"
maxWait="5000" maxActive="120" maxIdle="5" validationQuery="select 1 from dual"
poolPreparedStatements="true" />

Spring Context:
-----------------


<jee:jndi-lookup id="dataSource" jndi-name="jdbc/myDs" />

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource" />
</bean>
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think that should be:


But what does your database Java code look like?
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how does 'java:comp/env/' help in minimizing the number of connections?

There is NO issue in getting data source and DB connection without that , currently REST service/tomcat is creating more connections than expected around 150 to 200.

I am not handling the connections or DB statements separately, it is a normal statement like jdbcTemplate.execute("SELECT 1 FROM DUAL") etc
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Because the canonical JNDI directory path for a Tomcat connection pool is rooted at java:comp/env. Without that qualification, Spring is resorting to defaults, and that's not really a good idea in most cases. If for no other reason than it makes you careless about JNDI syntax and can come back to haunt you in environments that are less co-operative.

Regardless, in cases such as jdbcTemplate.execute("SELECT 1 FROM DUAL"), the connection should be obtained, used, and freed entirely within the execute() method unless its part of a larger transactional context, in which case it should be freed at termination of the transactional context. Although I hope that in the case of actual queries you're using one of the Spring query methods, instead.

Still, if you think that you are leaking connections, I'd recommend that you set log level DEBUG on for the org.springframework loggers.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim,

Thank you very much for your time and inputs , I am currently working with DBA for getting more info and get back to you asap.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim,

This is my observation so far: (tested everything in local tomcat this time)

1)Started tomcat alone without deploying any service - every time 25 connection are getting created.
2)Tested after deploying one service - this time 30 connections are getting created.


I am seeing this behvioud consistently , do we mentioned this count somewhere in tomcat? any idea?
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
java:comp/env - I tried adding this JNDI but , No Luck
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
25 connections with no webapps deployed.
30 connections with one webapp deployed. And your initialSize is 5. Sounds suspicious.

Did you define your JDBC resource in a Context XML file or in the TOMCAT_HOME/conf/server.xml file? If it's in server.xml, I guess you're going to have to provide me with a copy of it because it sounds like you have either defined some other pool or maybe even 5 other pools.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I defined it in context.xml only
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
interesting - Now i deployed 2 services , count is 25+5+5 = 35

 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
problem with initialSize="5" only , when i tried with initialSize="1" it has created 6 with one service.

initialSize="0" ---> NIL
initialSize="1" -->5
initialSize="5"--->25
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just out of curiosity, try the following:


 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
40 connections got created with above configuration.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim,

I came to know that I need to move DS configuration from context.xml to server.xml from stackoverflow(forum) , but when i tried to access the resource in spring context using below code.it give me error saying "given datasource not found"

<jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/jdbc/MyDs" /> //This works for context.xml not server.xml

Do you know how to access DS from server.xml?
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Found it , please ignore

<ResourceLink name="jdbc/DatabaseName"
global="jdbc/DatabaseName"
type="javax.sql.DataSource"/>

But , is there any problem in having 'datasource' configuration in server.xml??
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You [i]should[/b] define the JDBC connection pool in server,xml if the pool is to be shared between multiple web applications. If only a single web application uses the pool, then the pool should be defined within the application's Context (in J2EE terms, the Server-Dependent Deployment Descriptor). And a webapp Context should not be defined within server.xml, for reasons that the Tomcat documentation explains.
 
Ranch Hand
Posts: 93
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tomcat8 + Oracle11g + ojdbc7.jar +jdk7

Being new to web development it took me 3 days to set up an oracle connection pool.

Here are some suggestions to consider:

"oracle.jdbc.driver.OracleDriver" has been deprecated. Use "oracle.jdbc.OracleDriver"

Change maxWait to maxWaitMillis

Change maxActive to maxTotal
 
reply
    Bookmark Topic Watch Topic
  • New Topic