hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Xuefu Zhang (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (HIVE-16840) Investigate the performance of order by limit in HoS
Date Wed, 21 Jun 2017 14:01:02 GMT

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

Xuefu Zhang edited comment on HIVE-16840 at 6/21/17 2:00 PM:
-------------------------------------------------------------

Re: turning the optimization off when limit number is too big

It might seem over-engineering if we are trying to be smart. In my opinion, it's very rare
that LIMIT comes with a big number compared to the total number of rows. The penalty comes
only when the limit number is close to the total number of rows. Still, it's not clear how
much the penalty is.

Consider an extreme case: there are 1000 rows and user limits to 1000 rows. With the optimization,
suppose there are 10 partitions, each sorting 100 rows within that partition. All of the sorted
100 row set  (total 1000) will be shuffled to one reducer doing global sorting. The last step
may not be as costly as the global shuffle w/o the optimization because each 100 row set is
already sorted. (I'm not entirely sure if Spark can take advantage of that, though.)

It might be sufficient to provide a configuration to turn this optimization off if user knows
what he/she is doing. Otherwise, the optimization should be on by default.


was (Author: xuefuz):
Re: turning the optimization when limit number is too big

It might seem over-engineering if we are trying to be smart. In my opinion, it's very rare
that LIMIT comes with a big number compared to the total number of rows. The penalty comes
only when the limit number is close to the total number of rows. Still, it's not clear how
much the penalty is.

Consider an extreme case: there are 1000 rows and user limits to 1000 rows. With the optimization,
suppose there are 10 partitions, each sorting 100 rows within that partition. All of the sorted
100 row set  (total 1000) will be shuffled to one reducer doing global sorting. The last step
may not be as costly as the global shuffle w/o the optimization because each 100 row set is
already sorted. (I'm not entirely sure if Spark can take advantage of that, though.)

It might be sufficient to provide a configuration to turn this optimization off if user knows
what he/she is doing. Otherwise, the optimization should be on by default.

> Investigate the performance of order by limit in HoS
> ----------------------------------------------------
>
>                 Key: HIVE-16840
>                 URL: https://issues.apache.org/jira/browse/HIVE-16840
>             Project: Hive
>          Issue Type: Bug
>            Reporter: liyunzhang_intel
>            Assignee: liyunzhang_intel
>         Attachments: HIVE-16840.patch
>
>
> We found that on 1TB data of TPC-DS, q17 of TPC-DS hanged.
> {code}
>  select  i_item_id
>        ,i_item_desc
>        ,s_state
>        ,count(ss_quantity) as store_sales_quantitycount
>        ,avg(ss_quantity) as store_sales_quantityave
>        ,stddev_samp(ss_quantity) as store_sales_quantitystdev
>        ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov
>        ,count(sr_return_quantity) as_store_returns_quantitycount
>        ,avg(sr_return_quantity) as_store_returns_quantityave
>        ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev
>        ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov
>        ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave
>        ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev
>        ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov
>  from store_sales
>      ,store_returns
>      ,catalog_sales
>      ,date_dim d1
>      ,date_dim d2
>      ,date_dim d3
>      ,store
>      ,item
>  where d1.d_quarter_name = '2000Q1'
>    and d1.d_date_sk = store_sales.ss_sold_date_sk
>    and item.i_item_sk = store_sales.ss_item_sk
>    and store.s_store_sk = store_sales.ss_store_sk
>    and store_sales.ss_customer_sk = store_returns.sr_customer_sk
>    and store_sales.ss_item_sk = store_returns.sr_item_sk
>    and store_sales.ss_ticket_number = store_returns.sr_ticket_number
>    and store_returns.sr_returned_date_sk = d2.d_date_sk
>    and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
>    and store_returns.sr_customer_sk = catalog_sales.cs_bill_customer_sk
>    and store_returns.sr_item_sk = catalog_sales.cs_item_sk
>    and catalog_sales.cs_sold_date_sk = d3.d_date_sk
>    and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3')
>  group by i_item_id
>          ,i_item_desc
>          ,s_state
>  order by i_item_id
>          ,i_item_desc
>          ,s_state
> limit 100;
> {code}
> the reason why the script hanged is because we only use 1 task to implement sort.
> {code}
> STAGE PLANS:
>   Stage: Stage-1
>     Spark
>       Edges:
>         Reducer 10 <- Reducer 9 (SORT, 1)
>         Reducer 2 <- Map 1 (PARTITION-LEVEL SORT, 889), Map 11 (PARTITION-LEVEL SORT,
889)
>         Reducer 3 <- Map 12 (PARTITION-LEVEL SORT, 1009), Reducer 2 (PARTITION-LEVEL
SORT, 1009)
>         Reducer 4 <- Map 13 (PARTITION-LEVEL SORT, 683), Reducer 3 (PARTITION-LEVEL
SORT, 683)
>         Reducer 5 <- Map 14 (PARTITION-LEVEL SORT, 751), Reducer 4 (PARTITION-LEVEL
SORT, 751)
>         Reducer 6 <- Map 15 (PARTITION-LEVEL SORT, 826), Reducer 5 (PARTITION-LEVEL
SORT, 826)
>         Reducer 7 <- Map 16 (PARTITION-LEVEL SORT, 909), Reducer 6 (PARTITION-LEVEL
SORT, 909)
>         Reducer 8 <- Map 17 (PARTITION-LEVEL SORT, 1001), Reducer 7 (PARTITION-LEVEL
SORT, 1001)
>         Reducer 9 <- Reducer 8 (GROUP, 2)
> {code}
> The parallelism of Reducer 9 is 1. It is a orderby limit case so we use 1 task to execute
to ensure the correctness. But the performance is poor.
> the reason why we use 1 task to implement order by limit is [here|https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/spark/SetSparkReducerParallelism.java#L207]



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message