You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Daniel Vol <dv...@undertone.com> on 2024/08/20 19:25:21 UTC
Bucketing performance question
Hello,
We have a great scenario for using bucketing (at least as per definition) :
Few data tables built hourly and partitioned by date + hour
Several processes that join those tables by ID field many times
One table is pretty big (~100G hourly), others are relatively small (but
not small enough for broadcast), all joins are LEFT into the big table.
To test bucketing I took one day of data (~4.5TB in a big table) and only
two tables - one big and one small, left join between and count as an
action.
As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
- non bucketed tables ~ 3 min to perform count
- bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count
(huge mem + disk spill)
- bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
this is the data from spark ui:
*non bucketed:*
[image: image.png]
[image: image.png]
So we can see it reads 31525 files and 470G data (out of 4.5T total file
size), uses shuffle (1000 partitions) and finishes in 3.1 min
*Bucketed (to 16K buckets):*SortMerge JOIN:
[image: image.png]
[image: image.png]
Here we see it reads 16000 files and 245G of data (out of 4.5T total file
size) NO shuffle, BUT SORT instead
Using hint for SHUFFLE_HASH Join improves a bit but still bad
t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
[image: image.png]
[image: image.png]
*The code to create data:*df1
.withColumn("id1", ...)
.repartition(16000, col("id1"))
.write
.sortBy("id1") // tried with sortWithinPartition instead - faster write,
same read
.bucketBy(16000, "id1")
.partitionBy("dt", "hr")
.format("parquet").mode("overwrite")
.option("path", "...")
.saveAsTable("table_1_16000")
df2
.withColumn("id2", ...)
.repartition(16000, col("id2"))
.write
.sortBy("id2")
.bucketBy(16000, "id2")
.partitionBy("dt", "hr")
.format("parquet").mode("overwrite")
.option("path", "...")
.saveAsTable("table_2_16000")
To test join (in Zeppelin):
val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
t1.join(t2, col("id1") === col("id2"), "left).count
So it looks like bucketing doesn't use parquet metadata to check if data is
already sorted on read - seriously affecting performance.
Just to mention - I tried to run it with 160 buckets (instead of 16000) and
though each task reads only 1.4G of data => ~23G per instance (each
instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge
spill for both mem + disk - per my assumption due to heavy (likely
unnecessary) sort:
[image: image.png]
Few questions:
- is Spark supposed to use sort information from a parquet file?
- if it is - should there be any difference in read after using *sort* or
*sortWithinPartition*?
- is there a way somehow to force it to use sort metadata or skip sort
during execution?
It is very odd that full shuffling performs much (25%) faster than
bucketing.
Especially when with bucketing it reads half amount of files (16K instead
of 35K) and much less total input data (270G instead of 470G)
Thanks!
Daniel
Re: Bucketing performance question
Posted by Mich Talebzadeh <mi...@gmail.com>.
Quick observation
When you build your data using *sortWithinPartition()*, *it only ensures
that the data is sorted within individual partitions. However, this does
not guarantee that the data is globally sorted across all partitions. *Because
of this, even if you enable the
spark.sql.legacy.bucketedTableScan.outputOrdering
setting, Spark cannot fully rely on the assumption that the data is
globally ordered according to the bucket columns. As a result, Spark may
still introduce an additional sort stage during operations like
SortMergeJoin, where global ordering is necessary.
HTH
Mich Talebzadeh,
Architect | Data Engineer | Data Science | Financial Crime
PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial College
London <https://en.wikipedia.org/wiki/Imperial_College_London>
London, United Kingdom
view my Linkedin profile
<https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
https://en.everybodywiki.com/Mich_Talebzadeh
*Disclaimer:* The information provided is correct to the best of my
knowledge but of course cannot be guaranteed . It is essential to note
that, as with any advice, quote "one test result is worth one-thousand
expert opinions (Werner <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von
Braun <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
On Fri, 23 Aug 2024 at 22:13, Daniel Vol <dv...@undertone.com> wrote:
> One more observation - parameter spark.sql.legacy.bucketedTableScan.outputOrdering
> works only if used with sortBy().
> If I build my data with sortWithinPartition() - I still get sort stage
> during the join.
>
> On Thu, Aug 22, 2024 at 8:44 PM Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
>> OK some progress.
>>
>> "... Now I have to check the code to find out what causes in some cases
>> for sort / shuffle... "
>>
>> # Perform the join operation, something like below
>> result = large_df.join(small_df, large_df["id1"] == small_df["id2"],
>> "left")
>> # Get and print the extended execution plan
>> result.explain('extended')
>>
>> HTH
>>
>> Mich Talebzadeh,
>>
>> Architect | Data Engineer | Data Science | Financial Crime
>> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial
>> College London <https://en.wikipedia.org/wiki/Imperial_College_London>
>> London, United Kingdom
>>
>>
>> view my Linkedin profile
>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>
>>
>> https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>>
>> *Disclaimer:* The information provided is correct to the best of my
>> knowledge but of course cannot be guaranteed . It is essential to note
>> that, as with any advice, quote "one test result is worth one-thousand
>> expert opinions (Werner
>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von Braun
>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>>
>>
>> On Thu, 22 Aug 2024 at 14:35, Daniel Vol <dv...@undertone.com> wrote:
>>
>>> Tested now independently as a simple join + count - spark.sql.legacy.
>>> bucketedTableScan.outputOrdering = true
>>> For standalone simple join it looks great and works:
>>> [image: image.png]
>>> not bad time (though expected to even more - get down out of 2 min) and
>>> no shuffle / sort:
>>> [image: image.png]
>>>
>>> Now I have to check the code to find out what causes in some cases for
>>> sort / shuffle...
>>> Thank you @omanor@speedata.io <om...@speedata.io> !
>>>
>>> Daniel.
>>>
>>>
>>>
>>> On Thu, Aug 22, 2024 at 3:58 PM Daniel Vol <dv...@undertone.com> wrote:
>>>
>>>> Hi Mich and Ofir,
>>>>
>>>> First of all - thanks for an answer.
>>>>
>>>> We use Spark 3.2 so I think spark.sql.join.preferSortMergeJoin is true
>>>> by default.
>>>> Setting it to true - gives nothing in terms of performance and I
>>>> still see the "sort" part in JOIN.
>>>> spark.sql.legacy.bucketedTableScan.outputOrdering - looks like it
>>>> removes part of the sorts in the big process but not yet tested it
>>>> independently to see the dedicated performance effect - will update once
>>>> the test is done.
>>>>
>>>> Daniel.
>>>>
>>>> On Wed, Aug 21, 2024 at 7:56 PM Mich Talebzadeh <
>>>> mich.talebzadeh@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> You have not mentioned the version of Spark used here.
>>>>>
>>>>> The outcome you mentioned was that the non-bucketed join outperformed
>>>>> the bucketed join by approximately 25%, even though it processed more data
>>>>> and required shuffling. Specifically, despite the reduced number of files
>>>>> read and less data processed in the bucketed join (16K files vs. 35K, 245
>>>>> GB vs. 470 GB), the performance did not improve as expected. This was
>>>>> likely because Spark still performed sorting during the join, despite the
>>>>> data already being sorted within buckets. Additionally, you concluded that
>>>>> Spark does not use sort metadata from Parquet files during execution, which
>>>>> led to unnecessary sorting overhead.
>>>>>
>>>>> Recap:
>>>>>
>>>>>
>>>>> - Bucketing can reduce shuffling, but it introduces sorting
>>>>> overhead if Spark does not recognize that the data is already sorted.
>>>>> - Spark does not leverage sort metadata from Parquet files, which
>>>>> can lead to inefficient processing when using bucketed tables.
>>>>> - The number of buckets should be carefully chosen to balance
>>>>> shuffle reduction and memory/disk usage. In this case, reducing the number
>>>>> of buckets to 16000 was not effective due to unnecessary sorting.
>>>>>
>>>>>
>>>>> What you can do
>>>>>
>>>>> Setting
>>>>>
>>>>> - spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") //
>>>>> turn on sort-merge join, to skip the sorting step
>>>>>
>>>>> Ensure that when you write the data, you use the sortBy or
>>>>> sortWithinPartitions function to maintain the order within each bucket.
>>>>> This will help Spark recognize that the data is already sorted when reading
>>>>> it.
>>>>>
>>>>> df
>>>>> .repartition(numBuckets, col("id")) // Repartition by the "id" column
>>>>> to match the number of buckets
>>>>> .sortWithinPartitions("id") // Sort within each partition (not after
>>>>> repartition)
>>>>> .write .bucketBy(numBuckets, "id") // Specify the number of buckets
>>>>> and the column to bucket by
>>>>> .format("parquet")
>>>>> .saveAsTable("bucketed_table") // Save the data as a bucketed table
>>>>> in Parquet format
>>>>>
>>>>> Use hints in queries
>>>>>
>>>>> - df1.join(df2.hint("merge"), col("id1") === col("id2"),
>>>>> "left").count
>>>>>
>>>>>
>>>>> HTH
>>>>>
>>>>> Mich Talebzadeh,
>>>>>
>>>>> Architect | Data Engineer | Data Science | Financial Crime
>>>>> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial
>>>>> College London <https://en.wikipedia.org/wiki/Imperial_College_London>
>>>>>
>>>>> London, United Kingdom
>>>>>
>>>>>
>>>>> view my Linkedin profile
>>>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>>>
>>>>>
>>>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>>>
>>>>>
>>>>>
>>>>> *Disclaimer:* The information provided is correct to the best of my
>>>>> knowledge but of course cannot be guaranteed . It is essential to note
>>>>> that, as with any advice, quote "one test result is worth one-thousand
>>>>> expert opinions (Werner
>>>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von Braun
>>>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>>>>>
>>>>>
>>>>> On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> We have a great scenario for using bucketing (at least as per
>>>>>> definition) :
>>>>>> Few data tables built hourly and partitioned by date + hour
>>>>>> Several processes that join those tables by ID field many times
>>>>>>
>>>>>> One table is pretty big (~100G hourly), others are relatively small
>>>>>> (but not small enough for broadcast), all joins are LEFT into the big table.
>>>>>>
>>>>>> To test bucketing I took one day of data (~4.5TB in a big table) and
>>>>>> only two tables - one big and one small, left join between and count as an
>>>>>> action.
>>>>>>
>>>>>> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd
>>>>>> disks):
>>>>>> - non bucketed tables ~ 3 min to perform count
>>>>>> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform
>>>>>> count (huge mem + disk spill)
>>>>>> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>>>>>>
>>>>>> this is the data from spark ui:
>>>>>> *non bucketed:*
>>>>>> [image: image.png]
>>>>>> [image: image.png]
>>>>>> So we can see it reads 31525 files and 470G data (out of 4.5T total
>>>>>> file size), uses shuffle (1000 partitions) and finishes in 3.1 min
>>>>>>
>>>>>>
>>>>>> *Bucketed (to 16K buckets):*SortMerge JOIN:
>>>>>> [image: image.png]
>>>>>> [image: image.png]
>>>>>> Here we see it reads 16000 files and 245G of data (out of 4.5T total
>>>>>> file size) NO shuffle, BUT SORT instead
>>>>>>
>>>>>> Using hint for SHUFFLE_HASH Join improves a bit but still bad
>>>>>> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"),
>>>>>> "left).count
>>>>>>
>>>>>> [image: image.png]
>>>>>>
>>>>>> [image: image.png]
>>>>>>
>>>>>>
>>>>>>
>>>>>> *The code to create data:*df1
>>>>>> .withColumn("id1", ...)
>>>>>> .repartition(16000, col("id1"))
>>>>>> .write
>>>>>> .sortBy("id1") // tried with sortWithinPartition instead - faster
>>>>>> write, same read
>>>>>> .bucketBy(16000, "id1")
>>>>>> .partitionBy("dt", "hr")
>>>>>> .format("parquet").mode("overwrite")
>>>>>> .option("path", "...")
>>>>>> .saveAsTable("table_1_16000")
>>>>>>
>>>>>> df2
>>>>>> .withColumn("id2", ...)
>>>>>> .repartition(16000, col("id2"))
>>>>>> .write
>>>>>> .sortBy("id2")
>>>>>> .bucketBy(16000, "id2")
>>>>>> .partitionBy("dt", "hr")
>>>>>> .format("parquet").mode("overwrite")
>>>>>> .option("path", "...")
>>>>>> .saveAsTable("table_2_16000")
>>>>>>
>>>>>> To test join (in Zeppelin):
>>>>>> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
>>>>>> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
>>>>>> t1.join(t2, col("id1") === col("id2"), "left).count
>>>>>>
>>>>>> So it looks like bucketing doesn't use parquet metadata to check if
>>>>>> data is already sorted on read - seriously affecting performance.
>>>>>>
>>>>>> Just to mention - I tried to run it with 160 buckets (instead of
>>>>>> 16000) and though each task reads only 1.4G of data => ~23G per instance
>>>>>> (each instance has has 120G of memory + 3.5T dedicated ssd disk), I get a
>>>>>> huge spill for both mem + disk - per my assumption due to heavy (likely
>>>>>> unnecessary) sort:
>>>>>> [image: image.png]
>>>>>>
>>>>>> Few questions:
>>>>>> - is Spark supposed to use sort information from a parquet file?
>>>>>> - if it is - should there be any difference in read after using
>>>>>> *sort* or *sortWithinPartition*?
>>>>>> - is there a way somehow to force it to use sort metadata or skip
>>>>>> sort during execution?
>>>>>>
>>>>>> It is very odd that full shuffling performs much (25%) faster than
>>>>>> bucketing.
>>>>>> Especially when with bucketing it reads half amount of files (16K
>>>>>> instead of 35K) and much less total input data (270G instead of 470G)
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>> Daniel
>>>>>>
>>>>>
>>>>
>>>> --
>>>> *Daniel Vol | Undertone*
>>>> Data Engineer
>>>> *M *+972-50-8861615
>>>>
>>>> www.undertone.com
>>>>
>>>>
>>>> Undertone by Perion (NASDAQ: PERI)
>>>>
>>>>
>>>> .
>>>>
>>>> This message may contain confidential and privileged information. It is
>>>> intended to be read only by the individual or entity to whom it is
>>>> addressed or by their designee.
>>>> Suppose the reader of this message is not the intended recipient. In
>>>> that case, you are on notice that any use, copy, disclosure, or
>>>> distribution of this message
>>>> is strictly prohibited. If you have received this message in error,
>>>> please notify the sender by replying via email or telephone and delete or
>>>> destroy any copy of this message.
>>>> Please note that the sender of this message and their employer collect
>>>> and process the personal data contained in this message and any
>>>> of its correspondences as outlined in the applicable privacy policy
>>>> available on its website.
>>>>
>>>
>>>
>>> --
>>> *Daniel Vol | Undertone*
>>> Data Engineer
>>> *M *+972-50-8861615
>>>
>>> www.undertone.com
>>>
>>>
>>> Undertone by Perion (NASDAQ: PERI)
>>>
>>>
>>> .
>>>
>>> This message may contain confidential and privileged information. It is
>>> intended to be read only by the individual or entity to whom it is
>>> addressed or by their designee.
>>> Suppose the reader of this message is not the intended recipient. In
>>> that case, you are on notice that any use, copy, disclosure, or
>>> distribution of this message
>>> is strictly prohibited. If you have received this message in error,
>>> please notify the sender by replying via email or telephone and delete or
>>> destroy any copy of this message.
>>> Please note that the sender of this message and their employer collect
>>> and process the personal data contained in this message and any
>>> of its correspondences as outlined in the applicable privacy policy
>>> available on its website.
>>>
>>
>
> --
> *Daniel Vol | Undertone*
> Data Engineer
> *M *+972-50-8861615
>
> www.undertone.com
>
>
> Undertone by Perion (NASDAQ: PERI)
>
>
> .
>
> This message may contain confidential and privileged information. It is
> intended to be read only by the individual or entity to whom it is
> addressed or by their designee.
> Suppose the reader of this message is not the intended recipient. In that
> case, you are on notice that any use, copy, disclosure, or distribution of
> this message
> is strictly prohibited. If you have received this message in error, please
> notify the sender by replying via email or telephone and delete or destroy
> any copy of this message.
> Please note that the sender of this message and their employer collect and
> process the personal data contained in this message and any
> of its correspondences as outlined in the applicable privacy policy
> available on its website.
>
Re: Bucketing performance question
Posted by Daniel Vol <dv...@undertone.com>.
One more observation - parameter
spark.sql.legacy.bucketedTableScan.outputOrdering
works only if used with sortBy().
If I build my data with sortWithinPartition() - I still get sort stage
during the join.
On Thu, Aug 22, 2024 at 8:44 PM Mich Talebzadeh <mi...@gmail.com>
wrote:
> OK some progress.
>
> "... Now I have to check the code to find out what causes in some cases
> for sort / shuffle... "
>
> # Perform the join operation, something like below
> result = large_df.join(small_df, large_df["id1"] == small_df["id2"],
> "left")
> # Get and print the extended execution plan
> result.explain('extended')
>
> HTH
>
> Mich Talebzadeh,
>
> Architect | Data Engineer | Data Science | Financial Crime
> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial College
> London <https://en.wikipedia.org/wiki/Imperial_College_London>
> London, United Kingdom
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> *Disclaimer:* The information provided is correct to the best of my
> knowledge but of course cannot be guaranteed . It is essential to note
> that, as with any advice, quote "one test result is worth one-thousand
> expert opinions (Werner <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von
> Braun <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>
>
> On Thu, 22 Aug 2024 at 14:35, Daniel Vol <dv...@undertone.com> wrote:
>
>> Tested now independently as a simple join + count - spark.sql.legacy.
>> bucketedTableScan.outputOrdering = true
>> For standalone simple join it looks great and works:
>> [image: image.png]
>> not bad time (though expected to even more - get down out of 2 min) and
>> no shuffle / sort:
>> [image: image.png]
>>
>> Now I have to check the code to find out what causes in some cases for
>> sort / shuffle...
>> Thank you @omanor@speedata.io <om...@speedata.io> !
>>
>> Daniel.
>>
>>
>>
>> On Thu, Aug 22, 2024 at 3:58 PM Daniel Vol <dv...@undertone.com> wrote:
>>
>>> Hi Mich and Ofir,
>>>
>>> First of all - thanks for an answer.
>>>
>>> We use Spark 3.2 so I think spark.sql.join.preferSortMergeJoin is true
>>> by default.
>>> Setting it to true - gives nothing in terms of performance and I
>>> still see the "sort" part in JOIN.
>>> spark.sql.legacy.bucketedTableScan.outputOrdering - looks like it
>>> removes part of the sorts in the big process but not yet tested it
>>> independently to see the dedicated performance effect - will update once
>>> the test is done.
>>>
>>> Daniel.
>>>
>>> On Wed, Aug 21, 2024 at 7:56 PM Mich Talebzadeh <
>>> mich.talebzadeh@gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> You have not mentioned the version of Spark used here.
>>>>
>>>> The outcome you mentioned was that the non-bucketed join outperformed
>>>> the bucketed join by approximately 25%, even though it processed more data
>>>> and required shuffling. Specifically, despite the reduced number of files
>>>> read and less data processed in the bucketed join (16K files vs. 35K, 245
>>>> GB vs. 470 GB), the performance did not improve as expected. This was
>>>> likely because Spark still performed sorting during the join, despite the
>>>> data already being sorted within buckets. Additionally, you concluded that
>>>> Spark does not use sort metadata from Parquet files during execution, which
>>>> led to unnecessary sorting overhead.
>>>>
>>>> Recap:
>>>>
>>>>
>>>> - Bucketing can reduce shuffling, but it introduces sorting
>>>> overhead if Spark does not recognize that the data is already sorted.
>>>> - Spark does not leverage sort metadata from Parquet files, which
>>>> can lead to inefficient processing when using bucketed tables.
>>>> - The number of buckets should be carefully chosen to balance
>>>> shuffle reduction and memory/disk usage. In this case, reducing the number
>>>> of buckets to 16000 was not effective due to unnecessary sorting.
>>>>
>>>>
>>>> What you can do
>>>>
>>>> Setting
>>>>
>>>> - spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") //
>>>> turn on sort-merge join, to skip the sorting step
>>>>
>>>> Ensure that when you write the data, you use the sortBy or
>>>> sortWithinPartitions function to maintain the order within each bucket.
>>>> This will help Spark recognize that the data is already sorted when reading
>>>> it.
>>>>
>>>> df
>>>> .repartition(numBuckets, col("id")) // Repartition by the "id" column
>>>> to match the number of buckets
>>>> .sortWithinPartitions("id") // Sort within each partition (not after
>>>> repartition)
>>>> .write .bucketBy(numBuckets, "id") // Specify the number of buckets
>>>> and the column to bucket by
>>>> .format("parquet")
>>>> .saveAsTable("bucketed_table") // Save the data as a bucketed table in
>>>> Parquet format
>>>>
>>>> Use hints in queries
>>>>
>>>> - df1.join(df2.hint("merge"), col("id1") === col("id2"),
>>>> "left").count
>>>>
>>>>
>>>> HTH
>>>>
>>>> Mich Talebzadeh,
>>>>
>>>> Architect | Data Engineer | Data Science | Financial Crime
>>>> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial
>>>> College London <https://en.wikipedia.org/wiki/Imperial_College_London>
>>>> London, United Kingdom
>>>>
>>>>
>>>> view my Linkedin profile
>>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>>
>>>>
>>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>>
>>>>
>>>>
>>>> *Disclaimer:* The information provided is correct to the best of my
>>>> knowledge but of course cannot be guaranteed . It is essential to note
>>>> that, as with any advice, quote "one test result is worth one-thousand
>>>> expert opinions (Werner
>>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von Braun
>>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>>>>
>>>>
>>>> On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> We have a great scenario for using bucketing (at least as per
>>>>> definition) :
>>>>> Few data tables built hourly and partitioned by date + hour
>>>>> Several processes that join those tables by ID field many times
>>>>>
>>>>> One table is pretty big (~100G hourly), others are relatively small
>>>>> (but not small enough for broadcast), all joins are LEFT into the big table.
>>>>>
>>>>> To test bucketing I took one day of data (~4.5TB in a big table) and
>>>>> only two tables - one big and one small, left join between and count as an
>>>>> action.
>>>>>
>>>>> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
>>>>> - non bucketed tables ~ 3 min to perform count
>>>>> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform
>>>>> count (huge mem + disk spill)
>>>>> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>>>>>
>>>>> this is the data from spark ui:
>>>>> *non bucketed:*
>>>>> [image: image.png]
>>>>> [image: image.png]
>>>>> So we can see it reads 31525 files and 470G data (out of 4.5T total
>>>>> file size), uses shuffle (1000 partitions) and finishes in 3.1 min
>>>>>
>>>>>
>>>>> *Bucketed (to 16K buckets):*SortMerge JOIN:
>>>>> [image: image.png]
>>>>> [image: image.png]
>>>>> Here we see it reads 16000 files and 245G of data (out of 4.5T total
>>>>> file size) NO shuffle, BUT SORT instead
>>>>>
>>>>> Using hint for SHUFFLE_HASH Join improves a bit but still bad
>>>>> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"),
>>>>> "left).count
>>>>>
>>>>> [image: image.png]
>>>>>
>>>>> [image: image.png]
>>>>>
>>>>>
>>>>>
>>>>> *The code to create data:*df1
>>>>> .withColumn("id1", ...)
>>>>> .repartition(16000, col("id1"))
>>>>> .write
>>>>> .sortBy("id1") // tried with sortWithinPartition instead - faster
>>>>> write, same read
>>>>> .bucketBy(16000, "id1")
>>>>> .partitionBy("dt", "hr")
>>>>> .format("parquet").mode("overwrite")
>>>>> .option("path", "...")
>>>>> .saveAsTable("table_1_16000")
>>>>>
>>>>> df2
>>>>> .withColumn("id2", ...)
>>>>> .repartition(16000, col("id2"))
>>>>> .write
>>>>> .sortBy("id2")
>>>>> .bucketBy(16000, "id2")
>>>>> .partitionBy("dt", "hr")
>>>>> .format("parquet").mode("overwrite")
>>>>> .option("path", "...")
>>>>> .saveAsTable("table_2_16000")
>>>>>
>>>>> To test join (in Zeppelin):
>>>>> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
>>>>> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
>>>>> t1.join(t2, col("id1") === col("id2"), "left).count
>>>>>
>>>>> So it looks like bucketing doesn't use parquet metadata to check if
>>>>> data is already sorted on read - seriously affecting performance.
>>>>>
>>>>> Just to mention - I tried to run it with 160 buckets (instead of
>>>>> 16000) and though each task reads only 1.4G of data => ~23G per instance
>>>>> (each instance has has 120G of memory + 3.5T dedicated ssd disk), I get a
>>>>> huge spill for both mem + disk - per my assumption due to heavy (likely
>>>>> unnecessary) sort:
>>>>> [image: image.png]
>>>>>
>>>>> Few questions:
>>>>> - is Spark supposed to use sort information from a parquet file?
>>>>> - if it is - should there be any difference in read after using *sort*
>>>>> or *sortWithinPartition*?
>>>>> - is there a way somehow to force it to use sort metadata or skip sort
>>>>> during execution?
>>>>>
>>>>> It is very odd that full shuffling performs much (25%) faster than
>>>>> bucketing.
>>>>> Especially when with bucketing it reads half amount of files (16K
>>>>> instead of 35K) and much less total input data (270G instead of 470G)
>>>>>
>>>>> Thanks!
>>>>>
>>>>> Daniel
>>>>>
>>>>
>>>
>>> --
>>> *Daniel Vol | Undertone*
>>> Data Engineer
>>> *M *+972-50-8861615
>>>
>>> www.undertone.com
>>>
>>>
>>> Undertone by Perion (NASDAQ: PERI)
>>>
>>>
>>> .
>>>
>>> This message may contain confidential and privileged information. It is
>>> intended to be read only by the individual or entity to whom it is
>>> addressed or by their designee.
>>> Suppose the reader of this message is not the intended recipient. In
>>> that case, you are on notice that any use, copy, disclosure, or
>>> distribution of this message
>>> is strictly prohibited. If you have received this message in error,
>>> please notify the sender by replying via email or telephone and delete or
>>> destroy any copy of this message.
>>> Please note that the sender of this message and their employer collect
>>> and process the personal data contained in this message and any
>>> of its correspondences as outlined in the applicable privacy policy
>>> available on its website.
>>>
>>
>>
>> --
>> *Daniel Vol | Undertone*
>> Data Engineer
>> *M *+972-50-8861615
>>
>> www.undertone.com
>>
>>
>> Undertone by Perion (NASDAQ: PERI)
>>
>>
>> .
>>
>> This message may contain confidential and privileged information. It is
>> intended to be read only by the individual or entity to whom it is
>> addressed or by their designee.
>> Suppose the reader of this message is not the intended recipient. In that
>> case, you are on notice that any use, copy, disclosure, or distribution of
>> this message
>> is strictly prohibited. If you have received this message in error,
>> please notify the sender by replying via email or telephone and delete or
>> destroy any copy of this message.
>> Please note that the sender of this message and their employer collect
>> and process the personal data contained in this message and any
>> of its correspondences as outlined in the applicable privacy policy
>> available on its website.
>>
>
--
*Daniel Vol | Undertone*
Data Engineer
*M *+972-50-8861615
www.undertone.com
Undertone by Perion (NASDAQ: PERI)
.
This message may contain confidential and privileged information. It is
intended to be read only by the individual or entity to whom it is
addressed or by their designee.
Suppose the reader of this message is not the intended recipient. In that
case, you are on notice that any use, copy, disclosure, or distribution of
this message
is strictly prohibited. If you have received this message in error, please
notify the sender by replying via email or telephone and delete or destroy
any copy of this message.
Please note that the sender of this message and their employer collect and
process the personal data contained in this message and any
of its correspondences as outlined in the applicable privacy policy
available on its website.
Re: Bucketing performance question
Posted by Mich Talebzadeh <mi...@gmail.com>.
OK some progress.
"... Now I have to check the code to find out what causes in some cases for
sort / shuffle... "
# Perform the join operation, something like below
result = large_df.join(small_df, large_df["id1"] == small_df["id2"], "left")
# Get and print the extended execution plan
result.explain('extended')
HTH
Mich Talebzadeh,
Architect | Data Engineer | Data Science | Financial Crime
PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial College
London <https://en.wikipedia.org/wiki/Imperial_College_London>
London, United Kingdom
view my Linkedin profile
<https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
https://en.everybodywiki.com/Mich_Talebzadeh
*Disclaimer:* The information provided is correct to the best of my
knowledge but of course cannot be guaranteed . It is essential to note
that, as with any advice, quote "one test result is worth one-thousand
expert opinions (Werner <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von
Braun <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
On Thu, 22 Aug 2024 at 14:35, Daniel Vol <dv...@undertone.com> wrote:
> Tested now independently as a simple join + count - spark.sql.legacy.
> bucketedTableScan.outputOrdering = true
> For standalone simple join it looks great and works:
> [image: image.png]
> not bad time (though expected to even more - get down out of 2 min) and no
> shuffle / sort:
> [image: image.png]
>
> Now I have to check the code to find out what causes in some cases for
> sort / shuffle...
> Thank you @omanor@speedata.io <om...@speedata.io> !
>
> Daniel.
>
>
>
> On Thu, Aug 22, 2024 at 3:58 PM Daniel Vol <dv...@undertone.com> wrote:
>
>> Hi Mich and Ofir,
>>
>> First of all - thanks for an answer.
>>
>> We use Spark 3.2 so I think spark.sql.join.preferSortMergeJoin is true
>> by default.
>> Setting it to true - gives nothing in terms of performance and I
>> still see the "sort" part in JOIN.
>> spark.sql.legacy.bucketedTableScan.outputOrdering - looks like it
>> removes part of the sorts in the big process but not yet tested it
>> independently to see the dedicated performance effect - will update once
>> the test is done.
>>
>> Daniel.
>>
>> On Wed, Aug 21, 2024 at 7:56 PM Mich Talebzadeh <
>> mich.talebzadeh@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> You have not mentioned the version of Spark used here.
>>>
>>> The outcome you mentioned was that the non-bucketed join outperformed
>>> the bucketed join by approximately 25%, even though it processed more data
>>> and required shuffling. Specifically, despite the reduced number of files
>>> read and less data processed in the bucketed join (16K files vs. 35K, 245
>>> GB vs. 470 GB), the performance did not improve as expected. This was
>>> likely because Spark still performed sorting during the join, despite the
>>> data already being sorted within buckets. Additionally, you concluded that
>>> Spark does not use sort metadata from Parquet files during execution, which
>>> led to unnecessary sorting overhead.
>>>
>>> Recap:
>>>
>>>
>>> - Bucketing can reduce shuffling, but it introduces sorting overhead
>>> if Spark does not recognize that the data is already sorted.
>>> - Spark does not leverage sort metadata from Parquet files, which
>>> can lead to inefficient processing when using bucketed tables.
>>> - The number of buckets should be carefully chosen to balance
>>> shuffle reduction and memory/disk usage. In this case, reducing the number
>>> of buckets to 16000 was not effective due to unnecessary sorting.
>>>
>>>
>>> What you can do
>>>
>>> Setting
>>>
>>> - spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") //
>>> turn on sort-merge join, to skip the sorting step
>>>
>>> Ensure that when you write the data, you use the sortBy or
>>> sortWithinPartitions function to maintain the order within each bucket.
>>> This will help Spark recognize that the data is already sorted when reading
>>> it.
>>>
>>> df
>>> .repartition(numBuckets, col("id")) // Repartition by the "id" column
>>> to match the number of buckets
>>> .sortWithinPartitions("id") // Sort within each partition (not after
>>> repartition)
>>> .write .bucketBy(numBuckets, "id") // Specify the number of buckets and
>>> the column to bucket by
>>> .format("parquet")
>>> .saveAsTable("bucketed_table") // Save the data as a bucketed table in
>>> Parquet format
>>>
>>> Use hints in queries
>>>
>>> - df1.join(df2.hint("merge"), col("id1") === col("id2"),
>>> "left").count
>>>
>>>
>>> HTH
>>>
>>> Mich Talebzadeh,
>>>
>>> Architect | Data Engineer | Data Science | Financial Crime
>>> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial
>>> College London <https://en.wikipedia.org/wiki/Imperial_College_London>
>>> London, United Kingdom
>>>
>>>
>>> view my Linkedin profile
>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>
>>>
>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>
>>>
>>>
>>> *Disclaimer:* The information provided is correct to the best of my
>>> knowledge but of course cannot be guaranteed . It is essential to note
>>> that, as with any advice, quote "one test result is worth one-thousand
>>> expert opinions (Werner
>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von Braun
>>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>>>
>>>
>>> On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
>>>
>>>> Hello,
>>>>
>>>> We have a great scenario for using bucketing (at least as per
>>>> definition) :
>>>> Few data tables built hourly and partitioned by date + hour
>>>> Several processes that join those tables by ID field many times
>>>>
>>>> One table is pretty big (~100G hourly), others are relatively small
>>>> (but not small enough for broadcast), all joins are LEFT into the big table.
>>>>
>>>> To test bucketing I took one day of data (~4.5TB in a big table) and
>>>> only two tables - one big and one small, left join between and count as an
>>>> action.
>>>>
>>>> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
>>>> - non bucketed tables ~ 3 min to perform count
>>>> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count
>>>> (huge mem + disk spill)
>>>> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>>>>
>>>> this is the data from spark ui:
>>>> *non bucketed:*
>>>> [image: image.png]
>>>> [image: image.png]
>>>> So we can see it reads 31525 files and 470G data (out of 4.5T total
>>>> file size), uses shuffle (1000 partitions) and finishes in 3.1 min
>>>>
>>>>
>>>> *Bucketed (to 16K buckets):*SortMerge JOIN:
>>>> [image: image.png]
>>>> [image: image.png]
>>>> Here we see it reads 16000 files and 245G of data (out of 4.5T total
>>>> file size) NO shuffle, BUT SORT instead
>>>>
>>>> Using hint for SHUFFLE_HASH Join improves a bit but still bad
>>>> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
>>>>
>>>> [image: image.png]
>>>>
>>>> [image: image.png]
>>>>
>>>>
>>>>
>>>> *The code to create data:*df1
>>>> .withColumn("id1", ...)
>>>> .repartition(16000, col("id1"))
>>>> .write
>>>> .sortBy("id1") // tried with sortWithinPartition instead - faster
>>>> write, same read
>>>> .bucketBy(16000, "id1")
>>>> .partitionBy("dt", "hr")
>>>> .format("parquet").mode("overwrite")
>>>> .option("path", "...")
>>>> .saveAsTable("table_1_16000")
>>>>
>>>> df2
>>>> .withColumn("id2", ...)
>>>> .repartition(16000, col("id2"))
>>>> .write
>>>> .sortBy("id2")
>>>> .bucketBy(16000, "id2")
>>>> .partitionBy("dt", "hr")
>>>> .format("parquet").mode("overwrite")
>>>> .option("path", "...")
>>>> .saveAsTable("table_2_16000")
>>>>
>>>> To test join (in Zeppelin):
>>>> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
>>>> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
>>>> t1.join(t2, col("id1") === col("id2"), "left).count
>>>>
>>>> So it looks like bucketing doesn't use parquet metadata to check if
>>>> data is already sorted on read - seriously affecting performance.
>>>>
>>>> Just to mention - I tried to run it with 160 buckets (instead of 16000)
>>>> and though each task reads only 1.4G of data => ~23G per instance (each
>>>> instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge
>>>> spill for both mem + disk - per my assumption due to heavy (likely
>>>> unnecessary) sort:
>>>> [image: image.png]
>>>>
>>>> Few questions:
>>>> - is Spark supposed to use sort information from a parquet file?
>>>> - if it is - should there be any difference in read after using *sort*
>>>> or *sortWithinPartition*?
>>>> - is there a way somehow to force it to use sort metadata or skip sort
>>>> during execution?
>>>>
>>>> It is very odd that full shuffling performs much (25%) faster than
>>>> bucketing.
>>>> Especially when with bucketing it reads half amount of files (16K
>>>> instead of 35K) and much less total input data (270G instead of 470G)
>>>>
>>>> Thanks!
>>>>
>>>> Daniel
>>>>
>>>
>>
>> --
>> *Daniel Vol | Undertone*
>> Data Engineer
>> *M *+972-50-8861615
>>
>> www.undertone.com
>>
>>
>> Undertone by Perion (NASDAQ: PERI)
>>
>>
>> .
>>
>> This message may contain confidential and privileged information. It is
>> intended to be read only by the individual or entity to whom it is
>> addressed or by their designee.
>> Suppose the reader of this message is not the intended recipient. In that
>> case, you are on notice that any use, copy, disclosure, or distribution of
>> this message
>> is strictly prohibited. If you have received this message in error,
>> please notify the sender by replying via email or telephone and delete or
>> destroy any copy of this message.
>> Please note that the sender of this message and their employer collect
>> and process the personal data contained in this message and any
>> of its correspondences as outlined in the applicable privacy policy
>> available on its website.
>>
>
>
> --
> *Daniel Vol | Undertone*
> Data Engineer
> *M *+972-50-8861615
>
> www.undertone.com
>
>
> Undertone by Perion (NASDAQ: PERI)
>
>
> .
>
> This message may contain confidential and privileged information. It is
> intended to be read only by the individual or entity to whom it is
> addressed or by their designee.
> Suppose the reader of this message is not the intended recipient. In that
> case, you are on notice that any use, copy, disclosure, or distribution of
> this message
> is strictly prohibited. If you have received this message in error, please
> notify the sender by replying via email or telephone and delete or destroy
> any copy of this message.
> Please note that the sender of this message and their employer collect and
> process the personal data contained in this message and any
> of its correspondences as outlined in the applicable privacy policy
> available on its website.
>
Re: Bucketing performance question
Posted by Daniel Vol <dv...@undertone.com>.
Tested now independently as a simple join + count - spark.sql.legacy.
bucketedTableScan.outputOrdering = true
For standalone simple join it looks great and works:
[image: image.png]
not bad time (though expected to even more - get down out of 2 min) and no
shuffle / sort:
[image: image.png]
Now I have to check the code to find out what causes in some cases for sort
/ shuffle...
Thank you @omanor@speedata.io <om...@speedata.io> !
Daniel.
On Thu, Aug 22, 2024 at 3:58 PM Daniel Vol <dv...@undertone.com> wrote:
> Hi Mich and Ofir,
>
> First of all - thanks for an answer.
>
> We use Spark 3.2 so I think spark.sql.join.preferSortMergeJoin is true by
> default.
> Setting it to true - gives nothing in terms of performance and I still see
> the "sort" part in JOIN.
> spark.sql.legacy.bucketedTableScan.outputOrdering - looks like it removes
> part of the sorts in the big process but not yet tested it independently to
> see the dedicated performance effect - will update once the test is done.
>
> Daniel.
>
> On Wed, Aug 21, 2024 at 7:56 PM Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
>> Hi,
>>
>> You have not mentioned the version of Spark used here.
>>
>> The outcome you mentioned was that the non-bucketed join outperformed the
>> bucketed join by approximately 25%, even though it processed more data and
>> required shuffling. Specifically, despite the reduced number of files read
>> and less data processed in the bucketed join (16K files vs. 35K, 245 GB vs.
>> 470 GB), the performance did not improve as expected. This was likely
>> because Spark still performed sorting during the join, despite the data
>> already being sorted within buckets. Additionally, you concluded that Spark
>> does not use sort metadata from Parquet files during execution, which led
>> to unnecessary sorting overhead.
>>
>> Recap:
>>
>>
>> - Bucketing can reduce shuffling, but it introduces sorting overhead
>> if Spark does not recognize that the data is already sorted.
>> - Spark does not leverage sort metadata from Parquet files, which can
>> lead to inefficient processing when using bucketed tables.
>> - The number of buckets should be carefully chosen to balance shuffle
>> reduction and memory/disk usage. In this case, reducing the number of
>> buckets to 16000 was not effective due to unnecessary sorting.
>>
>>
>> What you can do
>>
>> Setting
>>
>> - spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") //
>> turn on sort-merge join, to skip the sorting step
>>
>> Ensure that when you write the data, you use the sortBy or
>> sortWithinPartitions function to maintain the order within each bucket.
>> This will help Spark recognize that the data is already sorted when reading
>> it.
>>
>> df
>> .repartition(numBuckets, col("id")) // Repartition by the "id" column to
>> match the number of buckets
>> .sortWithinPartitions("id") // Sort within each partition (not after
>> repartition)
>> .write .bucketBy(numBuckets, "id") // Specify the number of buckets and
>> the column to bucket by
>> .format("parquet")
>> .saveAsTable("bucketed_table") // Save the data as a bucketed table in
>> Parquet format
>>
>> Use hints in queries
>>
>> - df1.join(df2.hint("merge"), col("id1") === col("id2"), "left").count
>>
>>
>> HTH
>>
>> Mich Talebzadeh,
>>
>> Architect | Data Engineer | Data Science | Financial Crime
>> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial
>> College London <https://en.wikipedia.org/wiki/Imperial_College_London>
>> London, United Kingdom
>>
>>
>> view my Linkedin profile
>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>
>>
>> https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>>
>> *Disclaimer:* The information provided is correct to the best of my
>> knowledge but of course cannot be guaranteed . It is essential to note
>> that, as with any advice, quote "one test result is worth one-thousand
>> expert opinions (Werner
>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von Braun
>> <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>>
>>
>> On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
>>
>>> Hello,
>>>
>>> We have a great scenario for using bucketing (at least as per
>>> definition) :
>>> Few data tables built hourly and partitioned by date + hour
>>> Several processes that join those tables by ID field many times
>>>
>>> One table is pretty big (~100G hourly), others are relatively small (but
>>> not small enough for broadcast), all joins are LEFT into the big table.
>>>
>>> To test bucketing I took one day of data (~4.5TB in a big table) and
>>> only two tables - one big and one small, left join between and count as an
>>> action.
>>>
>>> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
>>> - non bucketed tables ~ 3 min to perform count
>>> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count
>>> (huge mem + disk spill)
>>> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>>>
>>> this is the data from spark ui:
>>> *non bucketed:*
>>> [image: image.png]
>>> [image: image.png]
>>> So we can see it reads 31525 files and 470G data (out of 4.5T total file
>>> size), uses shuffle (1000 partitions) and finishes in 3.1 min
>>>
>>>
>>> *Bucketed (to 16K buckets):*SortMerge JOIN:
>>> [image: image.png]
>>> [image: image.png]
>>> Here we see it reads 16000 files and 245G of data (out of 4.5T total
>>> file size) NO shuffle, BUT SORT instead
>>>
>>> Using hint for SHUFFLE_HASH Join improves a bit but still bad
>>> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
>>>
>>> [image: image.png]
>>>
>>> [image: image.png]
>>>
>>>
>>>
>>> *The code to create data:*df1
>>> .withColumn("id1", ...)
>>> .repartition(16000, col("id1"))
>>> .write
>>> .sortBy("id1") // tried with sortWithinPartition instead - faster
>>> write, same read
>>> .bucketBy(16000, "id1")
>>> .partitionBy("dt", "hr")
>>> .format("parquet").mode("overwrite")
>>> .option("path", "...")
>>> .saveAsTable("table_1_16000")
>>>
>>> df2
>>> .withColumn("id2", ...)
>>> .repartition(16000, col("id2"))
>>> .write
>>> .sortBy("id2")
>>> .bucketBy(16000, "id2")
>>> .partitionBy("dt", "hr")
>>> .format("parquet").mode("overwrite")
>>> .option("path", "...")
>>> .saveAsTable("table_2_16000")
>>>
>>> To test join (in Zeppelin):
>>> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
>>> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
>>> t1.join(t2, col("id1") === col("id2"), "left).count
>>>
>>> So it looks like bucketing doesn't use parquet metadata to check if data
>>> is already sorted on read - seriously affecting performance.
>>>
>>> Just to mention - I tried to run it with 160 buckets (instead of 16000)
>>> and though each task reads only 1.4G of data => ~23G per instance (each
>>> instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge
>>> spill for both mem + disk - per my assumption due to heavy (likely
>>> unnecessary) sort:
>>> [image: image.png]
>>>
>>> Few questions:
>>> - is Spark supposed to use sort information from a parquet file?
>>> - if it is - should there be any difference in read after using *sort*
>>> or *sortWithinPartition*?
>>> - is there a way somehow to force it to use sort metadata or skip sort
>>> during execution?
>>>
>>> It is very odd that full shuffling performs much (25%) faster than
>>> bucketing.
>>> Especially when with bucketing it reads half amount of files (16K
>>> instead of 35K) and much less total input data (270G instead of 470G)
>>>
>>> Thanks!
>>>
>>> Daniel
>>>
>>
>
> --
> *Daniel Vol | Undertone*
> Data Engineer
> *M *+972-50-8861615
>
> www.undertone.com
>
>
> Undertone by Perion (NASDAQ: PERI)
>
>
> .
>
> This message may contain confidential and privileged information. It is
> intended to be read only by the individual or entity to whom it is
> addressed or by their designee.
> Suppose the reader of this message is not the intended recipient. In that
> case, you are on notice that any use, copy, disclosure, or distribution of
> this message
> is strictly prohibited. If you have received this message in error, please
> notify the sender by replying via email or telephone and delete or destroy
> any copy of this message.
> Please note that the sender of this message and their employer collect and
> process the personal data contained in this message and any
> of its correspondences as outlined in the applicable privacy policy
> available on its website.
>
--
*Daniel Vol | Undertone*
Data Engineer
*M *+972-50-8861615
www.undertone.com
Undertone by Perion (NASDAQ: PERI)
.
This message may contain confidential and privileged information. It is
intended to be read only by the individual or entity to whom it is
addressed or by their designee.
Suppose the reader of this message is not the intended recipient. In that
case, you are on notice that any use, copy, disclosure, or distribution of
this message
is strictly prohibited. If you have received this message in error, please
notify the sender by replying via email or telephone and delete or destroy
any copy of this message.
Please note that the sender of this message and their employer collect and
process the personal data contained in this message and any
of its correspondences as outlined in the applicable privacy policy
available on its website.
Re: Bucketing performance question
Posted by Daniel Vol <dv...@undertone.com>.
Hi Mich and Ofir,
First of all - thanks for an answer.
We use Spark 3.2 so I think spark.sql.join.preferSortMergeJoin is true by
default.
Setting it to true - gives nothing in terms of performance and I still see
the "sort" part in JOIN.
spark.sql.legacy.bucketedTableScan.outputOrdering - looks like it removes
part of the sorts in the big process but not yet tested it independently to
see the dedicated performance effect - will update once the test is done.
Daniel.
On Wed, Aug 21, 2024 at 7:56 PM Mich Talebzadeh <mi...@gmail.com>
wrote:
> Hi,
>
> You have not mentioned the version of Spark used here.
>
> The outcome you mentioned was that the non-bucketed join outperformed the
> bucketed join by approximately 25%, even though it processed more data and
> required shuffling. Specifically, despite the reduced number of files read
> and less data processed in the bucketed join (16K files vs. 35K, 245 GB vs.
> 470 GB), the performance did not improve as expected. This was likely
> because Spark still performed sorting during the join, despite the data
> already being sorted within buckets. Additionally, you concluded that Spark
> does not use sort metadata from Parquet files during execution, which led
> to unnecessary sorting overhead.
>
> Recap:
>
>
> - Bucketing can reduce shuffling, but it introduces sorting overhead
> if Spark does not recognize that the data is already sorted.
> - Spark does not leverage sort metadata from Parquet files, which can
> lead to inefficient processing when using bucketed tables.
> - The number of buckets should be carefully chosen to balance shuffle
> reduction and memory/disk usage. In this case, reducing the number of
> buckets to 16000 was not effective due to unnecessary sorting.
>
>
> What you can do
>
> Setting
>
> - spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") //
> turn on sort-merge join, to skip the sorting step
>
> Ensure that when you write the data, you use the sortBy or
> sortWithinPartitions function to maintain the order within each bucket.
> This will help Spark recognize that the data is already sorted when reading
> it.
>
> df
> .repartition(numBuckets, col("id")) // Repartition by the "id" column to
> match the number of buckets
> .sortWithinPartitions("id") // Sort within each partition (not after
> repartition)
> .write .bucketBy(numBuckets, "id") // Specify the number of buckets and
> the column to bucket by
> .format("parquet")
> .saveAsTable("bucketed_table") // Save the data as a bucketed table in
> Parquet format
>
> Use hints in queries
>
> - df1.join(df2.hint("merge"), col("id1") === col("id2"), "left").count
>
>
> HTH
>
> Mich Talebzadeh,
>
> Architect | Data Engineer | Data Science | Financial Crime
> PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial College
> London <https://en.wikipedia.org/wiki/Imperial_College_London>
> London, United Kingdom
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> *Disclaimer:* The information provided is correct to the best of my
> knowledge but of course cannot be guaranteed . It is essential to note
> that, as with any advice, quote "one test result is worth one-thousand
> expert opinions (Werner <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von
> Braun <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
>
>
> On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
>
>> Hello,
>>
>> We have a great scenario for using bucketing (at least as per definition)
>> :
>> Few data tables built hourly and partitioned by date + hour
>> Several processes that join those tables by ID field many times
>>
>> One table is pretty big (~100G hourly), others are relatively small (but
>> not small enough for broadcast), all joins are LEFT into the big table.
>>
>> To test bucketing I took one day of data (~4.5TB in a big table) and only
>> two tables - one big and one small, left join between and count as an
>> action.
>>
>> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
>> - non bucketed tables ~ 3 min to perform count
>> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count
>> (huge mem + disk spill)
>> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>>
>> this is the data from spark ui:
>> *non bucketed:*
>> [image: image.png]
>> [image: image.png]
>> So we can see it reads 31525 files and 470G data (out of 4.5T total file
>> size), uses shuffle (1000 partitions) and finishes in 3.1 min
>>
>>
>> *Bucketed (to 16K buckets):*SortMerge JOIN:
>> [image: image.png]
>> [image: image.png]
>> Here we see it reads 16000 files and 245G of data (out of 4.5T total file
>> size) NO shuffle, BUT SORT instead
>>
>> Using hint for SHUFFLE_HASH Join improves a bit but still bad
>> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
>>
>> [image: image.png]
>>
>> [image: image.png]
>>
>>
>>
>> *The code to create data:*df1
>> .withColumn("id1", ...)
>> .repartition(16000, col("id1"))
>> .write
>> .sortBy("id1") // tried with sortWithinPartition instead - faster
>> write, same read
>> .bucketBy(16000, "id1")
>> .partitionBy("dt", "hr")
>> .format("parquet").mode("overwrite")
>> .option("path", "...")
>> .saveAsTable("table_1_16000")
>>
>> df2
>> .withColumn("id2", ...)
>> .repartition(16000, col("id2"))
>> .write
>> .sortBy("id2")
>> .bucketBy(16000, "id2")
>> .partitionBy("dt", "hr")
>> .format("parquet").mode("overwrite")
>> .option("path", "...")
>> .saveAsTable("table_2_16000")
>>
>> To test join (in Zeppelin):
>> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
>> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
>> t1.join(t2, col("id1") === col("id2"), "left).count
>>
>> So it looks like bucketing doesn't use parquet metadata to check if data
>> is already sorted on read - seriously affecting performance.
>>
>> Just to mention - I tried to run it with 160 buckets (instead of 16000)
>> and though each task reads only 1.4G of data => ~23G per instance (each
>> instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge
>> spill for both mem + disk - per my assumption due to heavy (likely
>> unnecessary) sort:
>> [image: image.png]
>>
>> Few questions:
>> - is Spark supposed to use sort information from a parquet file?
>> - if it is - should there be any difference in read after using *sort*
>> or *sortWithinPartition*?
>> - is there a way somehow to force it to use sort metadata or skip sort
>> during execution?
>>
>> It is very odd that full shuffling performs much (25%) faster than
>> bucketing.
>> Especially when with bucketing it reads half amount of files (16K instead
>> of 35K) and much less total input data (270G instead of 470G)
>>
>> Thanks!
>>
>> Daniel
>>
>
--
*Daniel Vol | Undertone*
Data Engineer
*M *+972-50-8861615
www.undertone.com
Undertone by Perion (NASDAQ: PERI)
.
This message may contain confidential and privileged information. It is
intended to be read only by the individual or entity to whom it is
addressed or by their designee.
Suppose the reader of this message is not the intended recipient. In that
case, you are on notice that any use, copy, disclosure, or distribution of
this message
is strictly prohibited. If you have received this message in error, please
notify the sender by replying via email or telephone and delete or destroy
any copy of this message.
Please note that the sender of this message and their employer collect and
process the personal data contained in this message and any
of its correspondences as outlined in the applicable privacy policy
available on its website.
Re: Bucketing performance question
Posted by Mich Talebzadeh <mi...@gmail.com>.
Hi,
You have not mentioned the version of Spark used here.
The outcome you mentioned was that the non-bucketed join outperformed the
bucketed join by approximately 25%, even though it processed more data and
required shuffling. Specifically, despite the reduced number of files read
and less data processed in the bucketed join (16K files vs. 35K, 245 GB vs.
470 GB), the performance did not improve as expected. This was likely
because Spark still performed sorting during the join, despite the data
already being sorted within buckets. Additionally, you concluded that Spark
does not use sort metadata from Parquet files during execution, which led
to unnecessary sorting overhead.
Recap:
- Bucketing can reduce shuffling, but it introduces sorting overhead if
Spark does not recognize that the data is already sorted.
- Spark does not leverage sort metadata from Parquet files, which can lead
to inefficient processing when using bucketed tables.
- The number of buckets should be carefully chosen to balance shuffle
reduction and memory/disk usage. In this case, reducing the number of
buckets to 16000 was not effective due to unnecessary sorting.
What you can do
Setting
- spark.conf.set("spark.sql.join.preferSortMergeJoin", "true") // turn
on sort-merge join, to skip the sorting step
Ensure that when you write the data, you use the sortBy or
sortWithinPartitions function to maintain the order within each bucket.
This will help Spark recognize that the data is already sorted when reading
it.
df
.repartition(numBuckets, col("id")) // Repartition by the "id" column to
match the number of buckets
.sortWithinPartitions("id") // Sort within each partition (not after
repartition)
.write .bucketBy(numBuckets, "id") // Specify the number of buckets and
the column to bucket by
.format("parquet")
.saveAsTable("bucketed_table") // Save the data as a bucketed table in
Parquet format
Use hints in queries
- df1.join(df2.hint("merge"), col("id1") === col("id2"), "left").count
HTH
Mich Talebzadeh,
Architect | Data Engineer | Data Science | Financial Crime
PhD <https://en.wikipedia.org/wiki/Doctor_of_Philosophy> Imperial College
London <https://en.wikipedia.org/wiki/Imperial_College_London>
London, United Kingdom
view my Linkedin profile
<https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
https://en.everybodywiki.com/Mich_Talebzadeh
*Disclaimer:* The information provided is correct to the best of my
knowledge but of course cannot be guaranteed . It is essential to note
that, as with any advice, quote "one test result is worth one-thousand
expert opinions (Werner <https://en.wikipedia.org/wiki/Wernher_von_Braun>Von
Braun <https://en.wikipedia.org/wiki/Wernher_von_Braun>)".
On Wed, 21 Aug 2024 at 00:48, Daniel Vol <dv...@undertone.com> wrote:
> Hello,
>
> We have a great scenario for using bucketing (at least as per definition) :
> Few data tables built hourly and partitioned by date + hour
> Several processes that join those tables by ID field many times
>
> One table is pretty big (~100G hourly), others are relatively small (but
> not small enough for broadcast), all joins are LEFT into the big table.
>
> To test bucketing I took one day of data (~4.5TB in a big table) and only
> two tables - one big and one small, left join between and count as an
> action.
>
> As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
> - non bucketed tables ~ 3 min to perform count
> - bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count
> (huge mem + disk spill)
> - bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
>
> this is the data from spark ui:
> *non bucketed:*
> [image: image.png]
> [image: image.png]
> So we can see it reads 31525 files and 470G data (out of 4.5T total file
> size), uses shuffle (1000 partitions) and finishes in 3.1 min
>
>
> *Bucketed (to 16K buckets):*SortMerge JOIN:
> [image: image.png]
> [image: image.png]
> Here we see it reads 16000 files and 245G of data (out of 4.5T total file
> size) NO shuffle, BUT SORT instead
>
> Using hint for SHUFFLE_HASH Join improves a bit but still bad
> t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
>
> [image: image.png]
>
> [image: image.png]
>
>
>
> *The code to create data:*df1
> .withColumn("id1", ...)
> .repartition(16000, col("id1"))
> .write
> .sortBy("id1") // tried with sortWithinPartition instead - faster
> write, same read
> .bucketBy(16000, "id1")
> .partitionBy("dt", "hr")
> .format("parquet").mode("overwrite")
> .option("path", "...")
> .saveAsTable("table_1_16000")
>
> df2
> .withColumn("id2", ...)
> .repartition(16000, col("id2"))
> .write
> .sortBy("id2")
> .bucketBy(16000, "id2")
> .partitionBy("dt", "hr")
> .format("parquet").mode("overwrite")
> .option("path", "...")
> .saveAsTable("table_2_16000")
>
> To test join (in Zeppelin):
> val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
> val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
> t1.join(t2, col("id1") === col("id2"), "left).count
>
> So it looks like bucketing doesn't use parquet metadata to check if data
> is already sorted on read - seriously affecting performance.
>
> Just to mention - I tried to run it with 160 buckets (instead of 16000)
> and though each task reads only 1.4G of data => ~23G per instance (each
> instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge
> spill for both mem + disk - per my assumption due to heavy (likely
> unnecessary) sort:
> [image: image.png]
>
> Few questions:
> - is Spark supposed to use sort information from a parquet file?
> - if it is - should there be any difference in read after using *sort* or
> *sortWithinPartition*?
> - is there a way somehow to force it to use sort metadata or skip sort
> during execution?
>
> It is very odd that full shuffling performs much (25%) faster than
> bucketing.
> Especially when with bucketing it reads half amount of files (16K instead
> of 35K) and much less total input data (270G instead of 470G)
>
> Thanks!
>
> Daniel
>
Re: [External] Bucketing performance question
Posted by Ofir Manor <om...@speedata.io>.
Try using spark.sql.legacy.bucketedTableScan.outputOrdering = true , which is supposed to tell Spark to assume buckets are pre-sorted
shuffle - Spark Sort Operation in Join Despite Pre-sorted Bucketed Tables - Stack Overflow<https://stackoverflow.com/questions/78442540/spark-sort-operation-in-join-despite-pre-sorted-bucketed-tables>
Ofir
________________________________
From: Daniel Vol <dv...@undertone.com>
Sent: Tuesday, August 20, 2024 10:25 PM
To: user@spark.apache.org <us...@spark.apache.org>
Cc: aronovich.daniel@gmail.com <ar...@gmail.com>
Subject: [External] Bucketing performance question
Hello,
We have a great scenario for using bucketing (at least as per definition) :
Few data tables built hourly and partitioned by date + hour
Several processes that join those tables by ID field many times
One table is pretty big (~100G hourly), others are relatively small (but not small enough for broadcast), all joins are LEFT into the big table.
To test bucketing I took one day of data (~4.5TB in a big table) and only two tables - one big and one small, left join between and count as an action.
As a result on a cluster of 10 i4g.4xl (160 cores, 1T mem + ssd disks):
- non bucketed tables ~ 3 min to perform count
- bucketed to 160 + sort / sortWithinPartition ~ 5 min to perform count (huge mem + disk spill)
- bucketed to 16000 + sort ~ 4 min to perform count (no spill at all)
this is the data from spark ui:
non bucketed:
[image.png]
[image.png]
So we can see it reads 31525 files and 470G data (out of 4.5T total file size), uses shuffle (1000 partitions) and finishes in 3.1 min
Bucketed (to 16K buckets):
SortMerge JOIN:
[image.png]
[image.png]
Here we see it reads 16000 files and 245G of data (out of 4.5T total file size) NO shuffle, BUT SORT instead
Using hint for SHUFFLE_HASH Join improves a bit but still bad
t1.join(t2.hint("SHUFFLE_HASH"), col("id1") === col("id2"), "left).count
[image.png]
[image.png]
The code to create data:
df1
.withColumn("id1", ...)
.repartition(16000, col("id1"))
.write
.sortBy("id1") // tried with sortWithinPartition instead - faster write, same read
.bucketBy(16000, "id1")
.partitionBy("dt", "hr")
.format("parquet").mode("overwrite")
.option("path", "...")
.saveAsTable("table_1_16000")
df2
.withColumn("id2", ...)
.repartition(16000, col("id2"))
.write
.sortBy("id2")
.bucketBy(16000, "id2")
.partitionBy("dt", "hr")
.format("parquet").mode("overwrite")
.option("path", "...")
.saveAsTable("table_2_16000")
To test join (in Zeppelin):
val t1 = spark.sql("select * from table_1_16000 where dt = '...' ")
val t2 = spark.sql("select * from table_2_16000 where dt = '...' ")
t1.join(t2, col("id1") === col("id2"), "left).count
So it looks like bucketing doesn't use parquet metadata to check if data is already sorted on read - seriously affecting performance.
Just to mention - I tried to run it with 160 buckets (instead of 16000) and though each task reads only 1.4G of data => ~23G per instance (each instance has has 120G of memory + 3.5T dedicated ssd disk), I get a huge spill for both mem + disk - per my assumption due to heavy (likely unnecessary) sort:
[image.png]
Few questions:
- is Spark supposed to use sort information from a parquet file?
- if it is - should there be any difference in read after using sort or sortWithinPartition?
- is there a way somehow to force it to use sort metadata or skip sort during execution?
It is very odd that full shuffling performs much (25%) faster than bucketing.
Especially when with bucketing it reads half amount of files (16K instead of 35K) and much less total input data (270G instead of 470G)
Thanks!
Daniel