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 <dmik...@gopivotal.com>:
> 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!
> >
>
