Tomcat Connection pool creating too many connections, stuck in sleep mode
I'm using Tomcat 6.0.29, with Tomcat 7's connection pool and MySQL. Testing my application, it doesn't reuse anything from the pool, but ends up creating a new pool, to eventually where I cannot use the database because there are hundreds of sleeping connections in the pool when the max active size for the pool is set to 20.
See here for reference:
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+--------+---------+------+-------+------------------+
| 2 | root | localhost:51877 | dbname | Sleep | 9 | | NULL |
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 5 | root | localhost:49213 | dbname | Sleep | 21 | | NULL |
| 6 | root | localhost:53492 | dbname | Sleep | 21 | | NULL |
| 7 | root | localhost:46012 | dbname | Sleep | 21 | | NULL |
| 8 | root | localhost:34964 | dbname | Sleep | 21 | | NULL |
| 9 | root | localhost:52728 | dbname | Sleep | 21 | | NULL |
| 10 | root | localhost:43782 | dbname | Sleep | 21 | | NULL |
| 11 | root | localhost:38468 | dbname | Sleep | 21 | | NULL |
| 12 | root | localhost:48021 | dbname | Sleep | 21 | | NULL |
| 13 | root | localhost:54854 | dbname | Sleep | 21 | | NULL |
| 14 | root | localhost:41520 | dbname | Sleep | 21 | | NULL |
| 15 | root | localhost:38112 | dbname | Sleep | 13 | | NULL |
| 16 | root | localhost:39168 | dbname | Sleep | 13 | | NULL |
| 17 | root | localhost:40427 | dbname | Sleep | 13 | | NULL |
| 18 | root | localhost:58179 | dbname | Sleep | 13 | | NULL |
| 19 | root | localhost:40957 | dbname | Sleep | 13 | | NULL |
| 20 | root | localhost:45567 | dbname | Sleep | 13 | | NULL |
| 21 | root | localhost:48314 | dbname | Sleep | 13 | | NULL |
| 22 | root | localhost:34546 | dbname | Sleep | 13 | | NULL |
| 23 | root | localhost:44928 | dbname | Sleep | 13 | | NULL |
| 24 | root | localhost:57320 | dbname | Sleep | 13 | | NULL |
| 25 | root | localhost:54643 | dbname | Sleep | 29 | | NULL |
| 26 | root | localhost:49809 | dbname | Sleep | 29 | | NULL |
| 27 | root | localhost:60993 | dbname | Sleep | 29 | | NULL |
| 28 | root | localhost:36676 | dbname | Sleep | 29 | | NULL |
| 29 | root | localhost:53574 | dbname | Sleep | 29 | | NULL |
| 30 | root | localhost:45402 | dbname | Sleep | 29 | | NULL |
| 31 | root | localhost:37632 | dbname | Sleep | 29 | | NULL |
| 32 | root | localhost:56561 | dbname | Sleep | 29 | | NULL |
| 33 | root | localhost:34261 | dbname | Sleep | 29 | | NULL |
| 34 | root | localhost:55221 | dbname | Sleep | 29 | | NULL |
| 35 | root | localhost:39613 | dbname | Sleep | 15 | | NULL |
| 36 | root | localhost:52908 | dbname | Sleep | 15 | | NULL |
| 37 | root | localhost:56401 | dbname | Sleep | 15 | | NULL |
| 38 | root | localhost:44446 | dbname | Sleep | 15 | | NULL |
| 39 | root | localhost:57567 | dbname | Sleep | 15 | | NULL |
| 40 | root | localhost:56445 | dbname | Sleep | 15 | | NULL |
| 41 | root | localhost:39616 | dbname | Sleep | 15 | | NULL |
| 42 | root | localhost:49197 | dbname | Sleep | 15 | | NULL |
| 43 | root | localhost:59916 | dbname | Sleep | 15 | | NULL |
| 44 | root | localhost:37165 | dbname | Sleep | 15 | | NULL |
| 45 | root | localhost:45649 | dbname | Sleep | 1 | | NULL |
| 46 | root | localhost:55397 | dbname | Sleep | 1 | | NULL |
| 47 | root | localhost:34322 | dbname | Sleep | 1 | | NULL |
| 48 | root | localhost:54387 | dbname | Sleep | 1 | | NULL |
| 49 | root | localhost:55147 | dbname | Sleep | 1 | | NULL |
| 50 | root | localhost:47280 | dbname | Sleep | 1 | | NULL |
| 51 | root | localhost:56856 | dbname | Sleep | 1 | | NULL |
| 52 | root | localhost:58369 | dbname | Sleep | 1 | | NULL |
| 53 | root | localhost:33712 | dbname | Sleep | 1 | | NULL |
| 54 | root | localhost:44315 | dbname | Sleep | 1 | | NULL |
| 55 | root | localhost:54649 | dbname | Sleep | 14 | | NULL |
| 56 | root | localhost:41202 | dbname | Sleep | 14 | | NULL |
| 57 | root | localhost:59393 | dbname | Sleep | 14 | | NULL |
| 58 | root | localhost:38304 | dbname | Sleep | 14 | | NULL |
| 59 | root | localhost:34548 | dbname | Sleep | 14 | | NULL |
| 60 | root | localhost:49567 | dbname | Sleep | 14 | | NULL |
| 61 | root | localhost:48077 | dbname | Sleep | 14 | | NULL |
| 62 | root | localhost:48586 | dbname | Sleep | 14 | | NULL |
| 63 | root | localhost:45308 | dbname | Sleep | 14 | | NULL |
| 64 | root | localhost:43169 | dbname | Sleep | 14 | | NULL |
It creates exactly 10 for each request, which is the minIdle & InitialSize attribute as seen below.
Here is the sample test code embedded into a jsp page. The code is not the code in my application and just used to see if the issue was with my code, but the problem still persisted.
Context envCtx;
envCtx = (Context) new InitialContext().lookup("java:comp/env");
DataSource datasource = (DataSource) envCtx.lookup("jdbc/dbname");
Connection con = null;
try {
con = datasource.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from UserAccount");
int cnt = 1;
while (rs.next()) {
out.println((cnt++)+". Token:" +rs.getString("UserToken")+
" FirstName:"+rs.getString("FirstName")+" LastName:"+rs.getString("LastName"));
}
rs.close();
st.close();
} finally {
if (con!=null) try {con.close();}catch (Exception ignore) {}
}
Here is my context.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/dbname"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="20"
minIdle="10"
maxWait="10000"
initialSize="10"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors=
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username=""
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true&useUnicode=true&characterEncoding=utf8"/>
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>META-INF/context.xml</WatchedResource>
</Context>
I'm sure I can use removeAbandonedTimeout to a low number and it would purge all these sleeping connections, but that wouldn't fix the real problem would it? Does anyone know what I'm doing wrong? Thank you very much.
Answer
I don't have an environment to test this in, at the moment, however, I believe that you should be closing your Connection, Statement, and ResultSet after each query; if any of these leak, it could leave the Connection hanging in an idle (but not necessarily returned to the pool) state.
The Connection object you receive should actually be a sort of proxy from the pooling layer; calling close on it releases your "reservation" on that connection and returns it to the pool. (It will not necessarily close the underlying, actual database connection.)
Because it could be remaining open (usually will be), unclosed Statements or ResultSets could be interpreted by the pool layer as an indication of being still “busy.”
You may be able to inspect (e.g. debugger makes this easy) the Connection object to identify its state at run-time, to confirm this.
For simplicity (…) we used the following nasty little routine in the finally blocks after every database connection call: … finally { closeAll (rs, st, con); }, ensuring that they would fall out of context immediately.
/**
* Close a bunch of things carefully, ignoring exceptions. The
* “things” supported, thus far, are:
* <ul>
* <li>JDBC ResultSet</li>
* <li>JDBC Statement</li>
* <li>JDBC Connection</li>
* <li>Lock:s</li>
* </ul>
* <p>
* This is mostly meant for “finally” clauses.
*
* @param things A set of SQL statements, result sets, and database
* connections
*/
public static void closeAll (final Object... things) {
for (final Object thing : things) {
if (null != thing) {
try {
if (thing instanceof ResultSet) {
try {
((ResultSet) thing).close ();
} catch (final SQLException e) {
/* No Op */
}
}
if (thing instanceof Statement) {
try {
((Statement) thing).close ();
} catch (final SQLException e) {
/* No Op */
}
}
if (thing instanceof Connection) {
try {
((Connection) thing).close ();
} catch (final SQLException e) {
/* No Op */
}
}
if (thing instanceof Lock) {
try {
((Lock) thing).unlock ();
} catch (final IllegalMonitorStateException e) {
/* No Op */
}
}
} catch (final RuntimeException e) {
/* No Op */
}
}
}
}
This was just syntactic sugar to ensure that nobody forgot to put in the longer, uglier stanza of if (null != con) { try { con.close () } catch (SQLException e) {} } (usually repeated three times for ResultSet, Statement, and Connection); and removed the "visual noise" of what our formatter would turn into a full screen of incidental cleanup code on every block of code that touched the database.
(The Lock support in there was for some related, but nasty, deadlock states on potential exceptions, that didn't have much to do with the database at all, but we used in a similar way to reduce the line noise in some thread-synchronization code. This is from an MMO server that might have 4,000 active threads at a time trying to manipulate game objects and SQL tables.)