You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@kyuubi.apache.org by "lordk911 (via GitHub)" <gi...@apache.org> on 2023/09/01 14:48:58 UTC
[GitHub] [kyuubi] lordk911 opened a new issue, #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
lordk911 opened a new issue, #5236:
URL: https://github.com/apache/kyuubi/issues/5236
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
### Search before asking
- [X] I have searched in the [issues](https://github.com/apache/kyuubi/issues?q=is%3Aissue) and found no similar issues.
### Describe the bug
1、kyuubi1.7.1 、kyuubi-extension-spark-3-3_2.12-1.7.1.jar (download from maven)、spark 3.3.3
2、insert into ... select ... statment
3、if I user AQE and KyuubiSparkSQLExtension, the Exchange node is RoundRobinPartitioning, and shuffle write data size expand very large, I finally cancel the query
<img width="470" alt="image" src="https://github.com/apache/kyuubi/assets/19989300/fb6a5215-db86-4e56-b546-a5c70ffb6de4">
4、if I not use KyuubiSparkSQLExtension
<img width="387" alt="image" src="https://github.com/apache/kyuubi/assets/19989300/038b7de7-c602-4bb1-b781-ebe707ffb4f0">
5、I'm I get the wrong way to use KyuubiSparkSQLExtension?
### Affects Version(s)
1.7.1
### Kyuubi Server Log Output
_No response_
### Kyuubi Engine Log Output
_No response_
### Kyuubi Server Configurations
_No response_
### Kyuubi Engine Configurations
```yaml
##AQE Adaptive Query Execution
spark.sql.adaptive.enabled true
spark.sql.adaptive.forceApply false
spark.sql.adaptive.logLevel info
spark.sql.adaptive.advisoryPartitionSizeInBytes 256m
spark.sql.adaptive.coalescePartitions.enabled true
spark.sql.adaptive.coalescePartitions.minPartitionNum 1
spark.sql.adaptive.coalescePartitions.initialPartitionNum 2000
spark.sql.adaptive.fetchShuffleBlocksInBatch true
spark.sql.adaptive.localShuffleReader.enabled true
spark.sql.adaptive.skewJoin.enabled true
spark.sql.adaptive.skewJoin.skewedPartitionFactor 5
spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes 400MB
spark.sql.adaptive.nonEmptyPartitionRatioForBroadcastJoin 0.2
spark.sql.adaptive.autoBroadcastJoinThreshold 100MB
spark.sql.adaptive.maxShuffledHashJoinLocalMapThreshold 400MB
spark.sql.autoBroadcastJoinThreshold -1
#spark.sql.extensions org.apache.kyuubi.plugin.spark.authz.ranger.RangerSparkExtension,org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.kyuubi.sql.KyuubiSparkSQLExtension
spark.sql.extensions org.apache.kyuubi.plugin.spark.authz.ranger.RangerSparkExtension,org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions
```
### Additional context
_No response_
### Are you willing to submit PR?
- [ ] Yes. I would be willing to submit a PR with guidance from the Kyuubi community to fix.
- [ ] No. I cannot submit a PR at this time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] pan3793 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "pan3793 (via GitHub)" <gi...@apache.org>.
pan3793 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704549603
@ulysses-you
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] ulysses-you commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "ulysses-you (via GitHub)" <gi...@apache.org>.
ulysses-you commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704578656
@lordk911 how about adding
`set spark.sql.optimizer.inferRebalanceAndSortOrders.enabled=true`;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704662042
@ulysses-you
1、I've change spark-default.conf to :
spark.sql.extensions org.apache.kyuubi.plugin.spark.authz.ranger.RangerSparkExtension,org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.kyuubi.sql.KyuubiSparkSQLExtension
2、then connect to kyuubi :
2.1） set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=true;
2.2）execute test sql : insert into select
2.3) before the InsertIntoHadoopFsRelationCommand there will be a Exchange node with RoundRobinPartitioning
2.4）about 20 minutes later I cancel the query , because the shuffle write data size become larger when I use spark3.2.3 with KyuubiSparkSQLExtension
2.5) set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=false;
2.6) execute test sql : insert into select again
2.7) sql finished with the same output datasize and file number as direct use of spark without KyuubiSparkSQLExtension.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] ulysses-you commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "ulysses-you (via GitHub)" <gi...@apache.org>.
ulysses-you commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704664873
> @lordk911 how about adding `set spark.sql.optimizer.inferRebalanceAndSortOrders.enabled=true`;
have you tried this ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] ulysses-you commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "ulysses-you (via GitHub)" <gi...@apache.org>.
ulysses-you commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704594145
> `set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=false`
have you reverted this config ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704527528
The target table is not a partion table, And datasource with multi table join and sub query.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704574263
> `set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=false`
I think this is same as I don't config KyuubiSparkSQLExtension to spark-default.conf, The core purpose is to transparently merge small files. However, KyuubiSparkSQLExtension may not be suitable for certain scenarios.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704589514
@ulysses-you as same as Direct use of spark
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1713257122
> CREATE TABLE test.items
> USING parquet
> AS
> SELECT id AS i_item_id,
> CAST(rand() * 1000 AS INT) AS i_price
> FROM RANGE(30000000);
>
> CREATE TABLE test.sales
> USING parquet
> AS
> SELECT CASE WHEN rand() < 0.8 THEN 100 ELSE CAST(rand() * 30000000 AS INT) END AS s_item_id,
> CAST(rand() * 100 AS INT) AS s_quantity,
> DATE_ADD(current_date(), - CAST(rand() * 360 AS INT)) AS s_date
> FROM RANGE(1000000000);
>
> create table IF NOT EXISTS test.aqe_kyuubi_extendtion(
> s_date string,
> total_sales long
> )STORED AS parquet;
>
> set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=true;
>
> truncate table test.aqe_kyuubi_extendtion;
>
> insert into test.aqe_kyuubi_extendtion
> SELECT s_date, s_quantity * i_price AS total_sales
> FROM test.sales
> JOIN test.items ON s_item_id = i_item_id;
I think I've give out the example data above,
When using KyuubiSparkSQLExtension, the number of output files is 69, with a total size of 2.7GB; without using KyuubiSparkSQLExtension, the number of output files is 49, with a total size of 2.5GB.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704673445
I've tried this before, no help.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
Re: [I] [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much [kyuubi]
Posted by "akenO8 (via GitHub)" <gi...@apache.org>.
akenO8 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-3717150899
I also encountered this problem. The written data has expanded from 90G to 130G. Is there a solution to this problem? plz
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] ulysses-you commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "ulysses-you (via GitHub)" <gi...@apache.org>.
ulysses-you commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1704556410
`set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=false`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] lordk911 commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "lordk911 (via GitHub)" <gi...@apache.org>.
lordk911 commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1705087975
```
CREATE TABLE test.items
USING parquet
AS
SELECT id AS i_item_id,
CAST(rand() * 1000 AS INT) AS i_price
FROM RANGE(30000000);
CREATE TABLE test.sales
USING parquet
AS
SELECT CASE WHEN rand() < 0.8 THEN 100 ELSE CAST(rand() * 30000000 AS INT) END AS s_item_id,
CAST(rand() * 100 AS INT) AS s_quantity,
DATE_ADD(current_date(), - CAST(rand() * 360 AS INT)) AS s_date
FROM RANGE(1000000000);
create table IF NOT EXISTS test.aqe_kyuubi_extendtion(
s_date string,
total_sales long
)STORED AS parquet;
set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=true;
truncate table test.aqe_kyuubi_extendtion;
insert into test.aqe_kyuubi_extendtion
SELECT s_date, s_quantity * i_price AS total_sales
FROM test.sales
JOIN test.items ON s_item_id = i_item_id;
```
```
set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=false;
truncate table test.aqe_kyuubi_extendtion;
insert into test.aqe_kyuubi_extendtion
SELECT s_date, s_quantity * i_price AS total_sales
FROM test.sales
JOIN test.items ON s_item_id = i_item_id;
```
will this help?
also test with set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=true; and set spark.sql.optimizer.inferRebalanceAndSortOrders.enabled=true; got the same result as just set spark.sql.optimizer.insertRepartitionBeforeWrite.enabled=true;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org
[GitHub] [kyuubi] ulysses-you commented on issue #5236: [Bug] KyuubiSparkSQLExtension will cause shuffle write data size expand too much
Posted by "ulysses-you (via GitHub)" <gi...@apache.org>.
ulysses-you commented on issue #5236:
URL: https://github.com/apache/kyuubi/issues/5236#issuecomment-1710975984
@lordk911 I'm not sure your data view looks like. Maybe you can try [Z-order](https://kyuubi.readthedocs.io/en/v1.7.1-rc0/extensions/engines/spark/z-order.html)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@kyuubi.apache.org
For additional commands, e-mail: notifications-help@kyuubi.apache.org