Tomcat Connection Pool configuration: DataSource type and "Too many connection" error

Question

I'm using the tomcat connection pool via JNDI resources.

In the context.xml:

<Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
          username="root" password="root" driverClassName="com.mysql.jdbc.Driver"
          maxActive="1000" maxIdle="100" maxWait="10000"
          url="jdbc:mysql://localhost:3306/mydatabase" 
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" />

In web.xml:

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/mydb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

From the java classes in which I need db connections, I do this lookup:

Context initContext = new InitialContext();
DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/mydb");

My first doubt is the DataSource type. Is it the same using javax.sql.DataSource or org.apache.tomcat.jdbc.pool.DataSource?

Moreover, sometimes I obtain a "Too many connections" error. I've read many stackoverflow question/answers about this, but I don't succeed in understanding where the problem could be.

I have followed the tomcat docs, and I close properly result sets, statements and connection.

EDIT

My tomcat version is 7.0.26. So there should be a bug (see link suggested by informatik01 user)

Solution 1

If you put the JDBC resource in the $CATALINA_HOME/conf/context.xml it loads the resource for every single webapp you have deployed. (Which can mean a huge number of connections) If you move that resource to META-INF/context.xml of your webapp it will only load when that specific webapp is deployed. http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html

It could also be that you have way too many maxActive and maxIdle.

Solution 2

javax.sql.DataSource is an interface and org.apache.tomcat.jdbc.pool.DataSource is a class. I am not sure if tomcat permits us to directly instantiate org.apache.tomcat.jdbc.pool.DataSource. If yes, you can use any of these.

The connection related error could be due to

maxActive="1000" maxIdle="100" maxWait="10000" in your tomcat configuration file.

Set it to maxActive="10" maxIdle="10" maxWait="10" - 10 number of active connections, 10 number of idle connections with a maximum 10 seconds wait time.

Comments

[a_horse_with_no_name]

I guess you mean "decreasing" the limits (not increasing).

[a_horse_with_no_name]

That's even worse. You want a lot less connections. So something like maxActive="50" but definitely not "unlimited"

[Ravindra Gullapalli]

I agree with you that in a production instance we should have much less number of active connections by properly returning the connections to the pool.

[a_horse_with_no_name]

Not only in production. In test and dev just as well.

[a_horse_with_no_name]

He/she should decrease the number in the pool. 1000 connections sounds terribly wrong.

[Stéphane]

agreed but that's not the question :-)

[Sefran2]

Have I to change only maxActive? Have I to add also the attributes for the abandoned connections?

[a_horse_with_no_name]

The question was how to cope with the "Too many connections" problem. And that should be be "solved" by simply increasing the number of allowed connections. Not even very busy websites need that many connections.

[Sefran2]

@RavindraGullapalli: For 10 seconds, maxWait must be equal to 10000.