You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Vasily Kukhta <v....@gmail.com> on 2014/11/05 15:13:47 UTC
Tomcat JDBC pool - too many connections in TIME_WAIT state
Hello all!
I have developed an application using Tomcat JDBC pool. Everything is fine
except that the pool leaves hundreds of TCP connections in TIME_WAIT state,
which kills the server sooner or later... Could you please suggest what to
fix, my configuration is below:
PoolProperties pp = new PoolProperties();
String connprops =
"oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;oracle.net.READ_TIMEOUT=3000";
pp.setUsername(user);
pp.setPassword(pass);
pp.setConnectionProperties(connprops);
pp.setDriverClassName("oracle.jdbc.OracleDriver");
pp.setTestOnBorrow(true);
pp.setTestOnConnect(true);
pp.setTestWhileIdle(true);
pp.setMaxWait(1000);
pp.setMinEvictableIdleTimeMillis(10000);
pp.setTimeBetweenEvictionRunsMillis(5000);
pp.setValidationInterval(10000);
pp.setValidationQuery("SELECT 1 FROM DUAL");
pp.setRemoveAbandoned(true);
pp.setRemoveAbandonedTimeout(5);
pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
dataSource = new DataSource();
dataSource.setPoolProperties(pp);
Thank you in advance!
Re: Tomcat JDBC pool - too many connections in TIME_WAIT state
Posted by Daniel Mikusa <dm...@pivotal.io>.
On Thu, Nov 6, 2014 at 2:36 AM, Vasily Kukhta <v....@gmail.com> wrote:
> I have received additional details - the application starts getting
> "java.sql.SQLException: Listener refused the connection with the following
> error: ORA-12519, TNS:no appropriate service handler found", although the
> amount of listeners in the DB is large enough. I have some concerns about
> the "removeAbandonedTimeout" property, it is set to 5 seconds now. Maybe
> the pool abandones every connection after 5 seconds, opens a new
> connection, and the previous connection goes to TIME_WAIT status consuming
> server resources?
>
The removeAbandonedTimeout will take effect when your application takes a
connection from the pool and holds it for more than 5 seconds. This would
happen for things like a long running query (runs more than 5s) or if your
app's not properly closing the connection.
It's easy enough to see if this is happening too often, because you can
simply enable logAbandoned (i.e. set it to true) and the pool will log a
stack trace when it detects an abandoned connection.
Dan
>
> Thank you!
>
>
> 2014-11-05 23:15 GMT+03:00 Filip Hanik <fi...@hanik.com>:
>
> > this is part of the TCP lifecycle, you can adjust this timeout yourself
> on
> > the Operating system level
> >
> >
> >
> http://www.cs.northwestern.edu/~agupta/cs340/project2/TCPIP_State_Transition_Diagram.pdf
> >
> > cat /proc/sys/net/ipv4/tcp_fin_timeoutecho 15 >
> > /proc/sys/net/ipv4/tcp_fin_timeout
> >
> >
> > On Wed, Nov 5, 2014 at 7:36 AM, Daniel Mikusa <dm...@pivotal.io>
> wrote:
> >
> > > On Wed, Nov 5, 2014 at 9:13 AM, Vasily Kukhta <v....@gmail.com>
> > > wrote:
> > >
> > > > Hello all!
> > > >
> > > > I have developed an application using Tomcat JDBC pool. Everything is
> > > fine
> > > > except that the pool leaves hundreds of TCP connections in TIME_WAIT
> > > state,
> > > >
> > >
> > > I have to ask, but are you sure it's the pool? TCP connections in the
> > > TIME_WAIT state would indicate that a connection was closed. Given
> that
> > > the job of the pool is to keep the connections open and reuse them, it
> > just
> > > seems a little odd.
> > >
> > >
> > > > which kills the server sooner or later... Could you please suggest
> what
> > > to
> > > > fix, my configuration is below:
> > > >
> > > > PoolProperties pp = new PoolProperties();
> > > >
> > > > String connprops =
> > > >
> > > >
> > >
> >
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;oracle.net.READ_TIMEOUT=3000";
> > > >
> > > > pp.setUsername(user);
> > > > pp.setPassword(pass);
> > > > pp.setConnectionProperties(connprops);
> > > >
> > > > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> > > >
> > > > pp.setTestOnBorrow(true);
> > > > pp.setTestOnConnect(true);
> > > >
> > > > pp.setTestWhileIdle(true);
> > > >
> > > > pp.setMaxWait(1000);
> > > > pp.setMinEvictableIdleTimeMillis(10000);
> > > > pp.setTimeBetweenEvictionRunsMillis(5000);
> > > >
> > > > pp.setValidationInterval(10000);
> > > > pp.setValidationQuery("SELECT 1 FROM DUAL");
> > > >
> > > > pp.setRemoveAbandoned(true);
> > > > pp.setRemoveAbandonedTimeout(5);
> > > >
> > > >
> > > >
> > >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > > > dataSource = new DataSource();
> > > > dataSource.setPoolProperties(pp);
> > > >
> > >
> > > Nothing is jumping out at me as incorrect. Maybe try without the
> > > connection properties (i.e. the driver level timeouts)? Maybe try
> > > increasing the log level for "org.apache.tomcat.jdbc.pool" to FINEST or
> > > DEBUG. That might generate some additional logging to show why the
> > > connections are being closed.
> > >
> > > Also, check that your server is not timing out the connection, perhaps
> > due
> > > to a server side limit. I've see this happen a lot. Although it seems
> > > unlikely, it's probably also worth checking that there's no firewall or
> > > network device that could be closing the connections.
> > >
> > > Dan
> > >
> > >
> > > > Thank you in advance!
> > > >
> > >
> >
>
Re: Tomcat JDBC pool - too many connections in TIME_WAIT state
Posted by chris derham <ch...@derham.me.uk>.
On 6 November 2014 05:36, Vasily Kukhta <v....@gmail.com> wrote:
> I have received additional details - the application starts getting
> "java.sql.SQLException: Listener refused the connection with the following
> error: ORA-12519, TNS:no appropriate service handler found", although the
> amount of listeners in the DB is large enough. I have some concerns about
> the "removeAbandonedTimeout" property, it is set to 5 seconds now. Maybe
> the pool abandones every connection after 5 seconds, opens a new
> connection, and the previous connection goes to TIME_WAIT status consuming
> server resources?
We hit a similar problem 5-6 years ago with Oracle. We found that the
pool would close connections from client end successfully. However
Oracle has a background thread thread that marks server end
connections, and this was a little slow. Until this had closed the
connection, and told TNSListener, TNSListener wouldn't allow new
connections.
Our fix was to increase the number of connections allowed, and the
problem went away
HTH
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat JDBC pool - too many connections in TIME_WAIT state
Posted by Vasily Kukhta <v....@gmail.com>.
I have received additional details - the application starts getting
"java.sql.SQLException: Listener refused the connection with the following
error: ORA-12519, TNS:no appropriate service handler found", although the
amount of listeners in the DB is large enough. I have some concerns about
the "removeAbandonedTimeout" property, it is set to 5 seconds now. Maybe
the pool abandones every connection after 5 seconds, opens a new
connection, and the previous connection goes to TIME_WAIT status consuming
server resources?
Thank you!
2014-11-05 23:15 GMT+03:00 Filip Hanik <fi...@hanik.com>:
> this is part of the TCP lifecycle, you can adjust this timeout yourself on
> the Operating system level
>
>
> http://www.cs.northwestern.edu/~agupta/cs340/project2/TCPIP_State_Transition_Diagram.pdf
>
> cat /proc/sys/net/ipv4/tcp_fin_timeoutecho 15 >
> /proc/sys/net/ipv4/tcp_fin_timeout
>
>
> On Wed, Nov 5, 2014 at 7:36 AM, Daniel Mikusa <dm...@pivotal.io> wrote:
>
> > On Wed, Nov 5, 2014 at 9:13 AM, Vasily Kukhta <v....@gmail.com>
> > wrote:
> >
> > > Hello all!
> > >
> > > I have developed an application using Tomcat JDBC pool. Everything is
> > fine
> > > except that the pool leaves hundreds of TCP connections in TIME_WAIT
> > state,
> > >
> >
> > I have to ask, but are you sure it's the pool? TCP connections in the
> > TIME_WAIT state would indicate that a connection was closed. Given that
> > the job of the pool is to keep the connections open and reuse them, it
> just
> > seems a little odd.
> >
> >
> > > which kills the server sooner or later... Could you please suggest what
> > to
> > > fix, my configuration is below:
> > >
> > > PoolProperties pp = new PoolProperties();
> > >
> > > String connprops =
> > >
> > >
> >
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;oracle.net.READ_TIMEOUT=3000";
> > >
> > > pp.setUsername(user);
> > > pp.setPassword(pass);
> > > pp.setConnectionProperties(connprops);
> > >
> > > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> > >
> > > pp.setTestOnBorrow(true);
> > > pp.setTestOnConnect(true);
> > >
> > > pp.setTestWhileIdle(true);
> > >
> > > pp.setMaxWait(1000);
> > > pp.setMinEvictableIdleTimeMillis(10000);
> > > pp.setTimeBetweenEvictionRunsMillis(5000);
> > >
> > > pp.setValidationInterval(10000);
> > > pp.setValidationQuery("SELECT 1 FROM DUAL");
> > >
> > > pp.setRemoveAbandoned(true);
> > > pp.setRemoveAbandonedTimeout(5);
> > >
> > >
> > >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > > dataSource = new DataSource();
> > > dataSource.setPoolProperties(pp);
> > >
> >
> > Nothing is jumping out at me as incorrect. Maybe try without the
> > connection properties (i.e. the driver level timeouts)? Maybe try
> > increasing the log level for "org.apache.tomcat.jdbc.pool" to FINEST or
> > DEBUG. That might generate some additional logging to show why the
> > connections are being closed.
> >
> > Also, check that your server is not timing out the connection, perhaps
> due
> > to a server side limit. I've see this happen a lot. Although it seems
> > unlikely, it's probably also worth checking that there's no firewall or
> > network device that could be closing the connections.
> >
> > Dan
> >
> >
> > > Thank you in advance!
> > >
> >
>
Re: Tomcat JDBC pool - too many connections in TIME_WAIT state
Posted by Filip Hanik <fi...@hanik.com>.
this is part of the TCP lifecycle, you can adjust this timeout yourself on
the Operating system level
http://www.cs.northwestern.edu/~agupta/cs340/project2/TCPIP_State_Transition_Diagram.pdf
cat /proc/sys/net/ipv4/tcp_fin_timeoutecho 15 >
/proc/sys/net/ipv4/tcp_fin_timeout
On Wed, Nov 5, 2014 at 7:36 AM, Daniel Mikusa <dm...@pivotal.io> wrote:
> On Wed, Nov 5, 2014 at 9:13 AM, Vasily Kukhta <v....@gmail.com>
> wrote:
>
> > Hello all!
> >
> > I have developed an application using Tomcat JDBC pool. Everything is
> fine
> > except that the pool leaves hundreds of TCP connections in TIME_WAIT
> state,
> >
>
> I have to ask, but are you sure it's the pool? TCP connections in the
> TIME_WAIT state would indicate that a connection was closed. Given that
> the job of the pool is to keep the connections open and reuse them, it just
> seems a little odd.
>
>
> > which kills the server sooner or later... Could you please suggest what
> to
> > fix, my configuration is below:
> >
> > PoolProperties pp = new PoolProperties();
> >
> > String connprops =
> >
> >
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;oracle.net.READ_TIMEOUT=3000";
> >
> > pp.setUsername(user);
> > pp.setPassword(pass);
> > pp.setConnectionProperties(connprops);
> >
> > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> >
> > pp.setTestOnBorrow(true);
> > pp.setTestOnConnect(true);
> >
> > pp.setTestWhileIdle(true);
> >
> > pp.setMaxWait(1000);
> > pp.setMinEvictableIdleTimeMillis(10000);
> > pp.setTimeBetweenEvictionRunsMillis(5000);
> >
> > pp.setValidationInterval(10000);
> > pp.setValidationQuery("SELECT 1 FROM DUAL");
> >
> > pp.setRemoveAbandoned(true);
> > pp.setRemoveAbandonedTimeout(5);
> >
> >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > dataSource = new DataSource();
> > dataSource.setPoolProperties(pp);
> >
>
> Nothing is jumping out at me as incorrect. Maybe try without the
> connection properties (i.e. the driver level timeouts)? Maybe try
> increasing the log level for "org.apache.tomcat.jdbc.pool" to FINEST or
> DEBUG. That might generate some additional logging to show why the
> connections are being closed.
>
> Also, check that your server is not timing out the connection, perhaps due
> to a server side limit. I've see this happen a lot. Although it seems
> unlikely, it's probably also worth checking that there's no firewall or
> network device that could be closing the connections.
>
> Dan
>
>
> > Thank you in advance!
> >
>
Re: Tomcat JDBC pool - too many connections in TIME_WAIT state
Posted by Daniel Mikusa <dm...@pivotal.io>.
On Wed, Nov 5, 2014 at 9:13 AM, Vasily Kukhta <v....@gmail.com> wrote:
> Hello all!
>
> I have developed an application using Tomcat JDBC pool. Everything is fine
> except that the pool leaves hundreds of TCP connections in TIME_WAIT state,
>
I have to ask, but are you sure it's the pool? TCP connections in the
TIME_WAIT state would indicate that a connection was closed. Given that
the job of the pool is to keep the connections open and reuse them, it just
seems a little odd.
> which kills the server sooner or later... Could you please suggest what to
> fix, my configuration is below:
>
> PoolProperties pp = new PoolProperties();
>
> String connprops =
>
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;oracle.net.READ_TIMEOUT=3000";
>
> pp.setUsername(user);
> pp.setPassword(pass);
> pp.setConnectionProperties(connprops);
>
> pp.setDriverClassName("oracle.jdbc.OracleDriver");
>
> pp.setTestOnBorrow(true);
> pp.setTestOnConnect(true);
>
> pp.setTestWhileIdle(true);
>
> pp.setMaxWait(1000);
> pp.setMinEvictableIdleTimeMillis(10000);
> pp.setTimeBetweenEvictionRunsMillis(5000);
>
> pp.setValidationInterval(10000);
> pp.setValidationQuery("SELECT 1 FROM DUAL");
>
> pp.setRemoveAbandoned(true);
> pp.setRemoveAbandonedTimeout(5);
>
>
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> dataSource = new DataSource();
> dataSource.setPoolProperties(pp);
>
Nothing is jumping out at me as incorrect. Maybe try without the
connection properties (i.e. the driver level timeouts)? Maybe try
increasing the log level for "org.apache.tomcat.jdbc.pool" to FINEST or
DEBUG. That might generate some additional logging to show why the
connections are being closed.
Also, check that your server is not timing out the connection, perhaps due
to a server side limit. I've see this happen a lot. Although it seems
unlikely, it's probably also worth checking that there's no firewall or
network device that could be closing the connections.
Dan
> Thank you in advance!
>