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
|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).
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!
--
Kristian
[1] https://issues.apache.org/jira/browse/DERBY
Regards,
Stephan van Loendersloot.
