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

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

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

David Wayne Birdsall commented on TRAFODION-2645:
-------------------------------------------------

I experimented with the suggested change a few days ago. When I ran the development regression suite I encountered two plan changes that were not beneficial. In seabase/TEST011, one query that formerly did not use MDAM started using MDAM with a disjunct of (V1 < 100000) AND (V2 < 99) AND (V3 < 500) which intuitively does not seem like a good plan to me. And indeed when I executed the query with old and new plans I found the execution time lengthened from 0.332512 seconds to 0.739676 seconds on my workstation. This was not a controlled environment so more careful testing would be needed to prove that it is detrimental but it seems like a plausible result to me. In test seabase/TEST026, I saw three queries switch from a full index-only scan against the OBJECTS_UNIQ_IDX to an MDAM plan against the OBJECTS table. The MDAM plans look better, actually, but we get different results! It turns out the testware needs upgrading. The test is a CLEANUP test, and the OBJECTS_UNIQ_IDX was intentionally made to be inconsistent with the OBJECTS table. The testware should be upgraded to execute the queries twice, with the access path forced each way.

So, the change suggested in this JIRA may still be a good one but additional changes might be needed. Further development testing is needed.

> 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
>
> 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
(v6.4.14#64029)