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/07/21 17:05:57 UTC
Query timeouts using tomcat jdbc pool
Hello, dear tomcat users!
I am developing high-load application using tomcat jdbc connection pool and
Oracle database. It is very important to ensure my app to have very small
DB query timeouts (no longer than 3 seconds) to prevent long-running
queries or database slowness from blocking all my application. To simulate
long-running queries I have put the DB in QUIESCE state using ALTER SYSTEM
QUIESCE RESTRICTED statement.
But it looks like the timeout values have no impact - when i begin to test
my application, it hangs...
Here is my jdbc pool configuration:
String connprops =
"oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
+ "oracle.net.READ_TIMEOUT=3000";
pp.setConnectionProperties(connprops);
pp.setDriverClassName("oracle.jdbc.OracleDriver");
pp.setTestOnBorrow(true);
pp.setTestOnConnect(true);
pp.setTestOnReturn(true);
pp.setTestWhileIdle(true);
pp.setMaxWait(2000);
pp.setMinEvictableIdleTimeMillis(20000);
pp.setTimeBetweenEvictionRunsMillis(20000);
pp.setValidationInterval(3000);
pp.setValidationQuery("SELECT 1 FROM DUAL");
pp.setMaxAge(3000);
pp.setRemoveAbandoned(true);
pp.setRemoveAbandonedTimeout(3);
pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
dataSource = new DataSource();
dataSource.setPoolProperties(pp);
That's how i work with connections (pretty simple):
Connection conn = dataSource.getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(/*some select query*/);
if (rs.next()) {
result = rs.getInt(1);
/*process the result*/
}
rs.close();
stmt.close();
conn.close();
}
catch(Exception e) {
logger.error("Exception: " + e.getMessage(), e);
}finally {
if (conn != null) {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
conn.close();
}
}
Any ideas?
Thanks in advance!
Re: Query timeouts using tomcat jdbc pool
Posted by Filip Hanik <fi...@hanik.com>.
maxAge is not a timeout setting. It simply means the connection gets
retired(closed) instead of returned to the pool after a certain amount of
time
timeBetweenEvictionRunsMillis is not a timeout either. It is the interval
that the thread checks for timeouts, but not query, connection checkout
time out.
removeAbandonedTimeout - is the timeout you're looking for. This is the
timeout for a connection usage. That is the time between the
DataSource.getConnection() call and the Connection.close() call.
And whether an exception is thrown here, depends on the driver. the pool
simply calls close() on the driver connection, if that yields an exception
if another thread is executing a query or not, depends on the driver itself.
See
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
On Wed, Jul 23, 2014 at 10:56 AM, Vasily Kukhta <v....@gmail.com>
wrote:
> So, it means that if the timeout is detected not using setQueryTimeout
> method, but by the tomcat pool settings (setMaxAge or
> setTimeBetweenEvictionRunsMillis), it means that no exception can be thrown
> at all?
>
> Vasily
>
>
>
> 2014-07-23 12:14 GMT+04:00 Filip Hanik <fi...@hanik.com>:
>
> > Vasily, the exception depends on where the timeout occurs.
> >
> > If the timeout is triggered by the driver, because you hit the
> > setQueryTimeout limit
> >
> >
> http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
> > then yes, as per javadoc, it is up to the JDBC driver to throw an
> > exception.
> >
> >
> > Filip
> >
> >
> >
> >
> > On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta <v....@gmail.com>
> > wrote:
> >
> > > Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
> > > working properly now. Another problem has appeared: although the
> timeout
> > is
> > > handled correctly, no exception is thrown. I thought an SQLException
> > would
> > > be thrown if query takes too long. Maybe pool does not throw exceptions
> > at
> > > all in that situation?
> > >
> > > Than you!
> > >
> > >
> > > 2014-07-21 20:40 GMT+04:00 Daniel Mikusa <dm...@gopivotal.com>:
> > >
> > > > On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <
> v.b.kukhta@gmail.com>
> > > > wrote:
> > > >
> > > > > Hello, dear tomcat users!
> > > > >
> > > > > I am developing high-load application using tomcat jdbc connection
> > pool
> > > > and
> > > > > Oracle database. It is very important to ensure my app to have very
> > > small
> > > > > DB query timeouts (no longer than 3 seconds) to prevent
> long-running
> > > > > queries or database slowness from blocking all my application. To
> > > > simulate
> > > > > long-running queries I have put the DB in QUIESCE state using ALTER
> > > > SYSTEM
> > > > > QUIESCE RESTRICTED statement.
> > > > >
> > > > > But it looks like the timeout values have no impact - when i begin
> to
> > > > test
> > > > > my application, it hangs...
> > > > >
> > > >
> > > > Have you taken thread dumps of your application when it "hangs"?
> What
> > do
> > > > these show your threads are doing?
> > > >
> > > >
> > > > >
> > > > > Here is my jdbc pool configuration:
> > > > >
> > > > > String connprops =
> > > > > "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
> > > > > + "oracle.net.READ_TIMEOUT=3000";
> > > > >
> > > > >
> > > > > pp.setConnectionProperties(connprops);
> > > > >
> > > > > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> > > > >
> > > > > pp.setTestOnBorrow(true);
> > > > > pp.setTestOnConnect(true);
> > > >
> > > > pp.setTestOnReturn(true);
> > > > > pp.setTestWhileIdle(true);
> > > > >
> > > >
> > > > It's probably not necessary to have all of these enabled. I usually
> > only
> > > > see testOnBorrow and testWhileIdle enabled. TestOnReturn is almost
> > > > worthless, in my opinion. TestOnConnect might be helpful to catch
> > > > connection issues at boot, but generally a connection that is just
> > > created
> > > > should be good unless you have configuration problems.
> > > >
> > > >
> > > > > pp.setMaxWait(2000);
> > > > > pp.setMinEvictableIdleTimeMillis(20000);
> > > > >
> > > >
> > > >
> > > > > pp.setTimeBetweenEvictionRunsMillis(20000);
> > > > >
> > > >
> > > > This might be high given your aggressive settings for
> > > > removeAbandonedTimeout. The timeBetweenEvictionRunsMillis setting
> > > dictates
> > > > how often the pool looks for idle connections, abandoned connections,
> > and
> > > > how often it validates idle connections. If you set this to 20 secs,
> > > using
> > > > an abandoned timeout less than 20 seconds is probably not going to be
> > > > accurate.
> > > >
> > > > For example, if the cleaner thread runs and your application has been
> > > > holding a connection for 2999ms, it won't consider the connection
> > > > abandoned. However the cleaner thread won't run again for another 20
> > > secs
> > > > (based on the config value you used), thus the application can
> continue
> > > > using that connection for way over the abandoned timeout you've
> > > configured.
> > > >
> > > >
> > > > > pp.setValidationInterval(3000);
> > > > > pp.setValidationQuery("SELECT 1 FROM DUAL");
> > > > >
> > > >
> > > > Seems OK.
> > > >
> > > >
> > > > >
> > > > > pp.setMaxAge(3000);
> > > > >
> > > >
> > > > This seems pretty short and might limit how much pooling actually
> > occurs.
> > > > Also, since this is enforced when you return the connection to the
> > pool
> > > > it's not going to help with your current issue. What are you trying
> to
> > > > accomplish by setting this value so low?
> > > >
> > > >
> > > > > pp.setRemoveAbandoned(true);
> > > > > pp.setRemoveAbandonedTimeout(3);
> > > > >
> > > >
> > > > This is a pretty low value for abandoned timeout. If you're hoping
> to
> > > use
> > > > this to limit how long a query can executed, it's probably not the
> best
> > > > approach. This will limit the amount of time that a connection can be
> > > > checked out of the connection pool, and unless you also configure
> > > > the ResetAbandonedTimer interceptor, it's going to limit the total
> time
> > > > your application has to use the connection. If you configure the
> > > > ResetAbandonedTimer interceptor, it will function closer to the use
> > case
> > > > you've described but it's probably not the most efficient way to
> > > accomplish
> > > > this task.
> > > >
> > > > Generally you'd set the remove abandoned timeout to reclaim
> connections
> > > > that were not properly closed, not to reclaim connections where a
> query
> > > is
> > > > stuck or running for a long time. I think the jdbc driver can more
> > > easily
> > > > handle killing stuck / long running queries. See next comment.
> > > >
> > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > > > >
> > > >
> > > > I'm a little surprised this isn't working for you. By setting the
> > > > statement's query timeout, you're telling the jdbc driver to limit
> how
> > > long
> > > > each query can run. If a query runs longer than 3 seconds, you
> should
> > > get
> > > > an SQLTimeoutException. Otherwise there's an issue / limitation with
> > > your
> > > > jdbc driver.
> > > >
> > > > I would suggest setting up a test without the connection pool to
> > validate
> > > > that the jdbc driver and your method for slowing down the database is
> > > > working correctly. In theory this test should be something like
> make a
> > > > connection, set the query timeout, run a query and wait for the
> > > exception.
> > > > If that does not timeout, then it's definitely not going to timeout
> > with
> > > > the connection pool because the jdbc driver handles the actual timing
> > out
> > > > of the request.
> > > >
> > > > If your test works, then you might want to look and see if anything
> > else
> > > is
> > > > setting the timeout on the statement object. The interceptor should
> > set
> > > > the value when a new statement object is created. If something else
> > sets
> > > > the timeout, it would override the value set by the interceptor.
> > > >
> > > > An alternative and less aggressive approach to all of this might be
> to
> > > use
> > > > the SlowQueryReport or SlowQueryReportJmx interceptors. These will
> > watch
> > > > for slow queries (queries that take longer than the threshold you
> > define
> > > to
> > > > execute) and report them. It does not actively close or clean up
> long
> > > > running connections, but it does provide you with a lot of helpful
> > > > information about the problem when a query does take a while to run.
> > > >
> > > > Dan
> > > >
> > > >
> > > >
> > > > > dataSource = new DataSource();
> > > > > dataSource.setPoolProperties(pp);
> > > > >
> > > > >
> > > > > That's how i work with connections (pretty simple):
> > > > >
> > > > > Connection conn = dataSource.getConnection();
> > > > > Statement stmt = null;
> > > > > ResultSet rs = null;
> > > > >
> > > > > try {
> > > > > stmt = conn.createStatement();
> > > > >
> > > > > rs = stmt.executeQuery(/*some select query*/);
> > > > >
> > > > >
> > > > > if (rs.next()) {
> > > > >
> > > > > result = rs.getInt(1);
> > > > >
> > > > > /*process the result*/
> > > > >
> > > > > }
> > > > >
> > > > > rs.close();
> > > > > stmt.close();
> > > > > conn.close();
> > > > >
> > > > > }
> > > > > catch(Exception e) {
> > > > > logger.error("Exception: " + e.getMessage(), e);
> > > > > }finally {
> > > > > if (conn != null) {
> > > > >
> > > > > if(rs!=null)
> > > > > rs.close();
> > > > > if(stmt!=null)
> > > > > stmt.close();
> > > > > conn.close();
> > > > >
> > > > > }
> > > > > }
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks in advance!
> > > > >
> > > >
> > >
> >
>
Re: Query timeouts using tomcat jdbc pool
Posted by Vasily Kukhta <v....@gmail.com>.
So, it means that if the timeout is detected not using setQueryTimeout
method, but by the tomcat pool settings (setMaxAge or
setTimeBetweenEvictionRunsMillis), it means that no exception can be thrown
at all?
Vasily
2014-07-23 12:14 GMT+04:00 Filip Hanik <fi...@hanik.com>:
> Vasily, the exception depends on where the timeout occurs.
>
> If the timeout is triggered by the driver, because you hit the
> setQueryTimeout limit
>
> http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
> then yes, as per javadoc, it is up to the JDBC driver to throw an
> exception.
>
>
> Filip
>
>
>
>
> On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta <v....@gmail.com>
> wrote:
>
> > Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
> > working properly now. Another problem has appeared: although the timeout
> is
> > handled correctly, no exception is thrown. I thought an SQLException
> would
> > be thrown if query takes too long. Maybe pool does not throw exceptions
> at
> > all in that situation?
> >
> > Than you!
> >
> >
> > 2014-07-21 20:40 GMT+04:00 Daniel Mikusa <dm...@gopivotal.com>:
> >
> > > On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <v....@gmail.com>
> > > wrote:
> > >
> > > > Hello, dear tomcat users!
> > > >
> > > > I am developing high-load application using tomcat jdbc connection
> pool
> > > and
> > > > Oracle database. It is very important to ensure my app to have very
> > small
> > > > DB query timeouts (no longer than 3 seconds) to prevent long-running
> > > > queries or database slowness from blocking all my application. To
> > > simulate
> > > > long-running queries I have put the DB in QUIESCE state using ALTER
> > > SYSTEM
> > > > QUIESCE RESTRICTED statement.
> > > >
> > > > But it looks like the timeout values have no impact - when i begin to
> > > test
> > > > my application, it hangs...
> > > >
> > >
> > > Have you taken thread dumps of your application when it "hangs"? What
> do
> > > these show your threads are doing?
> > >
> > >
> > > >
> > > > Here is my jdbc pool configuration:
> > > >
> > > > String connprops =
> > > > "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
> > > > + "oracle.net.READ_TIMEOUT=3000";
> > > >
> > > >
> > > > pp.setConnectionProperties(connprops);
> > > >
> > > > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> > > >
> > > > pp.setTestOnBorrow(true);
> > > > pp.setTestOnConnect(true);
> > >
> > > pp.setTestOnReturn(true);
> > > > pp.setTestWhileIdle(true);
> > > >
> > >
> > > It's probably not necessary to have all of these enabled. I usually
> only
> > > see testOnBorrow and testWhileIdle enabled. TestOnReturn is almost
> > > worthless, in my opinion. TestOnConnect might be helpful to catch
> > > connection issues at boot, but generally a connection that is just
> > created
> > > should be good unless you have configuration problems.
> > >
> > >
> > > > pp.setMaxWait(2000);
> > > > pp.setMinEvictableIdleTimeMillis(20000);
> > > >
> > >
> > >
> > > > pp.setTimeBetweenEvictionRunsMillis(20000);
> > > >
> > >
> > > This might be high given your aggressive settings for
> > > removeAbandonedTimeout. The timeBetweenEvictionRunsMillis setting
> > dictates
> > > how often the pool looks for idle connections, abandoned connections,
> and
> > > how often it validates idle connections. If you set this to 20 secs,
> > using
> > > an abandoned timeout less than 20 seconds is probably not going to be
> > > accurate.
> > >
> > > For example, if the cleaner thread runs and your application has been
> > > holding a connection for 2999ms, it won't consider the connection
> > > abandoned. However the cleaner thread won't run again for another 20
> > secs
> > > (based on the config value you used), thus the application can continue
> > > using that connection for way over the abandoned timeout you've
> > configured.
> > >
> > >
> > > > pp.setValidationInterval(3000);
> > > > pp.setValidationQuery("SELECT 1 FROM DUAL");
> > > >
> > >
> > > Seems OK.
> > >
> > >
> > > >
> > > > pp.setMaxAge(3000);
> > > >
> > >
> > > This seems pretty short and might limit how much pooling actually
> occurs.
> > > Also, since this is enforced when you return the connection to the
> pool
> > > it's not going to help with your current issue. What are you trying to
> > > accomplish by setting this value so low?
> > >
> > >
> > > > pp.setRemoveAbandoned(true);
> > > > pp.setRemoveAbandonedTimeout(3);
> > > >
> > >
> > > This is a pretty low value for abandoned timeout. If you're hoping to
> > use
> > > this to limit how long a query can executed, it's probably not the best
> > > approach. This will limit the amount of time that a connection can be
> > > checked out of the connection pool, and unless you also configure
> > > the ResetAbandonedTimer interceptor, it's going to limit the total time
> > > your application has to use the connection. If you configure the
> > > ResetAbandonedTimer interceptor, it will function closer to the use
> case
> > > you've described but it's probably not the most efficient way to
> > accomplish
> > > this task.
> > >
> > > Generally you'd set the remove abandoned timeout to reclaim connections
> > > that were not properly closed, not to reclaim connections where a query
> > is
> > > stuck or running for a long time. I think the jdbc driver can more
> > easily
> > > handle killing stuck / long running queries. See next comment.
> > >
> > >
> > > >
> > > >
> > > >
> > >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > > >
> > >
> > > I'm a little surprised this isn't working for you. By setting the
> > > statement's query timeout, you're telling the jdbc driver to limit how
> > long
> > > each query can run. If a query runs longer than 3 seconds, you should
> > get
> > > an SQLTimeoutException. Otherwise there's an issue / limitation with
> > your
> > > jdbc driver.
> > >
> > > I would suggest setting up a test without the connection pool to
> validate
> > > that the jdbc driver and your method for slowing down the database is
> > > working correctly. In theory this test should be something like make a
> > > connection, set the query timeout, run a query and wait for the
> > exception.
> > > If that does not timeout, then it's definitely not going to timeout
> with
> > > the connection pool because the jdbc driver handles the actual timing
> out
> > > of the request.
> > >
> > > If your test works, then you might want to look and see if anything
> else
> > is
> > > setting the timeout on the statement object. The interceptor should
> set
> > > the value when a new statement object is created. If something else
> sets
> > > the timeout, it would override the value set by the interceptor.
> > >
> > > An alternative and less aggressive approach to all of this might be to
> > use
> > > the SlowQueryReport or SlowQueryReportJmx interceptors. These will
> watch
> > > for slow queries (queries that take longer than the threshold you
> define
> > to
> > > execute) and report them. It does not actively close or clean up long
> > > running connections, but it does provide you with a lot of helpful
> > > information about the problem when a query does take a while to run.
> > >
> > > Dan
> > >
> > >
> > >
> > > > dataSource = new DataSource();
> > > > dataSource.setPoolProperties(pp);
> > > >
> > > >
> > > > That's how i work with connections (pretty simple):
> > > >
> > > > Connection conn = dataSource.getConnection();
> > > > Statement stmt = null;
> > > > ResultSet rs = null;
> > > >
> > > > try {
> > > > stmt = conn.createStatement();
> > > >
> > > > rs = stmt.executeQuery(/*some select query*/);
> > > >
> > > >
> > > > if (rs.next()) {
> > > >
> > > > result = rs.getInt(1);
> > > >
> > > > /*process the result*/
> > > >
> > > > }
> > > >
> > > > rs.close();
> > > > stmt.close();
> > > > conn.close();
> > > >
> > > > }
> > > > catch(Exception e) {
> > > > logger.error("Exception: " + e.getMessage(), e);
> > > > }finally {
> > > > if (conn != null) {
> > > >
> > > > if(rs!=null)
> > > > rs.close();
> > > > if(stmt!=null)
> > > > stmt.close();
> > > > conn.close();
> > > >
> > > > }
> > > > }
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks in advance!
> > > >
> > >
> >
>
Re: Query timeouts using tomcat jdbc pool
Posted by Filip Hanik <fi...@hanik.com>.
Vasily, the exception depends on where the timeout occurs.
If the timeout is triggered by the driver, because you hit the
setQueryTimeout limit
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
then yes, as per javadoc, it is up to the JDBC driver to throw an exception.
Filip
On Wed, Jul 23, 2014 at 10:04 AM, Vasily Kukhta <v....@gmail.com>
wrote:
> Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
> working properly now. Another problem has appeared: although the timeout is
> handled correctly, no exception is thrown. I thought an SQLException would
> be thrown if query takes too long. Maybe pool does not throw exceptions at
> all in that situation?
>
> Than you!
>
>
> 2014-07-21 20:40 GMT+04:00 Daniel Mikusa <dm...@gopivotal.com>:
>
> > On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <v....@gmail.com>
> > wrote:
> >
> > > Hello, dear tomcat users!
> > >
> > > I am developing high-load application using tomcat jdbc connection pool
> > and
> > > Oracle database. It is very important to ensure my app to have very
> small
> > > DB query timeouts (no longer than 3 seconds) to prevent long-running
> > > queries or database slowness from blocking all my application. To
> > simulate
> > > long-running queries I have put the DB in QUIESCE state using ALTER
> > SYSTEM
> > > QUIESCE RESTRICTED statement.
> > >
> > > But it looks like the timeout values have no impact - when i begin to
> > test
> > > my application, it hangs...
> > >
> >
> > Have you taken thread dumps of your application when it "hangs"? What do
> > these show your threads are doing?
> >
> >
> > >
> > > Here is my jdbc pool configuration:
> > >
> > > String connprops =
> > > "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
> > > + "oracle.net.READ_TIMEOUT=3000";
> > >
> > >
> > > pp.setConnectionProperties(connprops);
> > >
> > > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> > >
> > > pp.setTestOnBorrow(true);
> > > pp.setTestOnConnect(true);
> >
> > pp.setTestOnReturn(true);
> > > pp.setTestWhileIdle(true);
> > >
> >
> > It's probably not necessary to have all of these enabled. I usually only
> > see testOnBorrow and testWhileIdle enabled. TestOnReturn is almost
> > worthless, in my opinion. TestOnConnect might be helpful to catch
> > connection issues at boot, but generally a connection that is just
> created
> > should be good unless you have configuration problems.
> >
> >
> > > pp.setMaxWait(2000);
> > > pp.setMinEvictableIdleTimeMillis(20000);
> > >
> >
> >
> > > pp.setTimeBetweenEvictionRunsMillis(20000);
> > >
> >
> > This might be high given your aggressive settings for
> > removeAbandonedTimeout. The timeBetweenEvictionRunsMillis setting
> dictates
> > how often the pool looks for idle connections, abandoned connections, and
> > how often it validates idle connections. If you set this to 20 secs,
> using
> > an abandoned timeout less than 20 seconds is probably not going to be
> > accurate.
> >
> > For example, if the cleaner thread runs and your application has been
> > holding a connection for 2999ms, it won't consider the connection
> > abandoned. However the cleaner thread won't run again for another 20
> secs
> > (based on the config value you used), thus the application can continue
> > using that connection for way over the abandoned timeout you've
> configured.
> >
> >
> > > pp.setValidationInterval(3000);
> > > pp.setValidationQuery("SELECT 1 FROM DUAL");
> > >
> >
> > Seems OK.
> >
> >
> > >
> > > pp.setMaxAge(3000);
> > >
> >
> > This seems pretty short and might limit how much pooling actually occurs.
> > Also, since this is enforced when you return the connection to the pool
> > it's not going to help with your current issue. What are you trying to
> > accomplish by setting this value so low?
> >
> >
> > > pp.setRemoveAbandoned(true);
> > > pp.setRemoveAbandonedTimeout(3);
> > >
> >
> > This is a pretty low value for abandoned timeout. If you're hoping to
> use
> > this to limit how long a query can executed, it's probably not the best
> > approach. This will limit the amount of time that a connection can be
> > checked out of the connection pool, and unless you also configure
> > the ResetAbandonedTimer interceptor, it's going to limit the total time
> > your application has to use the connection. If you configure the
> > ResetAbandonedTimer interceptor, it will function closer to the use case
> > you've described but it's probably not the most efficient way to
> accomplish
> > this task.
> >
> > Generally you'd set the remove abandoned timeout to reclaim connections
> > that were not properly closed, not to reclaim connections where a query
> is
> > stuck or running for a long time. I think the jdbc driver can more
> easily
> > handle killing stuck / long running queries. See next comment.
> >
> >
> > >
> > >
> > >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> > >
> >
> > I'm a little surprised this isn't working for you. By setting the
> > statement's query timeout, you're telling the jdbc driver to limit how
> long
> > each query can run. If a query runs longer than 3 seconds, you should
> get
> > an SQLTimeoutException. Otherwise there's an issue / limitation with
> your
> > jdbc driver.
> >
> > I would suggest setting up a test without the connection pool to validate
> > that the jdbc driver and your method for slowing down the database is
> > working correctly. In theory this test should be something like make a
> > connection, set the query timeout, run a query and wait for the
> exception.
> > If that does not timeout, then it's definitely not going to timeout with
> > the connection pool because the jdbc driver handles the actual timing out
> > of the request.
> >
> > If your test works, then you might want to look and see if anything else
> is
> > setting the timeout on the statement object. The interceptor should set
> > the value when a new statement object is created. If something else sets
> > the timeout, it would override the value set by the interceptor.
> >
> > An alternative and less aggressive approach to all of this might be to
> use
> > the SlowQueryReport or SlowQueryReportJmx interceptors. These will watch
> > for slow queries (queries that take longer than the threshold you define
> to
> > execute) and report them. It does not actively close or clean up long
> > running connections, but it does provide you with a lot of helpful
> > information about the problem when a query does take a while to run.
> >
> > Dan
> >
> >
> >
> > > dataSource = new DataSource();
> > > dataSource.setPoolProperties(pp);
> > >
> > >
> > > That's how i work with connections (pretty simple):
> > >
> > > Connection conn = dataSource.getConnection();
> > > Statement stmt = null;
> > > ResultSet rs = null;
> > >
> > > try {
> > > stmt = conn.createStatement();
> > >
> > > rs = stmt.executeQuery(/*some select query*/);
> > >
> > >
> > > if (rs.next()) {
> > >
> > > result = rs.getInt(1);
> > >
> > > /*process the result*/
> > >
> > > }
> > >
> > > rs.close();
> > > stmt.close();
> > > conn.close();
> > >
> > > }
> > > catch(Exception e) {
> > > logger.error("Exception: " + e.getMessage(), e);
> > > }finally {
> > > if (conn != null) {
> > >
> > > if(rs!=null)
> > > rs.close();
> > > if(stmt!=null)
> > > stmt.close();
> > > conn.close();
> > >
> > > }
> > > }
> > >
> > > Any ideas?
> > >
> > > Thanks in advance!
> > >
> >
>
Re: Query timeouts using tomcat jdbc pool
Posted by Vasily Kukhta <v....@gmail.com>.
Thank you, I have changed timeBetweenEvictionRunsMillis value and it is
working properly now. Another problem has appeared: although the timeout is
handled correctly, no exception is thrown. I thought an SQLException would
be thrown if query takes too long. Maybe pool does not throw exceptions at
all in that situation?
Than you!
2014-07-21 20:40 GMT+04:00 Daniel Mikusa <dm...@gopivotal.com>:
> On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <v....@gmail.com>
> wrote:
>
> > Hello, dear tomcat users!
> >
> > I am developing high-load application using tomcat jdbc connection pool
> and
> > Oracle database. It is very important to ensure my app to have very small
> > DB query timeouts (no longer than 3 seconds) to prevent long-running
> > queries or database slowness from blocking all my application. To
> simulate
> > long-running queries I have put the DB in QUIESCE state using ALTER
> SYSTEM
> > QUIESCE RESTRICTED statement.
> >
> > But it looks like the timeout values have no impact - when i begin to
> test
> > my application, it hangs...
> >
>
> Have you taken thread dumps of your application when it "hangs"? What do
> these show your threads are doing?
>
>
> >
> > Here is my jdbc pool configuration:
> >
> > String connprops =
> > "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
> > + "oracle.net.READ_TIMEOUT=3000";
> >
> >
> > pp.setConnectionProperties(connprops);
> >
> > pp.setDriverClassName("oracle.jdbc.OracleDriver");
> >
> > pp.setTestOnBorrow(true);
> > pp.setTestOnConnect(true);
>
> pp.setTestOnReturn(true);
> > pp.setTestWhileIdle(true);
> >
>
> It's probably not necessary to have all of these enabled. I usually only
> see testOnBorrow and testWhileIdle enabled. TestOnReturn is almost
> worthless, in my opinion. TestOnConnect might be helpful to catch
> connection issues at boot, but generally a connection that is just created
> should be good unless you have configuration problems.
>
>
> > pp.setMaxWait(2000);
> > pp.setMinEvictableIdleTimeMillis(20000);
> >
>
>
> > pp.setTimeBetweenEvictionRunsMillis(20000);
> >
>
> This might be high given your aggressive settings for
> removeAbandonedTimeout. The timeBetweenEvictionRunsMillis setting dictates
> how often the pool looks for idle connections, abandoned connections, and
> how often it validates idle connections. If you set this to 20 secs, using
> an abandoned timeout less than 20 seconds is probably not going to be
> accurate.
>
> For example, if the cleaner thread runs and your application has been
> holding a connection for 2999ms, it won't consider the connection
> abandoned. However the cleaner thread won't run again for another 20 secs
> (based on the config value you used), thus the application can continue
> using that connection for way over the abandoned timeout you've configured.
>
>
> > pp.setValidationInterval(3000);
> > pp.setValidationQuery("SELECT 1 FROM DUAL");
> >
>
> Seems OK.
>
>
> >
> > pp.setMaxAge(3000);
> >
>
> This seems pretty short and might limit how much pooling actually occurs.
> Also, since this is enforced when you return the connection to the pool
> it's not going to help with your current issue. What are you trying to
> accomplish by setting this value so low?
>
>
> > pp.setRemoveAbandoned(true);
> > pp.setRemoveAbandonedTimeout(3);
> >
>
> This is a pretty low value for abandoned timeout. If you're hoping to use
> this to limit how long a query can executed, it's probably not the best
> approach. This will limit the amount of time that a connection can be
> checked out of the connection pool, and unless you also configure
> the ResetAbandonedTimer interceptor, it's going to limit the total time
> your application has to use the connection. If you configure the
> ResetAbandonedTimer interceptor, it will function closer to the use case
> you've described but it's probably not the most efficient way to accomplish
> this task.
>
> Generally you'd set the remove abandoned timeout to reclaim connections
> that were not properly closed, not to reclaim connections where a query is
> stuck or running for a long time. I think the jdbc driver can more easily
> handle killing stuck / long running queries. See next comment.
>
>
> >
> >
> >
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
> >
>
> I'm a little surprised this isn't working for you. By setting the
> statement's query timeout, you're telling the jdbc driver to limit how long
> each query can run. If a query runs longer than 3 seconds, you should get
> an SQLTimeoutException. Otherwise there's an issue / limitation with your
> jdbc driver.
>
> I would suggest setting up a test without the connection pool to validate
> that the jdbc driver and your method for slowing down the database is
> working correctly. In theory this test should be something like make a
> connection, set the query timeout, run a query and wait for the exception.
> If that does not timeout, then it's definitely not going to timeout with
> the connection pool because the jdbc driver handles the actual timing out
> of the request.
>
> If your test works, then you might want to look and see if anything else is
> setting the timeout on the statement object. The interceptor should set
> the value when a new statement object is created. If something else sets
> the timeout, it would override the value set by the interceptor.
>
> An alternative and less aggressive approach to all of this might be to use
> the SlowQueryReport or SlowQueryReportJmx interceptors. These will watch
> for slow queries (queries that take longer than the threshold you define to
> execute) and report them. It does not actively close or clean up long
> running connections, but it does provide you with a lot of helpful
> information about the problem when a query does take a while to run.
>
> Dan
>
>
>
> > dataSource = new DataSource();
> > dataSource.setPoolProperties(pp);
> >
> >
> > That's how i work with connections (pretty simple):
> >
> > Connection conn = dataSource.getConnection();
> > Statement stmt = null;
> > ResultSet rs = null;
> >
> > try {
> > stmt = conn.createStatement();
> >
> > rs = stmt.executeQuery(/*some select query*/);
> >
> >
> > if (rs.next()) {
> >
> > result = rs.getInt(1);
> >
> > /*process the result*/
> >
> > }
> >
> > rs.close();
> > stmt.close();
> > conn.close();
> >
> > }
> > catch(Exception e) {
> > logger.error("Exception: " + e.getMessage(), e);
> > }finally {
> > if (conn != null) {
> >
> > if(rs!=null)
> > rs.close();
> > if(stmt!=null)
> > stmt.close();
> > conn.close();
> >
> > }
> > }
> >
> > Any ideas?
> >
> > Thanks in advance!
> >
>
Re: Query timeouts using tomcat jdbc pool
Posted by Daniel Mikusa <dm...@gopivotal.com>.
On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <v....@gmail.com>
wrote:
> Hello, dear tomcat users!
>
> I am developing high-load application using tomcat jdbc connection pool and
> Oracle database. It is very important to ensure my app to have very small
> DB query timeouts (no longer than 3 seconds) to prevent long-running
> queries or database slowness from blocking all my application. To simulate
> long-running queries I have put the DB in QUIESCE state using ALTER SYSTEM
> QUIESCE RESTRICTED statement.
>
> But it looks like the timeout values have no impact - when i begin to test
> my application, it hangs...
>
Have you taken thread dumps of your application when it "hangs"? What do
these show your threads are doing?
>
> Here is my jdbc pool configuration:
>
> String connprops =
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;"
> + "oracle.net.READ_TIMEOUT=3000";
>
>
> pp.setConnectionProperties(connprops);
>
> pp.setDriverClassName("oracle.jdbc.OracleDriver");
>
> pp.setTestOnBorrow(true);
> pp.setTestOnConnect(true);
pp.setTestOnReturn(true);
> pp.setTestWhileIdle(true);
>
It's probably not necessary to have all of these enabled. I usually only
see testOnBorrow and testWhileIdle enabled. TestOnReturn is almost
worthless, in my opinion. TestOnConnect might be helpful to catch
connection issues at boot, but generally a connection that is just created
should be good unless you have configuration problems.
> pp.setMaxWait(2000);
> pp.setMinEvictableIdleTimeMillis(20000);
>
> pp.setTimeBetweenEvictionRunsMillis(20000);
>
This might be high given your aggressive settings for
removeAbandonedTimeout. The timeBetweenEvictionRunsMillis setting dictates
how often the pool looks for idle connections, abandoned connections, and
how often it validates idle connections. If you set this to 20 secs, using
an abandoned timeout less than 20 seconds is probably not going to be
accurate.
For example, if the cleaner thread runs and your application has been
holding a connection for 2999ms, it won't consider the connection
abandoned. However the cleaner thread won't run again for another 20 secs
(based on the config value you used), thus the application can continue
using that connection for way over the abandoned timeout you've configured.
> pp.setValidationInterval(3000);
> pp.setValidationQuery("SELECT 1 FROM DUAL");
>
Seems OK.
>
> pp.setMaxAge(3000);
>
This seems pretty short and might limit how much pooling actually occurs.
Also, since this is enforced when you return the connection to the pool
it's not going to help with your current issue. What are you trying to
accomplish by setting this value so low?
> pp.setRemoveAbandoned(true);
> pp.setRemoveAbandonedTimeout(3);
>
This is a pretty low value for abandoned timeout. If you're hoping to use
this to limit how long a query can executed, it's probably not the best
approach. This will limit the amount of time that a connection can be
checked out of the connection pool, and unless you also configure
the ResetAbandonedTimer interceptor, it's going to limit the total time
your application has to use the connection. If you configure the
ResetAbandonedTimer interceptor, it will function closer to the use case
you've described but it's probably not the most efficient way to accomplish
this task.
Generally you'd set the remove abandoned timeout to reclaim connections
that were not properly closed, not to reclaim connections where a query is
stuck or running for a long time. I think the jdbc driver can more easily
handle killing stuck / long running queries. See next comment.
>
>
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
>
I'm a little surprised this isn't working for you. By setting the
statement's query timeout, you're telling the jdbc driver to limit how long
each query can run. If a query runs longer than 3 seconds, you should get
an SQLTimeoutException. Otherwise there's an issue / limitation with your
jdbc driver.
I would suggest setting up a test without the connection pool to validate
that the jdbc driver and your method for slowing down the database is
working correctly. In theory this test should be something like make a
connection, set the query timeout, run a query and wait for the exception.
If that does not timeout, then it's definitely not going to timeout with
the connection pool because the jdbc driver handles the actual timing out
of the request.
If your test works, then you might want to look and see if anything else is
setting the timeout on the statement object. The interceptor should set
the value when a new statement object is created. If something else sets
the timeout, it would override the value set by the interceptor.
An alternative and less aggressive approach to all of this might be to use
the SlowQueryReport or SlowQueryReportJmx interceptors. These will watch
for slow queries (queries that take longer than the threshold you define to
execute) and report them. It does not actively close or clean up long
running connections, but it does provide you with a lot of helpful
information about the problem when a query does take a while to run.
Dan
> dataSource = new DataSource();
> dataSource.setPoolProperties(pp);
>
>
> That's how i work with connections (pretty simple):
>
> Connection conn = dataSource.getConnection();
> Statement stmt = null;
> ResultSet rs = null;
>
> try {
> stmt = conn.createStatement();
>
> rs = stmt.executeQuery(/*some select query*/);
>
>
> if (rs.next()) {
>
> result = rs.getInt(1);
>
> /*process the result*/
>
> }
>
> rs.close();
> stmt.close();
> conn.close();
>
> }
> catch(Exception e) {
> logger.error("Exception: " + e.getMessage(), e);
> }finally {
> if (conn != null) {
>
> if(rs!=null)
> rs.close();
> if(stmt!=null)
> stmt.close();
> conn.close();
>
> }
> }
>
> Any ideas?
>
> Thanks in advance!
>
Re: Query timeouts using tomcat jdbc pool
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Vasily,
On 7/21/14, 11:05 AM, Vasily Kukhta wrote:
> Oracle database. It is very important to ensure my app to have very
> small DB query timeouts (no longer than 3 seconds) to prevent
> long-running queries or database slowness from blocking all my
> application. To simulate long-running queries I have put the DB in
> QUIESCE state using ALTER SYSTEM QUIESCE RESTRICTED statement.
It's important to note that nothing in the JDBC driver will prevent a
/series/ of queries from taking more than 3 seconds. For example:
> String connprops =
> "oracle.net.CONNECT_TIMEOUT=3000;oracle.jdbc.ReadTimeout=3000;" +
> "oracle.net.READ_TIMEOUT=3000";
>
> pp.setMaxWait(2000);
>
> pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=3)");
With
>
the above settings, issuing 10 queries can take up to 35 seconds:
up to 2 seconds to wait for a connection from the pool, up to 3
seconds to (re)connect to the database, then up to 3 seconds for each
of the 10 queries.
If you want to make sure that entire HTTP transactions don't take "too
long" then you'll have to keep track of the overall timeout yourself.
> That's how i work with connections (pretty simple):
>
> Connection conn = dataSource.getConnection(); Statement stmt =
> null; ResultSet rs = null;
>
> try { stmt = conn.createStatement();
>
> rs = stmt.executeQuery(/*some select query*/);
>
>
> if (rs.next()) {
>
> result = rs.getInt(1);
>
> /*process the result*/
>
> }
>
> rs.close(); stmt.close(); conn.close();
>
> } catch(Exception e) { logger.error("Exception: " + e.getMessage(),
> e); }finally { if (conn != null) {
>
> if(rs!=null) rs.close(); if(stmt!=null) stmt.close();
> conn.close();
>
> } }
>
> Any ideas?
What query are you executing that should take longer than 3 seconds,
and therefore trigger the timeout?
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBCAAGBQJTzVswAAoJEBzwKT+lPKRY8m8P/3bh/pKfuS1oSodKgpyQFIvz
3OXlaSLK5QZBLsL9EFJJDw6bpNPKYHd3GdpQRmGqJYtG98qoXBVztZJPckPZBQwH
6mWuMAdbhwYZFNJVoyG72EfcZAamv+z8n4+WMTR6kjD6XbmAjAQMhgItOEMMnfSR
xyhb+w/dbwFk33Qrwf8Jfhj9/LHNBg99AS0SY8EgKGUT4PQZJ0DR2jY90oVWTy/k
ZaHXQxjNM1eiZPXTZmprsq+NVaLegG6BrJy0kTWgPrDk3jilgMgoFWDHb4MNuGCR
iSuWd8i+Sy6Y5VgYQBF5+UQylys7an3rZmYNRCr0GNj4NK2o9ipkvhTvaoH79G/z
26kungun8AEsmbBnK/YqzVYRxH66Ysy2mudGJYuDvNo55vJ7NoMtNt3Is0tF3Tgk
A/QjG3p4qPFMLeOMhlBqUvSAjsYIWsSBy9/698nkgDvILXKB4yMhyn+/jtoWImvy
EkHIlrKPAtGkMoRPqIODisTCDyVXd2bBntUM6rWfB7LrI8Hp8PLIN3d3IDXl3Mzc
qlTQiL0z5q9oOgxpaIXccWvR0kY7gobipASjyNfi09BGXAvM7rigFIHj9BO28x46
38r6vV2Ok7tatDJM7C2Xxoec69ke7CgQx3KEaabbCNzv3jFBbhfxULBPVtSoqzkY
XuqS6VKmEWnYoHbwbfkv
=f7nf
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org