[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13554954#comment-13554954
]
Knut Anders Hatlen commented on DERBY-6011:
-------------------------------------------
In case someone wonders why the estimated row count is 6 for the empty index,
that's because
1) OpenBTree.getEstimatedRowCount() adjusts the row count for empty indexes
from 0 to 1, with the following rationale:
// Don't return 0 rows (return 1 instead), as this often leads the
// optimizer to produce plans which don't use indexes because of the 0
// row edge case.
//
// Eventually the plan is recompiled when rows are added, but we
// have seen multiple customer cases of deadlocks and timeouts
// because of these 0 row based plans.
2) FromBaseTable.estimateCost() adds 5 to the row count with this comment:
/* we prefer index than table scan for concurrency
reason, by a small
* adjustment on estimated row count. This affects
optimizer's decision
* especially when few rows are in table. beetle 5006.
This makes sense
* since the plan may stay long before we actually
check and invalidate it.
* And new rows may be inserted before we check and
invalidate the plan.
* Here we only prefer index that has start/stop key
from predicates. Non-
* constant start/stop key case is taken care of by
selectivity later.
*/
If we'd skipped (2), the estimated row count would stayed at 1, and the
aforementioned adjustment of the cost in FromBaseTable to prefer unique indexes
would have been applied. So even though the two adjustments in FromBaseTable
have the same goal, they end up canceling each other.
> 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
