Database connections count greater than that set by DBCP maxTotal

Question

I am running a java web application using Tomcat (version 8.0.43).

I moved the tomcat-dbcp.jar (which came with Tomcat8.0.43) to jdk/jre/lib/ext so that it's available to the jre.

I think that I am using the Tomcat DBCP (as opposed to Apache Commons DBCP) but as I understand from the documentation, the configuration parameters are the same as for Apache Commons DBCP, which are detailed here.

Accordingly, I set up the following Resource element for the connection pool in context.xml:

<Resource 
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
auth="Container" 
driverClassName="com.mysql.jdbc.Driver"  
logAbandoned="false" 
maxTotal="100" 
maxIdle="30" 
maxWaitMillis="10000" 
name="jdbc/[dbName]" 
password="${dbPassword}" 
removeAbandonedOnBorrow="true" 
removeAbandonedTimeout="60" 
type="javax.sql.DataSource" 
url="[dbUrl]autoReconnect=true" 
username="${dbUsername}" 
testWhileIdle="true" 
testOnBorrow="true" 
validationQuery="SELECT 1 AS dbcp_connection_test"/>

Lately, I have been getting an error in my logs:

MySQLNonTransientConnectionException: Too many connections

So I checked how many connections I have at the time of the error (via Amazon CloudWatch for RDS. Specifically "ConnectionCount") and it can reach as high as 150 connections. How is this possible if I have set my maxTotal connections to 100?

I am running my application on multiple instances. Can it be that the maxTotal attribute applies to each instance (eg: if I have the Connection Pool defined on 2 instances then my maxTotal = 100 + 100 = 200?)

It's as if the attributes that I set in the Resource element are ignored. Can it be that something is wrong with my setup? Am I not using the tomcat DBCP like I assumed?

In addition, if I look further down the StackTrack of the Exception that I quoted above, I see that

org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection

is called. I'm not sure if this means that I am using a different Connection Pool (DBCP2) from the one I assumed I'm using (Tomcat DBCP).

@Selaron Tomcat version 8.0.43 (I updated the question to include this). — theyuv, Mar 25 '19 at 14:45
Make sure you account only for "ESTABLISHED" connections only. — LMC, Mar 25 '19 at 14:51
@LuisMuñoz Where? In my `Resource` element attributes? Or when I count the number of connections? — theyuv, Mar 25 '19 at 15:01
I use the mysql query `show processlist` in addition, I use AWS RDS console and it shows the amount of connections the instance is using. Also, I know that it is active connections because my connection pool is throwing errors (it doesn't have any available connections). — theyuv, Mar 25 '19 at 16:42
It turns out that you are missing the [factory](https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JNDI_Factory_and_Type) required property on your pool definition. — LMC, Mar 25 '19 at 17:06
Yes, I see that. Thanks! But do you think this could be the cause for seeing a high connection count? I have been running the connection pool with these settings for months. The spike only started happening about a day ago. You'll notice also that the [docs](https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html) for Tomcat 8 don't have a `maxTotal` attribute. — theyuv, Mar 25 '19 at 17:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190646/discussion-between-theyuv-and-luis-munoz). — theyuv, Mar 25 '19 at 17:40
I was wrong, the amount of connections still surpass the `maxTotal` figure (even when it's summed across all instances) — theyuv, Mar 25 '19 at 19:26

LMC · Answer 1 · 2019-04-18T16:44:20.463

Can it be that the maxTotal attribute applies to each instance (eg: if I have the Connection Pool defined on 2 instances then my maxTotal = 100 + 100 = 200?)

That's correct since you have defined your <Resource> inside the context.xml file.
Add your resource inside the server.xml to make it available to all contexts and make sure you remove it from all context.xml files. Make note of the global attribute not present on your configuration

<GlobalNamingResources>
    <Resource
        global="jdbc/[dbName]"

        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        auth="Container" 
        driverClassName="com.mysql.jdbc.Driver"  
        logAbandoned="false" 
        maxTotal="100" 
        maxIdle="30" 
        maxWaitMillis="10000" 
        name="jdbc/[dbName]" 
        password="${dbPassword}" 
        removeAbandonedOnBorrow="true" 
        removeAbandonedTimeout="60" 
        type="javax.sql.DataSource" 
        url="[dbUrl]autoReconnect=true" 
        username="${dbUsername}" 
        testWhileIdle="true" 
        testOnBorrow="true" 
        validationQuery="SELECT 1 AS dbcp_connection_test"/>
</GlobalNamingResources>

To keep an eye on:

testOnBorrow="true" causes a performance penalty that is not always worth it.
testWhileIdle="true" is related to timeBetweenEvictionRunsMillis which is 5 seconds by default. Default value could be a too short period in some cases.
Verify AWS RDS quota is not exceeded.

1) The number of connection still climbed higher than the sum of `MaxTotal`'s for all instances. 2) I am not familiar with the `global` attribute, can you provide a link to documentation where that attribute is described? — theyuv, Apr 17 '19 at 12:23
1) check the source IP of the connection to make sure all come from your app. I suggest to count ESTABLISHED connections at the app host and compare. 2) [GloblaNamingResources](https://tomcat.apache.org/tomcat-8.0-doc/config/globalresources.html) — LMC, Apr 17 '19 at 13:04

Database connections count greater than that set by DBCP maxTotal

1 Answers1