[jira] [Commented] (SPARK-43526) when shuffle hash join is enabled, q95 performance deteriorates
[ https://issues.apache.org/jira/browse/SPARK-43526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17724099#comment-17724099 ] Yuming Wang commented on SPARK-43526: - [~caican] Thank you for investigation. > when shuffle hash join is enabled, q95 performance deteriorates > --- > > Key: SPARK-43526 > URL: https://issues.apache.org/jira/browse/SPARK-43526 > Project: Spark > Issue Type: Improvement > Components: SQL >Affects Versions: 3.1.2, 3.2.0, 3.3.2 >Reporter: caican >Priority: Major > Attachments: image-2023-05-16-21-21-35-493.png, > image-2023-05-16-21-22-16-170.png, image-2023-05-16-21-23-35-237.png, > image-2023-05-16-21-24-09-182.png, image-2023-05-16-21-28-11-514.png, > image-2023-05-16-21-28-44-163.png, image-2023-05-17-16-53-42-302.png, > image-2023-05-17-16-54-59-053.png, image-2023-05-19-10-43-51-747.png, > shuffle1.png, sort1.png, sort2.png > > > Testing with 5TB dataset, the performance of q95 in tpcds deteriorates when > shuffle hash join is enabled and the performance is better when sortMergeJoin > is used. > > Performance difference: from 3.9min(sortMergeJoin) to > 8.1min(shuffledHashJoin) > > 1. enable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-44-163.png|width=935,height=64! > !image-2023-05-16-21-21-35-493.png|width=924,height=502! > 2. disable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-11-514.png|width=922,height=67! > !image-2023-05-16-21-22-16-170.png|width=934,height=477! > > And when shuffledHashJoin is enabled, gc is very serious, > !image-2023-05-16-21-23-35-237.png|width=929,height=570! > > but sortMergeJoin executes without this problem. > !image-2023-05-16-21-24-09-182.png|width=931,height=573! > > Any suggestions on how to solve it？Thanks! > > -- This message was sent by Atlassian Jira (v8.20.10#820010) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-43526) when shuffle hash join is enabled, q95 performance deteriorates
[
https://issues.apache.org/jira/browse/SPARK-43526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17724080#comment-17724080
]
caican commented on SPARK-43526:
I find that the shuffle hash join is slower than the sort merge join because
the sort node is added after two shuffle hash joins, and the number of data
bars of the two shuffle hash joins expands a lot.
I overwrote q95, after closing shuffle hash join and adding sort operation
after corresponding join nodes, q95 execution also became slow.
The execution plan before I rewrite q95 sql is as follows:
```Sort merge join```
!sort1.png|width=926,height=473!
```shuffle hash join```
!shuffle1.png|width=921,height=441!
The execution plan after I rewrite q95 sql is as follows:
!sort2.png|width=936,height=496!
the sort operation was added after the corresponding join nodes, and the
execution was slower than shuffle hash join.
And it can be confirmed that the performance deteriorates after the shuffle
hash join function is enabled because a large amount of data is sorted.
!image-2023-05-19-10-43-51-747.png|width=932,height=50!
q95 sql with sort operation added```
set
spark.sql.optimizer.excludedRules="org.apache.spark.sql.catalyst.optimizer.EliminateSorts";
set spark.sql.execution.removeRedundantSorts=false;
WITH ws_wh AS ( SELECT ws1.ws_order_number, ws1.ws_warehouse_sk wh1,
ws2.ws_warehouse_sk wh2 FROM web_sales ws1, web_sales ws2 WHERE
ws1.ws_order_number=ws2.ws_order_number AND
ws1.ws_warehouse_sk<>ws2.ws_warehouse_sk SORT BY ws1.ws_order_number ), tmp1 as
( SELECT ws_order_number FROM ws_wh ), tmp2 as ( SELECT wr_order_number FROM
web_returns, ws_wh WHERE wr_order_number=ws_wh.ws_order_number SORT BY
wr_order_number ) SELECT count(DISTINCT ws_order_number) AS `order count `,
sum(ws_ext_ship_cost) AS `total shipping cost `, sum(ws_net_profit) AS `total
net profit ` FROM web_sales ws1 left semi join tmp1 on
ws1.ws_order_number=tmp1.ws_order_number left semi join tmp2 on
ws1.ws_order_number=tmp2.wr_order_number join date_dim on
ws1.ws_ship_date_sk=date_dim.d_date_sk join customer_address on
ws1.ws_ship_addr_sk=customer_address.ca_address_sk join web_site on
ws1.ws_web_site_sk=web_site.web_site_sk WHERE d_date BETWEEN '1999-02-01' AND
(CAST('1999-02-01' AS DATE)+INTERVAL 60 DAY) AND ws1.ws_ship_date_sk=d_date_sk
AND ws1.ws_ship_addr_sk=ca_address_sk AND ca_state='IL' AND
ws1.ws_web_site_sk=web_site_sk AND web_company_name='pri' ORDER BY
count(DISTINCT ws_order_number) LIMIT 100
```
> when shuffle hash join is enabled, q95 performance deteriorates
> ---
>
> Key: SPARK-43526
> URL: https://issues.apache.org/jira/browse/SPARK-43526
> Project: Spark
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 3.1.2, 3.2.0
>Reporter: caican
>Priority: Major
> Attachments: image-2023-05-16-21-21-35-493.png,
> image-2023-05-16-21-22-16-170.png, image-2023-05-16-21-23-35-237.png,
> image-2023-05-16-21-24-09-182.png, image-2023-05-16-21-28-11-514.png,
> image-2023-05-16-21-28-44-163.png, image-2023-05-17-16-53-42-302.png,
> image-2023-05-17-16-54-59-053.png, image-2023-05-19-10-43-51-747.png,
> shuffle1.png, sort1.png, sort2.png
>
>
> Testing with 5TB dataset, the performance of q95 in tpcds deteriorates when
> shuffle hash join is enabled and the performance is better when sortMergeJoin
> is used.
>
> Performance difference: from 3.9min(sortMergeJoin) to
> 8.1min(shuffledHashJoin)
>
> 1. enable shuffledHashJoin, the execution plan is as follows:
> !image-2023-05-16-21-28-44-163.png|width=935,height=64!
> !image-2023-05-16-21-21-35-493.png|width=924,height=502!
> 2. disable shuffledHashJoin, the execution plan is as follows:
> !image-2023-05-16-21-28-11-514.png|width=922,height=67!
> !image-2023-05-16-21-22-16-170.png|width=934,height=477!
>
> And when shuffledHashJoin is enabled, gc is very serious,
> !image-2023-05-16-21-23-35-237.png|width=929,height=570!
>
> but sortMergeJoin executes without this problem.
> !image-2023-05-16-21-24-09-182.png|width=931,height=573!
>
> Any suggestions on how to solve it？Thanks!
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-43526) when shuffle hash join is enabled, q95 performance deteriorates
[ https://issues.apache.org/jira/browse/SPARK-43526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17723388#comment-17723388 ] caican commented on SPARK-43526: [~yumwang] We plan to use shuffledHashJoin preferentially to eliminate sort consumption when the small table meets a certain threshold, but q95 in tpcds has a serious performance regression and we are not sure if it can be turned on by default. with shuffledHashJoin: !image-2023-05-17-16-53-42-302.png|width=691,height=344! without shuffledHashJoin: !image-2023-05-17-16-54-59-053.png|width=722,height=319! > when shuffle hash join is enabled, q95 performance deteriorates > --- > > Key: SPARK-43526 > URL: https://issues.apache.org/jira/browse/SPARK-43526 > Project: Spark > Issue Type: Improvement > Components: SQL >Affects Versions: 3.1.2, 3.2.0 >Reporter: caican >Priority: Major > Attachments: image-2023-05-16-21-21-35-493.png, > image-2023-05-16-21-22-16-170.png, image-2023-05-16-21-23-35-237.png, > image-2023-05-16-21-24-09-182.png, image-2023-05-16-21-28-11-514.png, > image-2023-05-16-21-28-44-163.png, image-2023-05-17-16-53-42-302.png, > image-2023-05-17-16-54-59-053.png > > > Testing with 5TB dataset, the performance of q95 in tpcds deteriorates when > shuffle hash join is enabled and the performance is better when sortMergeJoin > is used. > > Performance difference: from 3.9min(sortMergeJoin) to > 8.1min(shuffledHashJoin) > > 1. enable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-44-163.png|width=935,height=64! > !image-2023-05-16-21-21-35-493.png|width=924,height=502! > 2. disable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-11-514.png|width=922,height=67! > !image-2023-05-16-21-22-16-170.png|width=934,height=477! > > And when shuffledHashJoin is enabled, gc is very serious, > !image-2023-05-16-21-23-35-237.png|width=929,height=570! > > but sortMergeJoin executes without this problem. > !image-2023-05-16-21-24-09-182.png|width=931,height=573! > > Any suggestions on how to solve it？Thanks! > > -- This message was sent by Atlassian Jira (v8.20.10#820010) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-43526) when shuffle hash join is enabled, q95 performance deteriorates
[ https://issues.apache.org/jira/browse/SPARK-43526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17723338#comment-17723338 ] Yuming Wang commented on SPARK-43526: - Why do you prefer shuffle hash join? > when shuffle hash join is enabled, q95 performance deteriorates > --- > > Key: SPARK-43526 > URL: https://issues.apache.org/jira/browse/SPARK-43526 > Project: Spark > Issue Type: Improvement > Components: SQL >Affects Versions: 3.1.2, 3.2.0 >Reporter: caican >Priority: Major > Attachments: image-2023-05-16-21-21-35-493.png, > image-2023-05-16-21-22-16-170.png, image-2023-05-16-21-23-35-237.png, > image-2023-05-16-21-24-09-182.png, image-2023-05-16-21-28-11-514.png, > image-2023-05-16-21-28-44-163.png > > > Testing with 5TB dataset, the performance of q95 in tpcds deteriorates when > shuffle hash join is enabled and the performance is better when sortMergeJoin > is used. > > Performance difference: from 3.9min(sortMergeJoin) to > 8.1min(shuffledHashJoin) > > 1. enable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-44-163.png|width=935,height=64! > !image-2023-05-16-21-21-35-493.png|width=924,height=502! > 2. disable shuffledHashJoin, the execution plan is as follows: > !image-2023-05-16-21-28-11-514.png|width=922,height=67! > !image-2023-05-16-21-22-16-170.png|width=934,height=477! > > And when shuffledHashJoin is enabled, gc is very serious, > !image-2023-05-16-21-23-35-237.png|width=929,height=570! > > but sortMergeJoin executes without this problem. > !image-2023-05-16-21-24-09-182.png|width=931,height=573! > > Any suggestions on how to solve it？Thanks! > > -- This message was sent by Atlassian Jira (v8.20.10#820010) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
