You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Milan Tomic <to...@yahoo.com> on 2012/11/21 11:36:29 UTC
DB Connection Pool
After 1 month of Tomcat 6.0.18 running, I have 50 opened JDBC connections, which should be closed, but they aren't. All connections have first or second stack trace:
TP-Processor22
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
java.io.BufferedInputStream.fill(BufferedInputStream.java:218)
java.io.BufferedInputStream.read1(BufferedInputStream.java:258)
java.io.BufferedInputStream.read(BufferedInputStream.java:317)
org.apache.jk.common.ChannelSocket.read(ChannelSocket.java:620)
org.apache.jk.common.ChannelSocket.receive(ChannelSocket.java:558)
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:685)
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
java.lang.Thread.run(Thread.java:662)
TP-Processor29
java.lang.Object.wait(Native Method)
java.lang.Object.wait(Object.java:485)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:662)
java.lang.Thread.run(Thread.java:662)
Those are my connection pool settings:
<Resource name="jdbc/myDS" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
connectionCacheProperties="{InitialLimit=3, MinLimit=3, MaxLimit=100, AbandonedConnectionTimeout=900, InactivityTimeout=1800, PropertyCheckInterval=60, ValidateConnection=true}"
connectionCachingEnabled="true"
user="myuser" password="mypass" factory="oracle.jdbc.pool.OracleDataSourceFactory"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@someip:1521:myDB"
/>
What am I doing wrong? I am closing DB connections from my Java code, but even if I fail to close, it should be automatically closed after some period (timeout)?
Thank you in advance,
Milan
RE: DB Connection Pool
Posted by "Propes, Barry L " <ba...@citi.com>.
I use 6.0.29, and there was a setting I had to change to keep some connections from hanging or timing out.
Although I use Tomcat's DBCP, and not OracleDataSource.
I think it was this first line here.
timeBetweenEvictionRunsMillis="-1"
minEvictableIdleTimeMillis="28800"
poolPreparedStatements="true"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="false
If you can go back to DBCP, check that...and why did you need to cast to Oracle's connection?
-----Original Message-----
From: Daniel Mikusa [mailto:dmikusa@vmware.com]
Sent: Wednesday, November 21, 2012 9:11 AM
To: Tomcat Users List
Subject: Re: DB Connection Pool
On Nov 21, 2012, at 9:45 AM, Milan Tomic wrote:
>
>
>> That's a really old version, you should strongly consider upgrading to the latest Tomcat 6.0.x or 7.0.x release.
> My SSO (old JOSSO version) works only with 6.0.18.
>
>> How are you determining this? Are you looking at open connections from your database? Are you looking at JMX stats for the connection pool?
> I'm using JavaMelody monitoring system.
Can you verify this through your database? Perhaps the monitoring tool is not showing the correct information?
>
>> These don't really seem to be indicating any sort of problem. The first looks like it is reading data from a socket, the second is simply waiting for something to do.
> Yes, but why there are 50 open old (some almost a month) DB connections (and still raising) on low traffic app?
This sounds like an application issue. Check to make sure it is not storing connections retrieved from the connection pool and check to make sure that you are properly closing connection (i.e. correctly using try..catch..finally).
If possible, maybe run FindBugs. That's a good way to catch errors like these.
> That is strange. There should be some parameter to auto close old connections. I have tried tunning many parameters, but without success.
Can't really say with OracleDataSource. With DBCP there is "removeAbandoned" / "removeAbandonedTimeout" / "logAbandoned" which can be used to reclaim connections that have been held by the application for too long.
>
>> This looks fairly normal with the exception that you are using an OracleDataSource. Not sure how much this list will be able to help you if the problem is with the connection pool. You'd probably need to contact Oracle.
> I was using DBCP, but I couldn't cast in my Java servlet code from DBCP to OracleConnection once when I get DBCP datasource from JNDI (getInnerConnection() also didn't helped), so I swithced from DBCP to OracleDataSource.
The documentation for DBCP say to do this...
1.) Set "accessToUnderlyingConnectionAllowed" to "true" (it defaults to false)
2.) Use this construct to get the connection. Do NOT close the underlying connection, only the original one!
Connection conn = ds.getConnection();
Connection dconn = ((DelegatingConnection) conn).getInnermostDelegate();
...
conn.close()
https://commons.apache.org/dbcp/configuration.html
I have not done this though, so I cannot be sure it will work for you.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: DB Connection Pool
Posted by Daniel Mikusa <dm...@vmware.com>.
On Nov 21, 2012, at 9:45 AM, Milan Tomic wrote:
>
>
>> That's a really old version, you should strongly consider upgrading to the latest Tomcat 6.0.x or 7.0.x release.
> My SSO (old JOSSO version) works only with 6.0.18.
>
>> How are you determining this? Are you looking at open connections from your database? Are you looking at JMX stats for the connection pool?
> I'm using JavaMelody monitoring system.
Can you verify this through your database? Perhaps the monitoring tool is not showing the correct information?
>
>> These don't really seem to be indicating any sort of problem. The first looks like it is reading data from a socket, the second is simply waiting for something to do.
> Yes, but why there are 50 open old (some almost a month) DB connections (and still raising) on low traffic app?
This sounds like an application issue. Check to make sure it is not storing connections retrieved from the connection pool and check to make sure that you are properly closing connection (i.e. correctly using try..catch..finally).
If possible, maybe run FindBugs. That's a good way to catch errors like these.
> That is strange. There should be some parameter to auto close old connections. I have tried tunning many parameters, but without success.
Can't really say with OracleDataSource. With DBCP there is "removeAbandoned" / "removeAbandonedTimeout" / "logAbandoned" which can be used to reclaim connections that have been held by the application for too long.
>
>> This looks fairly normal with the exception that you are using an OracleDataSource. Not sure how much this list will be able to help you if the problem is with the connection pool. You'd probably need to contact Oracle.
> I was using DBCP, but I couldn't cast in my Java servlet code from DBCP to OracleConnection once when I get DBCP datasource from JNDI (getInnerConnection() also didn't helped), so I swithced from DBCP to OracleDataSource.
The documentation for DBCP say to do this…
1.) Set "accessToUnderlyingConnectionAllowed" to "true" (it defaults to false)
2.) Use this construct to get the connection. Do NOT close the underlying connection, only the original one!
Connection conn = ds.getConnection();
Connection dconn = ((DelegatingConnection) conn).getInnermostDelegate();
...
conn.close()
https://commons.apache.org/dbcp/configuration.html
I have not done this though, so I cannot be sure it will work for you.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: DB Connection Pool
Posted by Milan Tomic <to...@yahoo.com>.
> That's a really old version, you should strongly consider upgrading to the latest Tomcat 6.0.x or 7.0.x release.
My SSO (old JOSSO version) works only with 6.0.18.
> How are you determining this? Are you looking at open connections from your database? Are you looking at JMX stats for the connection pool?
I'm using JavaMelody monitoring system.
> These don't really seem to be indicating any sort of problem. The first looks like it is reading data from a socket, the second is simply waiting for something to do.
Yes, but why there are 50 open old (some almost a month) DB connections (and still raising) on low traffic app? That is strange. There should be some parameter to auto close old connections. I have tried tunning many parameters, but without success.
> This looks fairly normal with the exception that you are using an OracleDataSource. Not sure how much this list will be able to help you if the problem is with the connection pool. You'd probably need to contact Oracle.
I was using DBCP, but I couldn't cast in my Java servlet code from DBCP to OracleConnection once when I get DBCP datasource from JNDI (getInnerConnection() also didn't helped), so I swithced from DBCP to OracleDataSource.
Best regards,
Milan
Re: DB Connection Pool
Posted by Daniel Mikusa <dm...@vmware.com>.
On Nov 21, 2012, at 5:36 AM, Milan Tomic wrote:
>
> After 1 month of Tomcat 6.0.18 running,
That's a really old version, you should strongly consider upgrading to the latest Tomcat 6.0.x or 7.0.x release.
> I have 50 opened JDBC connections, which should be closed, but they aren't.
How are you determining this? Are you looking at open connections from your database? Are you looking at JMX stats for the connection pool?
> All connections have first or second stack trace:
>
> TP-Processor22
> java.net.SocketInputStream.socketRead0(Native Method)
> java.net.SocketInputStream.read(SocketInputStream.java:129)
> java.io.BufferedInputStream.fill(BufferedInputStream.java:218)
> java.io.BufferedInputStream.read1(BufferedInputStream.java:258)
> java.io.BufferedInputStream.read(BufferedInputStream.java:317)
> org.apache.jk.common.ChannelSocket.read(ChannelSocket.java:620)
> org.apache.jk.common.ChannelSocket.receive(ChannelSocket.java:558)
> org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:685)
> org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
> java.lang.Thread.run(Thread.java:662)
>
> TP-Processor29
> java.lang.Object.wait(Native Method)
> java.lang.Object.wait(Object.java:485)
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:662)
> java.lang.Thread.run(Thread.java:662)
These don't really seem to be indicating any sort of problem. The first looks like it is reading data from a socket, the second is simply waiting for something to do.
>
> Those are my connection pool settings:
>
> <Resource name="jdbc/myDS" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
> connectionCacheProperties="{InitialLimit=3, MinLimit=3, MaxLimit=100, AbandonedConnectionTimeout=900, InactivityTimeout=1800, PropertyCheckInterval=60, ValidateConnection=true}"
> connectionCachingEnabled="true"
> user="myuser" password="mypass" factory="oracle.jdbc.pool.OracleDataSourceFactory"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@someip:1521:myDB"
> />
This looks fairly normal with the exception that you are using an OracleDataSource. Not sure how much this list will be able to help you if the problem is with the connection pool. You'd probably need to contact Oracle.
Dan
> What am I doing wrong? I am closing DB connections from my Java code, but even if I fail to close, it should be automatically closed after some period (timeout)?
>
> Thank you in advance,
> Milan
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: DB Connection Pool
Posted by Jeffrey Janner <Je...@PolyDyne.com>.
> -----Original Message-----
> From: Milan Tomic [mailto:tomicmilan@yahoo.com]
> Sent: Wednesday, November 21, 2012 4:36 AM
> To: users@tomcat.apache.org
> Subject: DB Connection Pool
>
>
> After 1 month of Tomcat 6.0.18 running, I have 50 opened JDBC
> connections, which should be closed, but they aren't. All connections
> have first or second stack trace:
>
> TP-Processor22
> java.net.SocketInputStream.socketRead0(Native Method)
> java.net.SocketInputStream.read(SocketInputStream.java:129)
> java.io.BufferedInputStream.fill(BufferedInputStream.java:218)
> java.io.BufferedInputStream.read1(BufferedInputStream.java:258)
> java.io.BufferedInputStream.read(BufferedInputStream.java:317)
> org.apache.jk.common.ChannelSocket.read(ChannelSocket.java:620)
> org.apache.jk.common.ChannelSocket.receive(ChannelSocket.java:558)
> org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java
> :685)
> org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket
> .java:889)
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPoo
> l.java:690)
> java.lang.Thread.run(Thread.java:662)
>
> TP-Processor29
> java.lang.Object.wait(Native Method)
> java.lang.Object.wait(Object.java:485)
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPoo
> l.java:662)
> java.lang.Thread.run(Thread.java:662)
>
> Those are my connection pool settings:
>
> <Resource name="jdbc/myDS" auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> connectionCacheProperties="{InitialLimit=3, MinLimit=3,
> MaxLimit=100, AbandonedConnectionTimeout=900, InactivityTimeout=1800,
> PropertyCheckInterval=60, ValidateConnection=true}"
> connectionCachingEnabled="true"
> user="myuser" password="mypass"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@someip:1521:myDB"
> />
>
> What am I doing wrong? I am closing DB connections from my Java code,
> but even if I fail to close, it should be automatically closed after
> some period (timeout)?
>
> Thank you in advance,
> Milan
What version of the Oracle ojdbc driver are you using?
If you are using the 11g drivers, you really want the latest.
There is an issue with abandoned connection cleanup that is fixed in the 11.2.03 drivers.
I am not sure if the issue exists or is fixed in the 10g drivers.
Also be sure you are using the version that matches your java release, ojdbc6 == Java6, ojdbc5 == Java5.
Which Oracle pooling mechanism are you using? The 10g version was deprecated in the 11g drivers in favor of a new pooling mechanism.
Finally, make sure that you understand the Oracle definition of the terms used for paramaters.
For example, a connection isn't considered abandoned unless it was checked out of the pool and not used. Once a query is started, a heartbeat is set and the connection must be closed, i.e. returned to the pool, in order to turn off the heartbeat. If the query fails, or the connection is never closed, the abandoned connection mechanism will not see it as abandoned.
In other words, make sure that all your connections are being properly closed after use. You must close the result set, query, and connection to do it properly.
Once in the pool, the inactivity timeout will take over and close any physical connections that are not being reused. I'm not absolutely sure what mechanism Oracle uses to supply connections, but I strongly suspect a round-robin queue, which means that you'll need 30 minutes of almost no activity before you see connections start to close physically.
If you suspect, as I do, that some connections are not being returned to the pool, the easiest way to determine which statements are not closing is to use the "Search Sessions" feature of Enterprise Manager to find sessions which have not be re-used in a long time. The last SQL statement it lists is most likely the culprit.
Jeff
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org