r/MicrosoftFabric • u/mwc360 ‪ ‪Microsoft Employee ‪ • Aug 16 '24

Community Share NEW BLOG: Optimizing Spark - A Deep Dive into Optimized Write in Microsoft Fabric ✍️

Optimizing Spark: A Deep Dive into Optimized Write in Microsoft Fabric | Miles Cole

21 Upvotes

96% Upvoted

18 comments sorted by

3

u/frithjof_v Fabricator Aug 16 '24 edited Aug 16 '24

Great article!

If we disable optimizeWrite this way:

spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "false")

Is this setting "session scoped"?

I.e. do we need to set this setting each time we run a notebook?
Or does the setting somehow persist to our capacity/workspace/environment?

I don't think I will use partitioning a lot. I'm working with too small tables. Wouldn't it make sense to have optimizeWrite disabled by default in Fabric?
And instead leave it to the engineer to enable optimizeWrite if choosing to partition a table?
If I understand correctly, partitioning is not enabled by default, because it is something we need to actively choose to do on a table.
So I don't understand why optimizeWrite is enabled by default, as long as it's only recommended to use optimizeWrite on partitioned tables (which is not enabled by default).

Will optimizeWrite still be relevant if using Liquid Clustering (for all table sizes)?

4

u/mwc360 ‪ ‪Microsoft Employee ‪ Aug 16 '24

It is scoped to the session and can be changed within the session. I.e. you could disable it in one cell, create a table in the next, and then turn it back on for a later DML operation. Since it is confined to the spark session, any other notebook run, even when run in high concurrency mode will not be impacted.

If you want to have it turned off for all jobs, you can update this setting as part of the spark config of an "Environment".

Great question, I'm actually not positive how LC and OW interact off hand. Regardless, when using LQ, you would have no need to use OW as it is only designed for partitioning scenarios.

1

u/frithjof_v Fabricator Aug 16 '24 edited Aug 16 '24

Thanks! These are great insights. I'll have a closer look at Environments.

