[jira] [Commented] (TRAFODION-2645) MDAM costing overestimates I/O cost

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16363306#comment-16363306 ] 

ASF GitHub Bot commented on TRAFODION-2645:
-------------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/trafodion/pull/1443

    [TRAFODION-2645] Fixes to MDAM costing code found during inner join tests

    This set of changes fixes some issues that were found in the new MDAM costing code while testing it for the case of an inner table of a nested join.
    
    The fixes are:
    
    1. We were way too pessimistic in calculating the number of seeks. We assumed we would do a seek per block, but it is obvious that the number of seeks is bounded from above by the number of subsets. This showed up when testing queries with tiny numbers of probes (4) but large numbers of rows per sequential subset (over 100 blocks worth in total).
    
    2. We were applying some rowsize adjustment factors twice, once in NewMDAMCostWA::compute, and again in FileScanOptimizer::scmComputeMDAMCostForHbase, which it calls. The duplicate logic was removed in NewMDAMCostWA::compute.
    
    3. We were overestimating the number of fetch subset rows when costing MDAM for the inner table of a nested join. It turns out that the histogram handling does a cross product scale-up of the histograms in order to calculate the effect of a join predicate. But this scale-up looks at things from the standpoint of the complete result of the query, while the MDAM costing logic is looking at things from the standpoint of a single outer table join probe. Dividing fetch row counts by the number of outer table join probes corrects the problem. Then at a higher level we multiply by the number of outer table join probes (covering both MDAM probes and MDAM fetches) to calculate the total cost of the MDAM scan.
    
    Notes: The new MDAM costing code is still off by default. To turn it on, use CQD MDAM_COSTING_REWRITE 'ON'. To test MDAM on the inner table of nested joins, one must either force such plans with control query shape or turn off heuristics that prevent MDAM in these cases. There are two of main interest: CQD MDAM_UNDER_NJ_PROBES_THRESHOLD turns off MDAM when the number of outer table probes is higher than its value. It defaults to '0' so by default MDAM is (almost) always turned off on the inner table of nested joins. Setting this to a positive value (I used '1000' for my testing) allows MDAM. Secondly, there is a heuristic in the JoinToTSJ transformation rule that prevents nested joins if no inner table key prefix is covered by some predicate. This prevents consideration of nested joins where MDAM may help when a predicate is missing on the leading column. Setting CQD KEYLESS_NESTED_JOINS 'ON' overrides this heuristic. Alternatively, turning off hash joins and merge joins also overrides this heuristic.
    
    Summarizing, if one wishes to test the new MDAM costing code, and wishes to benefit from nested joins using MDAM on the inner table, then one should presently set these CQDs:
    
    ```
    CQD MDAM_UNDER_NJ_PROBES_THRESHOLD '1000';  -- (some positive value greater than one)
    CQD KEYLESS_NESTED_JOINS 'ON';
    CQD MDAM_COSTING_REWRITE 'ON';
    ```
    


You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/trafodion MdamRewriteNJ

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/trafodion/pull/1443.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #1443
    
----
commit c008ebe519f51e5344b650a9c88e66e973320084
Author: Dave Birdsall <db...@...>
Date:   2018-02-14T00:42:41Z

    [TRAFODION-2645] Fixes to MDAM costing code found during inner join testing

----


> MDAM costing overestimates I/O cost
> -----------------------------------
>
>                 Key: TRAFODION-2645
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2645
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.2-incubating
>         Environment: All
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>
> The method MDAMCostWA::compute (optimizer/ScanOptimizer.cpp) has logic to calculate the total cost of an MDAM plan and compare it to the cost of a single subset plan. In the case of a Trafodion table, the cost logic for MDAM cost has an error. It is adding the total number of probes (totalRqsts) to the number of seeks (totalSeeks) in order to calculate I/O costs.
> TotalSeeks models direct access disk seeks. These happen at the block level and are an I/O cost.
> TotalRqsts models the number of probes, which is a CPU cost. A probe is a direct access within a block that has already been read into memory. This is a vastly smaller unit of cost than an I/O operation.
> Note that TotalRqsts is already modeled as a message cost since it is added to TotalRows.
> The effect of the bug is to vastly penalize MDAM plans having large numbers of probes. This will show up the most on the largest tables.
> The fix is simply not to add totalRqsts to totalSeeks. However, in making this fix, it is likely that other MDAM-related parameters will need tuning. So this change needs to be done carefully.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)