Tomcat Connection pool creating too many connections, stuck in sleep mode

[Ben Shoots]

I'm using Tomcat 7's connection pool with 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 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,
Ben

[Tim Holloway]

I heartily recommend NEVER ignoring exceptions. Dump the stacktrace to the log, but don't just drop them. Otherwise when things go mysteriously wrong, you won't have a clue.

There is a DBCP option that allows tracking connections. What it does is generate an Exception (with associated stacktrace) when you request a connection from the pool. This Exception is then attached to the Connection instead of being thrown. If the orphan connection cleanup service picks up an abandoned connection of this type, it prints the stacktrace, which tells you what part of the app is creating improperly-released connections.

The orphan connection problem is one reason I like the Spring Framework. It manages the grunt work, including ensuring that connections get returned to the pool.