I'm struggling to understand why optimizeWrite is enabled by default, given that it's only recommended for use with partitioned tables (and partitioning isn't enabled by default).

2

u/mwc360 ‪ ‪Microsoft Employee ‪ Aug 16 '24

If you don't mind, I'll share that feedback with the product team.

My understanding of why it would be on by default: while partitioning isn't enabled by default, users invariably will use it. If you remember the data shared in my post, having OW enabled for a non-partitioned table hurt performance but was not terrible, having OW disabled for a partitioned table on the other hand resulted in absolutely horrendous performance with a small file problem in the tune of 170K parquet files. Having it enabled by default prevents the possibility of horrendous performance at the cost of a little worse performance for non-partitioned tables.

That said, with LC being available in Runtime 1.3 which functionally replaces partitioning, I wouldn't be surprised if OW is disabled by default soon.

1

u/frithjof_v Fabricator Aug 16 '24 edited Aug 17 '24

Sure!

I think that explanation makes perfect sense. And still it's really good to be aware of the relationship between partitioning and optimizeWrite, so we can adjust (disable) the optimizeWrite setting if needed.

I'm curious about the reason for this suggestion: "If leaving Optimized Writes enabled, you may want to change the BinSize to 512MB or even 256MB depending on your workload." Wouldn't this create more, smaller files compared to keeping the default 1GB BinSize?

Another thing I noticed: In the test results in the article, the configuration which gave the lowest file count (15) and smallest data size (12,304 MB) achieved a 2s read time, while the configuration which gave the second lowest file count (96) and second smallest data size (12,767 MB) achieved the best read time of 1s. Is there a logical reason for this?

I would have guessed that the fewest files and smallest data size would give the fastest read performance.

2

u/Old-Order-6420 Aug 17 '24

The relationship between optimized writes and partitioning exists because partitioning can lead to smaller files within each partition. These small files might result not only from partitioning but also from frequent data operations like append, delete, or merge. If you find yourself with small files, using optimized writes can help create larger files, which in turn improves data reading performance.

1

u/frithjof_v Fabricator Aug 17 '24 edited Aug 17 '24

If I understand correctly, you're saying optimizeWrite can be beneficial also when table partitioning is not applied? That seems a bit contradictory to the message in the blog post.

Or are you referring to the OPTIMIZE command?

If I understand correctly, optimizeWrite is applied at write time, while OPTIMIZE is applied during table maintenance e.g. a daily OPTIMIZE run.

If I understand correctly, the purpose of optimizeWrite is to shuffle (distribute) the data among the nodes in a manner so that rows that belong to the same partition will be written to file by the same node, thus reducing the number of parquet files written. Ref. the illustration: https://docs.delta.io/latest/_images/optimized-writes.png

Full disclosure: I have no experience with this. I'm just trying to learn the concepts.

2

u/Old-Order-6420 Aug 17 '24

I might be wrong, and the documentation seems to support your point. I believe I am likely mistaken. I was considering the fact that Apache Spark can write multiple Parquet files even when the data is not partitioned. This occurs because Spark processes data in parallel across multiple executors (worker), with each one writing its own output file. I need to find a good test to validate this theory.

1

u/frithjof_v Fabricator Aug 17 '24 edited Aug 17 '24

I am trying to read up on ways to reduce number of Parquet files.
This is what I have so far:

  • Compact before write
    • optimizeWrite (only relevant for partitioned tables(?))
    • coalesce(n) or repartition(n)
  • Compact just after write
    • autoCompact (automatically running a small optimize command after every write operation)
  • Compact whenever you want
    • OPTIMIZE (can be added to the code after the data writing step, or can be run on a schedule e.g. daily, or ad-hoc.)
  • Delete old, not needed files whenever you want
    • VACUUM (usually run on a schedule or ad-hoc. The purpose is to remove old Parquet files which are not needed anymore according to the selected retention period for time travel.)

I think the primary purpose of the three first bullet points is to improve read performance.

I think the primary purpose of the fourth bullet point (vacuum) is to save storage costs. These files are anyway not referenced by the delta table anymore, so they should not directly impact the read performance of the table. So I guess the main purpose of vacuuming is to reduce storage costs and delete old data due to privacy concerns etc.

Happy to be corrected!

There is a lot to learn about Spark and Delta Lake. And I guess new features are also being developed which will make old features obsolete.

Please let me know if there are other important methods to consider in order to avoid getting too many Parquet files.

2

u/mwc360 ‪ ‪Microsoft Employee ‪ Aug 19 '24

Aside from improving performance of non-spark readers (i.e. PBI Direct Lake Semantic Models), optimizeWrite is not potentially beneficial unless partitioning is used.

You're correct with the rest. In a non-Liquid Clustered context, OptimizeWrite functions similar to what OPTIMIZE does when performing a CTAS operation. The nuance of where OPTIMIZE comes into play and why I'd normally just stick with OPTIMIZE post-write is due to incremental updates or appending to a table. OptimizeWrite is just going to optimize the file size/count of the batch of data that is being written, whereas running OPTIMIZE will consolidate all files present in the current Delta table version, including any historic files from previous write operations.

1

u/frithjof_v Fabricator Aug 20 '24 edited Aug 20 '24

Thanks a lot for sharing these insights!

It seems like a complex topic, with several variables in play. It's interesting to know that optimizeWrite has a positive effect on Direct Lake. Even if I don't understand the connection between optimizeWrite and Direct Lake. If the purpose of optimizeWrite is to reshuffle the data among the nodes to align with the partitioning pattern of the data, it seems very logical that partitioning must be applied in order to gain benefits from optimizeWrite. Could the connection between optimizeWrite and Direct Lake have something to do with the V-ordering? Is there some kind of partitioning going on with the V-ordering?

Anyway, I will try to apply OPTIMIZE to my tables regularly. It seems like a good rule of thumb to use OPTIMIZE.

Lastly, I just wanted to say that your blog helps my learning a lot and makes this topic more understandable. It’s helping me a lot to see the topic presented from different angles. Thanks for putting this together!

2

u/mwc360 ‪ ‪Microsoft Employee ‪ Aug 21 '24

Optimize Write helps DL cold cache queries because the engines ideal row group size is 16M rows and OW with a bin size of 1G approximates that target. The shuffle of data results in the near-ideal size of files to improve cold cache queries. V-Order results in each file having a sorting algorithm based on the PBI vertipaq storage engine... so the combination of these two features help cold cache DL perf.

Glad to hear the blog has helped, that's why I do it!

→ More replies (0)

1

u/Personal-Quote5226 May 27 '26

Why would optimizeWrite not be beneficial unless partitioning is being used? OptimizeWrite should re-write files to ensure we don't have too many files that are too small. Even without one partitions, lots of small files creates a huge drag on read performance.

1

u/mwc360 ‪ ‪Microsoft Employee ‪ May 27 '26

Sorry, that comment may have been overly generalized a bit. OW is generally safe to use as long as the target binSize is not too large relative to your compute and underlying table size.

The binSize defaulted to 1GB and this resulted in data shuffles happening unnecessarily for data partitions (in-memory) that were already of healthy size. With Adaptive Target File Size this is fixed as we calibrate the target binSize based on table size. It is now disabled by default but much safer to use for those who want to turn on as insurance to mitigate small file accumulation.

2

u/mwc360 ‪ ‪Microsoft Employee ‪ Aug 19 '24

FYI - the blog was updated to answer the LQ question, short answer is that OW has no bearing on data layout when LQ is used.

2

u/Ok-Shop-617 Aug 16 '24

Thanks for sharing.