On Mon, Jul 21, 2014 at 11:05 AM, Vasily Kukhta <v.b.kuk...@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!
>
