7

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)

Sefran2
  • 3,578
  • 13
  • 71
  • 106
  • 1
    You need to adjust the `maxActive` to the number of allowed connections. –  Feb 07 '13 at 20:56
  • 1
    OP, if you have more than 1000 concurrent connections, you should be careful that your application is releasing connections back to the pool after they are done being used. 1000 concurrent connections is quite a lot. – mightyrick Feb 07 '13 at 21:09
  • 1
    Using maxActive=1000 is nonsensical. You're using a connection pool to avoid having that many db connections even if you do have 1000 concurrent users. The db is normally not isn't going to cope with that load any faster than having e.g. 100 concurrent connections – nos Feb 07 '13 at 21:52
  • 2
    In addition to the above comments, if the version of the Apache Tomcat you use is less than 7.0.34, you **may** find interesting the issue described here: http://stackoverflow.com/q/13595794/814702 – informatik01 Feb 07 '13 at 22:14
  • 1
    Unless you have a very, very busy website, you should investigate whey you try to open that many connections. I would suspect your code to not properly close (=return) the connections that have been taken from the pool. Set `maxActive=50` to start with and debug your application why that isn't enough. –  Feb 07 '13 at 22:33
  • @informatik01: my tomcat version is 7.0.26. I'll try the solution suggested in http://stackoverflow.com/q/13595794/814702 and, moreover, I'll set maxActive to 50 or less. – Sefran2 Feb 08 '13 at 07:58
  • @Cricket Good luck! By the way, considering that you use Tomcat with Tomcat JDBC pool, here is also a very useful article from the Tomcat Experts community: [Configuring jdbc-pool for high-concurrency](http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency) – informatik01 Feb 08 '13 at 16:32

4 Answers4

6

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.

user1681732
  • 97
  • 1
  • 8
1

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.

Ravindra Gullapalli
  • 9,049
  • 3
  • 48
  • 70
  • 1
    I guess you mean "*decreasing*" the limits (not increasing). –  Feb 07 '13 at 20:56
  • 2
    That's even worse. You want a lot **less** connections. So something like `maxActive="50"` but definitely not "unlimited" –  Feb 07 '13 at 21:21
  • 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. – Ravindra Gullapalli Feb 07 '13 at 21:25
  • Not only in production. In test and dev just as well. –  Feb 07 '13 at 21:32
  • Have I to change only `maxActive`? Have I to add also the attributes for the abandoned connections? – Sefran2 Feb 07 '13 at 21:47
  • @RavindraGullapalli: For 10 seconds, `maxWait` must be equal to 10000. – Sefran2 Feb 12 '13 at 11:21
0

You may need to increase the max connection on mysql , the default max is 151.

Stéphane
  • 867
  • 4
  • 7
  • 1
    He/she should **decrease** the number in the *pool*. 1000 connections sounds terribly wrong. –  Feb 07 '13 at 21:33
  • agreed but that's not the question :-) – Stéphane Feb 07 '13 at 21:36
  • 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. –  Feb 07 '13 at 22:32
0

Make sure you don't have a resource leak: Example java.sql.Connection not getting closed

Nestor Hernandez Loli
  • 1,412
  • 3
  • 21
  • 26