Databases
StackOverflow2013
Postsdbo
POTomcat Connection pool creating too many connections, stuck in sleep mode
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTomcat Connection pool creating too many connections, stuck in sleep mode
    primarykey
    data
    text
    <p>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. </p> <p>See here for reference:</p> <pre><code>+----+------+-----------------+--------+---------+------+-------+------------------+ | 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 | </code></pre> <p>It creates exactly 10 for each request, which is the minIdle &amp; InitialSize attribute as seen below.</p> <p>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.</p> <pre><code>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) {} } </code></pre> <p>Here is my context.xml file:</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;Context&gt; &lt;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&amp;amp;useUnicode=true&amp;amp;characterEncoding=utf8"/&gt; &lt;WatchedResource&gt;WEB-INF/web.xml&lt;/WatchedResource&gt; &lt;WatchedResource&gt;META-INF/context.xml&lt;/WatchedResource&gt; &lt;/Context&gt; </code></pre> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGoran Jovic
    1. USmeatyowllegs
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. USGoran Jovic
      1. VTFavorite
    1. COIs your code in the try/catch throwing any exception? At the moment, you're just ignoring and possible output.
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. USMikaveli
    2. COThat's just copy pasted from apache's tomcat connection pool documentation. My application which has normal try catch statements isn't throwing any exceptions despite this problem occurring.
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. USmeatyowllegs
    3. COis this happening in your dev environment or anywhere where you have hot deploy and/or redeploy your war?
      singulars
      1. POTomcat Connection pool creating too many connections, stuck in sleep mode
      1. USAugusto
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload