db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6011) Derby performs very badly (seems to deadlock and timeout) in very simple multi-threaded tests
Date Mon, 21 Jan 2013 14:22:12 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13558788#comment-13558788

Knut Anders Hatlen commented on DERBY-6011:

The suggested fix only works if the cost we add to the non-unique index scan is sufficiently
big to out-weigh the difference in cost for fetching the base row.

The fix is sufficient in the original query, as the difference in base row cost isn't  0.6
base row retrievals (non-unique index) vs 1 base row (unique index).

It does not seem to be sufficient for this similar query, though:

create table t1(c1 int not null, c2 int not null, c3 int not null, c4 int not null, c5 blob);
create index idx3 on t1(c1, c2, c3);
create unique index uidx4 on t1(c1, c2, c3, c4);

prepare ps as 'select * from t1 where c1 = ? and c2 = ? and c3 = ? and c4 = ?';
execute ps using 'values (1,2,3,4)';

Here, we'd want the SELECT statement to use the unique index UIDX4, but the optimizer picks
the non-unique index IDX3. The unique index is better because it reads fewer rows from the
index (or at least no more rows than the non-unique index), fewer rows from the base table.

The reason why it ends up using the non-unique index, is that the higher number of predicates
in this query makes the selectivity very low, so the estimated number of rows returned by
the non-unique index is 0.01. That means the estimated cost of the base row fetch operation
is 100 times higher for the unique index, where the estimated row count is 1.

For this query, Mike's suggestion of using a floor of 1.0 for the row count would probably
work better. That should also help the query we looked at originally. I'll investigate and
see if that's a viable alternative.
> Derby performs very badly (seems to deadlock and timeout) in very simple multi-threaded
> ---------------------------------------------------------------------------------------------
>                 Key: DERBY-6011
>                 URL: https://issues.apache.org/jira/browse/DERBY-6011
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions:,,
>         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, prefer-unique-index-v1.diff
> 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

View raw message