You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Eric Owhadi <er...@esgyn.com> on 2016/01/08 01:02:40 UTC
perf optimization in Predicate Push down V2 and MDAM problem
OK so now I am seeing a hang on executor/test025
And I have investigated it to be related to the optimization I do to limit
the columns retrieved to strict minimum.
Here is a working scenario, where I removed the optimization code:
Apache Trafodion Conversational Interface 2.0.0
Copyright (c) 2015 Apache Software Foundation
>>cqd hbase_filter_preds '2';
--- SQL operation complete.
>>control query shape groupby(scan('t025m2', MDAM_COLUMNS ALL));
--- SQL operation complete.
>>explain select count(*),sum(s),min(s),max(s),avg(s) from t025m2
where r > 4 and r < 60;+>
------------------------------------------------------------------ PLAN
SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212318970462810408
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.08
STATEMENT ................ select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
MUST_MATCH ............... REL_ANY_GROUP(forced scan(T025M2, index T025M2))
------------------------------------------------------------------ NODE
LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality ... 11
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
HBASE_FILTER_PREDS ..... 2
GENERATE_EXPLAIN ....... ON
ObjectUIDs ............. 8537785827430889366
select_list ............ count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T025
M2.S)) * 10000 ...0)) /
cast(count_nonull(TRAFODION.SCH.T025M2.S)))) /
10000 ...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
aggregates ............. count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
count_nonull(TRAFODION.SCH.T025M2.S)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T025M2
REQUESTS_IN .............. 1
ROWS_OUT ................ 11
EST_OPER_COST ............ 0.08
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 11
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan limited by mdam of table
TRAFODION.SCH.T025M2
object_type ............ Trafodion
cache_size ........... 100
rows_accessed ......... 11
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ Q, R
mdam_disjunct .......... (R > 4) and (R < 60)
--- SQL operation complete.
>>select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
+>+>
(EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
-------------------- -------------------- ----------- -----------
--------------------
3 65 5
50 21
--- 1 row(s) selected.
>>
Now if I put my optimization back in, I get for column retrieved
column_retrieved ....... #1:2,#1:3
so I am not returning Q, logically so, since only s and r are involved in
the select statement.
Is there a reason why for the case of MDAM scan, I should always return all
key columns? (so bypass the optimization for MDAM scan)?
Thanks in advance for the help,
Eric
RE: perf optimization in Predicate Push down V2 and MDAM problem
Posted by Eric Owhadi <er...@esgyn.com>.
OK cool, I will change the code to disable the optimization for mdam scan.
Thanks for the quick response,
Eric
-----Original Message-----
From: Dave Birdsall [mailto:dave.birdsall@esgyn.com]
Sent: Thursday, January 7, 2016 6:06 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: perf optimization in Predicate Push down V2 and MDAM problem
Hi Eric,
In the Explain output, I see a disjunct that references column R. MDAM will
need to see column R and any key columns that precede it. I'm not quite sure
what the CQD is doing, but it is possible it is forcing MDAM to traverse to
all of the key columns. So it may be that MDAM needs to see all of the key
columns.
Dave
-----Original Message-----
From: Eric Owhadi [mailto:eric.owhadi@esgyn.com]
Sent: Thursday, January 7, 2016 4:03 PM
To: dev@trafodion.incubator.apache.org
Subject: perf optimization in Predicate Push down V2 and MDAM problem
OK so now I am seeing a hang on executor/test025
And I have investigated it to be related to the optimization I do to limit
the columns retrieved to strict minimum.
Here is a working scenario, where I removed the optimization code:
Apache Trafodion Conversational Interface 2.0.0
Copyright (c) 2015 Apache Software Foundation
>>cqd hbase_filter_preds '2';
--- SQL operation complete.
>>control query shape groupby(scan('t025m2', MDAM_COLUMNS ALL));
--- SQL operation complete.
>>explain select count(*),sum(s),min(s),max(s),avg(s) from t025m2
where r > 4 and r < 60;+>
------------------------------------------------------------------ PLAN
SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212318970462810408
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.08
STATEMENT ................ select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
MUST_MATCH ............... REL_ANY_GROUP(forced scan(T025M2, index T025M2))
------------------------------------------------------------------ NODE
LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality ... 11
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
HBASE_FILTER_PREDS ..... 2
GENERATE_EXPLAIN ....... ON
ObjectUIDs ............. 8537785827430889366
select_list ............ count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T025
M2.S)) * 10000 ...0)) /
cast(count_nonull(TRAFODION.SCH.T025M2.S)))) /
10000 ...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
aggregates ............. count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
count_nonull(TRAFODION.SCH.T025M2.S)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T025M2
REQUESTS_IN .............. 1
ROWS_OUT ................ 11
EST_OPER_COST ............ 0.08
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 11
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan limited by mdam of table
TRAFODION.SCH.T025M2
object_type ............ Trafodion
cache_size ........... 100
rows_accessed ......... 11
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ Q, R
mdam_disjunct .......... (R > 4) and (R < 60)
--- SQL operation complete.
>>select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
+>+>
(EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
-------------------- -------------------- ----------- -----------
--------------------
3 65 5
50 21
--- 1 row(s) selected.
>>
Now if I put my optimization back in, I get for column retrieved
column_retrieved ....... #1:2,#1:3
so I am not returning Q, logically so, since only s and r are involved in
the select statement.
Is there a reason why for the case of MDAM scan, I should always return all
key columns? (so bypass the optimization for MDAM scan)?
Thanks in advance for the help,
Eric
RE: perf optimization in Predicate Push down V2 and MDAM problem
Posted by Dave Birdsall <da...@esgyn.com>.
Hi Eric,
In the Explain output, I see a disjunct that references column R. MDAM will
need to see column R and any key columns that precede it. I'm not quite sure
what the CQD is doing, but it is possible it is forcing MDAM to traverse to
all of the key columns. So it may be that MDAM needs to see all of the key
columns.
Dave
-----Original Message-----
From: Eric Owhadi [mailto:eric.owhadi@esgyn.com]
Sent: Thursday, January 7, 2016 4:03 PM
To: dev@trafodion.incubator.apache.org
Subject: perf optimization in Predicate Push down V2 and MDAM problem
OK so now I am seeing a hang on executor/test025
And I have investigated it to be related to the optimization I do to limit
the columns retrieved to strict minimum.
Here is a working scenario, where I removed the optimization code:
Apache Trafodion Conversational Interface 2.0.0
Copyright (c) 2015 Apache Software Foundation
>>cqd hbase_filter_preds '2';
--- SQL operation complete.
>>control query shape groupby(scan('t025m2', MDAM_COLUMNS ALL));
--- SQL operation complete.
>>explain select count(*),sum(s),min(s),max(s),avg(s) from t025m2
where r > 4 and r < 60;+>
------------------------------------------------------------------ PLAN
SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212318970462810408
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.08
STATEMENT ................ select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
MUST_MATCH ............... REL_ANY_GROUP(forced scan(T025M2, index T025M2))
------------------------------------------------------------------ NODE
LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality ... 11
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
HBASE_FILTER_PREDS ..... 2
GENERATE_EXPLAIN ....... ON
ObjectUIDs ............. 8537785827430889366
select_list ............ count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T025
M2.S)) * 10000 ...0)) /
cast(count_nonull(TRAFODION.SCH.T025M2.S)))) /
10000 ...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
aggregates ............. count(1 ), sum(TRAFODION.SCH.T025M2.S),
min(TRAFODION.SCH.T025M2.S),
max(TRAFODION.SCH.T025M2.S),
count_nonull(TRAFODION.SCH.T025M2.S)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T025M2
REQUESTS_IN .............. 1
ROWS_OUT ................ 11
EST_OPER_COST ............ 0.08
EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 11
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan limited by mdam of table
TRAFODION.SCH.T025M2
object_type ............ Trafodion
cache_size ........... 100
rows_accessed ......... 11
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ Q, R
mdam_disjunct .......... (R > 4) and (R < 60)
--- SQL operation complete.
>>select count(*),sum(s),min(s),max(s),avg(s)
from t025m2
where r > 4 and r < 60;
+>+>
(EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
-------------------- -------------------- ----------- -----------
--------------------
3 65 5
50 21
--- 1 row(s) selected.
>>
Now if I put my optimization back in, I get for column retrieved
column_retrieved ....... #1:2,#1:3
so I am not returning Q, logically so, since only s and r are involved in
the select statement.
Is there a reason why for the case of MDAM scan, I should always return all
key columns? (so bypass the optimization for MDAM scan)?
Thanks in advance for the help,
Eric