db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: cancel a running query?
Date Sun, 12 Oct 2008 02:35:35 GMT

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.

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.


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     
|IX_PRODUCT_SUB_GROUPS_ENCODED_TITLE        |read 
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   
|constraint|PK_PRODUCT_SUB_GROUPS_PRODUCT_SUB_GROUP_ID |read 
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).



Regards,

Stephan van Loendersloot.

Mime
View raw message