db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: cancel a running query?
Date Tue, 14 Oct 2008 07:20:27 GMT
On 12.10.08 04:35, Stephan van Loendersloot (LIST) wrote:
> Kristian Waagan wrote:
>> Rick Hillegas wrote:
>>> Hi Dirk,
>>> Yes, Derby does support Statement.setQueryTimeout(), at least as 
>>> verified by the Derby regression test 
>>> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest. 
>>> That's a good defense if you suspect in advance that your queries may 
>>> ramble on. It leaves the lingering issue of how to cancel a runaway 
>>> query that you (or someone else) didn't limit before the query started.
>> Correct.
>> It should be noted though, that your mileage may vary with 
>> setQueryTimeout. I assume that mechanism requires the engine to check 
>> a flag to see if it should abort, and if the code is "stuck" in an 
>> area where there are no such checks you might have to wait longer than 
>> the specified timeout value.
>> If anyone has been using it, it would be nice to get some feedback on 
>> how well it is working/behaving.
>> Is it possible to use the same machinery to add support for timing out 
>> a running transaction?
>> Is this something that would be needed by many users?
>> I suppose the client (dba, user etc) would normally have an idea 
>> whether the transaction/query will finish in a few seconds or a few 
>> days before issuing it.
>> regards,
> Hi Kristian,
> I had the intention to reply to this earlier, but work and time limits 
> didn't help very much...
> Anyway, here's your feedback. The setQueryTimeOut() method works like a 
> charm... we really optimize our queries for Derby. It's easy, since in 
> our systems every dynamic 'new' query runs through 
> SYSCS_GET_RUNTIMESTATISTICS, which has a 24 hour 'cache'. This enables 
> us to to log any anomaly that might occur.

Hello, Stephan,

It's great to hear that setQueryTimeOut is working well in Derby.

> Sometimes the anomaly rears it's ugly head when the index statistics get 
> stale, which is why I'm really looking forward to the 10.5 release. 
> However, various other unpredictable results can happen when working 
> with millions of tuples and *a lot* of concurrency, no matter how much 
> unit-testing was done.
> We hardly need it anymore since we switched from another open source 
> RDBMS to Derby, but hey, it doesn't hurt performance, so we use 
> setQueryTimeOut on every query, and our software does exactly what it 
> needs to do: quit->log->inform instantly.
> We're able to solve problems of unexpected long running queries in a 
> short timeframe and setQueryTimeOut really helps.
> I haven't had the time yet (again, sigh) to look at the codebase to see 
> how it's implemented, but we tend not to use simple time differences 
> like System.currentMilliseconds/nanoSeconds to measure the quality of a 
> query. Instead we use ThreadMXBean or hook into JNI to get the actual 
> processor-time on our (Linux) production servers, even regarding the 
> sloppy delays on some (Windows) development systems.
> Conclusion: setQueryTimeout works... our logs show messages within 
> nanoseconds.

Sounds like you have a good system going. I'm sure other people would be 
happy to learn from your experiences.

> Now, to be able to actually identify queries that do not use intended 
> indices, and as such may be slow, I've written some simple classes that 
> parse the output of SYSCS_GET_RUNTIME_STATISTICS to show per table/join 
> in a MySQL-EXPLAIN like format how Derby does things (excuse the format, 
> you may need a wide screen):
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

> Table             |RowResultSetType     |ResultSetType|IndexType 
> |IndexName                                  
> |IsolationLevel|LockingType            |ScanType|RowsVisited|RowsQualified|
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

> PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan   |index     
> committed|instantaneous share row|btree   |1          |1            |
> PRODUCTS_UNITS    |                     |Table Scan   |          
> |                                           |read committed|share 
> row              |heap    |391        |308          |
> PRODUCTS          |Index Row to Base Row|Index Scan   
> |constraint|PK_PRODUCTS_PRODUCT_ID                     |read 
> committed|share row              |btree   |308        |308          |
> PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan   
> committed|share row              |btree   |262        |262          |
> PRODUCT_GROUPS    |Index Row to Base Row|Index Scan   
> |constraint|PK_PRODUCT_GROUPS_PRODUCT_GROUP_ID         |read 
> committed|share row              |btree   |248        |248          |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

> Though this may seem unrelated to the raised issue, it is related to us 
> and using setQueryTimeout, as well as the issue raised about a week ago 
> on the dev-list, (Question regarding runtimestatistics and join order), 
> which is why I brought it here...
> It's late in the Netherlands, about 4/5 o'clock and I really need to get 
> some sleep now... but I hope to have cleared that setQueryTimeout 
> actually DOES work like it's supposed to.
> The last part of my post is to remind myself to contribute the code that 
> parses the statistics, if and when anyonye finds that it can be useful 
> (I'll get back on that on the dev-list).

There have been talks about presenting the runtime statistics in a more 
parser-friendly format, but I don't think anyone has started working on 
it yet. I appreciate that you took the time to share your information 
with us. If you want to contribute some of your code, creating a Jira 
issue would be an option [1].

Thanks a lot for your feedback!


[1] https://issues.apache.org/jira/browse/DERBY

> Regards,
> Stephan van Loendersloot.

View raw message