Created attachment 29646 [details] Exception stack trace I am developing a Java EE web application in Eclipse Juno. I have configured Tomcat to use JDBC connection pool (org.apache.tomcat.jdbc.pool) along with PostgreSQL database. Here are the configurations in my project's META-INF/context.xml: <?xml version="1.0" encoding="UTF-8"?> <Context> <!-- Configuration for the Tomcat JDBC Connection Pool --> <Resource name="jdbc/someDB" type="javax.sql.DataSource" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/somedb" username="postgres" password="12345" maxActive="100" minIdle="10" initialSize="10" validatonQuery="SELECT 1" validationInterval="30000" removeAbandoned="true" removeAbandonedTimeout="60" abandonWhenPercentageFull="50" /> </Context> My application is deployed to Tomcat using Eclipse, and in Tomcat's context.xml an attribute reloadable is set to "true" to automatically reload the web application if a change is detected: <Context reloadable="true"> I have noticed that every time the above mentioned automatic reload is happening 10 more connections to PostgreSQL db is reserved (because in webapp's context.xml initialSize="10"). So after 10 changes a PSQLException is thrown: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already ... (full stack in attachment) If I manually restart Tomcat - everything is fine and just 10 connections are reserved. And I am closing all ResultSets, Statements/PrepareStatements, Connections in the finally block. Would be great if this issue could be fixed, so that it could be possible to develop with reloadable set to "true" and not cause pooling more connections every time the context is reloaded. P.S. Apache Tomcat 7.0.32 Java(TM) SE Runtime Environment (build 1.7.0_09-b05) Eclipse Juno Service Release 1 PostgreSQL version 9.1 DB driver: postgresql-9.1-902.jdbc4.jar
You are not closing your connection pool properly. You need to specify closeMethod="close". Without that the pool would not know when to close those 10 "minIdle" connections. The attribute is documented here: http://tomcat.apache.org/tomcat-7.0-doc/config/context.html#Resource_Definitions This is going to be close as INVALID. Just holding it open for a while as a chance to improve documentation: it would be better to add closeMethod to the examples in jndi-datasource-examples-howto.html
(In reply to comment #1) > You are not closing your connection pool properly. You need to specify > closeMethod="close". Without that the pool would not know when to close > those 10 "minIdle" connections. > > The attribute is documented here: > http://tomcat.apache.org/tomcat-7.0-doc/config/context. > html#Resource_Definitions > > > This is going to be close as INVALID. Just holding it open for a while as a > chance to improve documentation: it would be better to add closeMethod to > the examples in jndi-datasource-examples-howto.html Thank you so much for your help! I added the attribute closeMethod="close" to the Resource element in my /META-INFO/context.xml and it works! I asked about how to handle this issue on stackoverflow.com and nobody seems to know. So I'll post the answer to my question myself referencing this page and your help. Спасибо!
(In reply to comment #1) > You are not closing your connection pool properly. You need to specify > closeMethod="close". Without that the pool would not know when to close > those 10 "minIdle" connections. > > The attribute is documented here: > http://tomcat.apache.org/tomcat-7.0-doc/config/context. > html#Resource_Definitions > > > This is going to be close as INVALID. Just holding it open for a while as a > chance to improve documentation: it would be better to add closeMethod to > the examples in jndi-datasource-examples-howto.html This attribute is not available to Tomcat 6, is it? I checked the docs. Seems to be Tomcat 7 specific. I am using JDBC Pool happily with Tomcat 6 in production.
There looks to be a memory leak here. Stopping the context should make the connections eligible for GC but a reference is being retained by the PoolCleaner thread.
Confirmed. This is a memory leak bug in jdbc-pool. PoolCleaner instances are retaining references to the ConnectionPool preventing it from being GC'd.
This has been fixed in trunk and 7.0.x and will be included in 7.0.34 onwards.
(In reply to comment #6) > This has been fixed in trunk and 7.0.x and will be included in 7.0.34 > onwards. Wow it turned out to be a bug. Glad I unintentionally found it ) I will update my answer on StackOverflow.com Thank you for the clarification!
(In reply to comment #5) > Confirmed. This is a memory leak bug in jdbc-pool. PoolCleaner instances are > retaining references to the ConnectionPool preventing it from being GC'd. It still doesn't make the closeMethod call obsolete, does it?
As per the docs for closeMethod, that method exists solely to speed up the freeing of resources that would otherwise be freed by GC.
Hi. It seems like the issue initially described in this bug report is still present for the currently latest Tomcat version 7.0.50. Although now Tomcat sometimes manages to close additional connections after reloading, sometimes the number of connections are increased after one reload and then kept steady. But I still could reproduce the initially described issue (although again not that easy: it may be related to the frequency of successive reloads). Seems like it's just a matter of time, i.e. if Tomcat has enough time after reload, it manages the connection pool more or less as it should. As Mark mentioned in the "Comment 9" the closeMethod "speeds up the freeing of resources that would otherwise be freed by GC.", and it looks like the speed is the defining factor. When using the solution presented by Konstantin Kolinko (to use closeMethod="close") all WORKS just fine, and the number of connections reserved are kept STRICTLY as defined in the context.xml file. So it appears that using closeMethod="close" is the ONLY true way (at the moment) to avoid "PSQLException: FATAL: sorry, too many clients already" exception. I'll update my post at Stack Overflow (http://stackoverflow.com/questions/13595794/jdbc-connection-pool-runs-out-of-connections-when-context-reload-true-is-enabl) to reflect this situation.
There are two separate issues in this report and WONTFIX is not the correct status for either of them. The actual memory leak found while investingating this bug has been fixed in 7.0.34 onwards as per comments #4 to #6. The issue of the connections not being closed on reload is a result of the J2EE specification for JNDI resources and this part of the bug report is therefore invalid. I am restoring the state of this bug to fixed to reflect that the memory leak that did exist has been fixed. To expandon why the failure to immediately close connection after relod is invalid, the J2EE specification provides no mechanism for the container to tell the resource it is no longer required. Therefore all the container can do is clear references to the resource and wait for garbage collection (which will trigger the closure of the pool and the associated connections). Garbage collection occurs at times determined by the JVM so this is why it takes an indeterminate amount of time for connections to be closed after a context reload as a garbage collection may not occur for some time. Tomcat has added the Tomcat specific JNDI attribute closeMethod which can be used to trigger the explicit close of a JNDI resource when a context is stopped. If waiting for GC to clean up resources is not acceptable then simply use this parameter. Tomcat does not use this by default as it may have unexpected and unwanted side-effects for some JNDI resources. If you'd like to see a standard mechanism provided for telling JNDI resources that they are no loger required then you need to lobby the J2EE expert group.
(In reply to Mark Thomas from comment #11) > There are two separate issues in this report and WONTFIX is not the correct > status for either of them. > > The actual memory leak found while investigating this bug has been fixed in > 7.0.34 onwards as per comments #4 to #6. > > The issue of the connections not being closed on reload is a result of the > J2EE specification for JNDI resources and this part of the bug report is > therefore invalid. > > I am restoring the state of this bug to fixed to reflect that the memory > leak that did exist has been fixed. > > To expand on why the failure to immediately close connection after reload is > invalid, the J2EE specification provides no mechanism for the container to > tell the resource it is no longer required. Therefore all the container can > do is clear references to the resource and wait for garbage collection > (which will trigger the closure of the pool and the associated connections). > > Garbage collection occurs at times determined by the JVM so this is why it > takes an indeterminate amount of time for connections to be closed after a > context reload as a garbage collection may not occur for some time. > > Tomcat has added the Tomcat specific JNDI attribute closeMethod which can be > used to trigger the explicit close of a JNDI resource when a context is > stopped. If waiting for GC to clean up resources is not acceptable then > simply use this parameter. Tomcat does not use this by default as it may > have unexpected and unwanted side-effects for some JNDI resources. > > If you'd like to see a standard mechanism provided for telling JNDI > resources that they are no longer required then you need to lobby the J2EE > expert group. Thank you Mark for the explanation. I'll make a now third update to my Stack Overflow page and post this comment there. I knew it, it also had to do be about garbage collector. Again thanks!
(In reply to Mark Thomas from comment #11) > There are two separate issues in this report and WONTFIX is not the correct > status for either of them. > > The actual memory leak found while investingating this bug has been fixed in > 7.0.34 onwards as per comments #4 to #6. > > The issue of the connections not being closed on reload is a result of the > J2EE specification for JNDI resources and this part of the bug report is > therefore invalid. > > I am restoring the state of this bug to fixed to reflect that the memory > leak that did exist has been fixed. > > To expandon why the failure to immediately close connection after relod is > invalid, the J2EE specification provides no mechanism for the container to > tell the resource it is no longer required. Therefore all the container can > do is clear references to the resource and wait for garbage collection > (which will trigger the closure of the pool and the associated connections). > > Garbage collection occurs at times determined by the JVM so this is why it > takes an indeterminate amount of time for connections to be closed after a > context reload as a garbage collection may not occur for some time. > > Tomcat has added the Tomcat specific JNDI attribute closeMethod which can be > used to trigger the explicit close of a JNDI resource when a context is > stopped. If waiting for GC to clean up resources is not acceptable then > simply use this parameter. Tomcat does not use this by default as it may > have unexpected and unwanted side-effects for some JNDI resources. > > If you'd like to see a standard mechanism provided for telling JNDI > resources that they are no loger required then you need to lobby the J2EE > expert group. Hi, Mark. A user on the Stack Overflow asked (quote from here http://stackoverflow.com/questions/13595794/jdbc-connection-pool-runs-out-of-connections-when-context-reload-true-is-enabl/13631242?noredirect=1#comment44403937_13631242): "What is the "unexpected and unwanted side-effects for some JNDI resources" and does it apply to PostgreSQL connections?" Would you be so kind to give a brief answer?
Thanks for the answer on Stack Overflow.
Levan, you probably want to use such a listener: http://mo-tomcat-ext.sourceforge.net/user-guide.html#CloseableResourceListener
(In reply to Michael Osipov from comment #15) > Levan, you probably want to use such a listener: > http://mo-tomcat-ext.sourceforge.net/user-guide. > html#CloseableResourceListener Thank you, Michael, for the suggestion. I have updated my answer on the Stack Overflow to mention your solution as an alternative. P.S. Btw, there is a typo in the description of the filter on this page http://mo-tomcat-ext.sourceforge.net/user-guide.html: "Prevents memory leaks caused by left open resources during undepoyment." I mean the word "undeployment".
Oh thanks, I have checked spelling so often that I missed this one.