[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13552932#comment-13552932
]
Mike Matrigali commented on DERBY-6011:
---------------------------------------
thanks for the analysis knut. I agree that again there is a problem with edge
case of very few rows. The actual performance diff probably does not matter,
but for locking would be
nice if we could get the optimizer to pick the unique key index in this case.
estimating one row from the unique index seems a reasonable guess for most
queries, so I don't think we should change that.
What would people think about changing the estimate on the non-unique index to
always have a floor of either 1 row if that works or maybe 1.1 rows just to
make it always bigger than
the unique index case if 1 does not work. I have not worked in the optimizer
code so not sure where or how easy that change would be. Would that get the
"right" indexes picked in
both of these cases? This seems like a pretty safe change to me, as it would
only affect these edge case small number of row cases.
For those interested this problem is likely specific to queries with "?"
parameters, as the optimization has to use guesses like "10%" when it does not
exact value in the case of
a non unique index or using part of the columns in a unique index. With actual
values Derby will use actual index to figure out better estimate.
> Derby performs very badly (seems to deadlock and timeout) in very simple
> multi-threaded tests
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-6011
> URL: https://issues.apache.org/jira/browse/DERBY-6011
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.7.1.1, 10.8.2.2, 10.9.1.0
> Environment: Lenovo laptop with SSD's, Windows 7, 64-bit, Sun JDK
> 1.6.xx
> Reporter: Karl Wright
> Attachments: derby.log, force-specific-index.diff, manifoldcf.log
>
>
> The Apache ManifoldCF project supports Derby as one of its underlying
> databases. Simple tests, however, demonstrate that Derby is apparently
> deadlocking and timing out repeatedly under multi-thread conditions. This
> problem is long-standing, and is not exhibited by any other database
> ManifoldCF supports, and makes a simple test take between 6x and 12x as long.
> There is a trivial test with demonstrates the problem vs. other databases.
> Please do the following (once you have java 1.6+, svn 1.7+, and ant 1.7+
> available):
> (1) Check out https://svn.apache.org/repos/asf/manifoldcf/trunk
> (2) Run the following ant target to download the dependencies: "ant
> make-core-deps"
> (3) Run the Derby test: "ant run-rss-tests-derby" . Note the time required -
> at least 180 seconds, can be up to 360 seconds.
> (4) Run the equivalent HSQLDB test: "ant run-rss-tests-HSQLDB". This test
> takes about 31 seconds to run.
> The output of the Derby test can be found in the directory
> "tests/rss/test-derby-output". Have a look at manifoldcf.log, where all
> long-running queries are reported. Derby.log is also included, which shows
> only that during the test's cleanup phase the database is deleted before it
> is shutdown, which is not pertinent to the performance issue.
> I am available to assist with ManifoldCF, if that seems to be required.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
