db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-1397) Tuning Guide: Puzzling optimizer documentation
Date Thu, 17 Oct 2013 22:09:44 GMT

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

Kathey Marsden commented on DERBY-1397:

The only out of memory complaint I have gotten was  a bug: https://issues.apache.org/jira/browse/DERBY-6096
where the  user as a work around set derby.language.maxMemoryPerTable  to 0 to avoid hash
joins all together.

The more common symptom would be one of performance where nested loop joins are chosen instead
of hash joins.  I haven't heard that complaint specifically, but it is nuanced because the
query still works, just is not as fast as it could or should be.

I am all for changing the property default to something more reasonable. I am not sure if
there is an issue for that or not. I am also not sure what would be a good rule for choosing
a default value that would work for both small and large memory profiles.

> Tuning Guide: Puzzling optimizer documentation
> ----------------------------------------------
>                 Key: DERBY-1397
>                 URL: https://issues.apache.org/jira/browse/DERBY-1397
>             Project: Derby
>          Issue Type: Bug
>          Components: Documentation
>    Affects Versions:
>            Reporter: Rick Hillegas
>            Assignee: Kim Haase
>              Labels: derby_triage10_5_2
>  Selectivity and cardinality statistics
>    Working with cardinality statistics
>      When cardinality statistics are automatically updated
>        "For other operations, Derby automatically updates statistics for the table and
all indexes on the table if they are already exist. Those operations are:
>    * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
>    * (index only) When you drop a column that is part of a table's index; the statistics
for the affected index are dropped, and statistics for the other indexes on the table are
> "
> What does the second bullet mean? Derby doesn't let you drop a column from a table right
> ----------------------------------------------------------
> Here's another puzzling piece of optimizer documentation:
> I'm puzzled by the following paragraph in Tuning Guide->DML statements and performance->Performance
and optimization->Joins and performance->Join strategies:
> "If memory use is not a problem for your environment, set this property to a high number;
allowing the optimizer the maximum flexibility in considering a join strategy queries involving
large queries leads to better performance. It can also be set to smaller values for more limited
> I can't find the name of this property on that page of the Tuning Guide. I'm also confused
about what we consider to be a "high number" versus what we consider to be "smaller values".
Would appreciate advice here. 
> Satheesh adds this:
> The property it may be referring to is
> *derby.language.maxMemoryPerTable*. The default value is 1024 KB.
> Current default value is too small, so it would be a good tip for
> developers to know and tune this property. It would be great if Derby
> can configure this property value based on factors like max heap size,
> size of data cache and/or other parameters.

This message was sent by Atlassian JIRA

View raw message