On Thu, Nov 6, 2014 at 2:36 AM, Vasily Kukhta <v.b.kuk...@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 <dmik...@pivotal.io> > wrote: > > > > > On Wed, Nov 5, 2014 at 9:13 AM, Vasily Kukhta <v.b.kuk...@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! > > > > > > > > > >
