optimizeWrite takes too long

[svrdragon]

Hi ,

We have a spark job write data in delta table for last 90 date partition. We have enabled spark.databricks.delta.autoCompact.enabled and delta.autoOptimize.optimizeWrite. Job takes 50 mins to complete. In that logic takes 12 mins and optimizewrite takes 37 mins to complete. Is any way to reduce total job time as the output per partition is 64mb file

We are using DBT 12 . 

[Kaniz · Accepted solution]

Hi @svrdragon, It’s great you’re using Delta Lake features to optimize your Spark job. 

Let’s explore some strategies to reduce the total job time potentially:

Optimize Write:

• You’ve already enabled delta.autoOptimize.optimizeWrite, which is a good start. This feature dynamically optimizes partitions while generating files with a default size of 128 MB. However, you can adjust the target file size based on your workload requirements using configuration....
• Consider experimenting with different file sizes to find the sweet spot that balances write performance and read efficiency.

Partitioning:

• Ensure that your Delta Lake partitioned tables are subject to write patterns that generate suboptimal (less than 128 MB) or non-standardized file sizes. Repartitioning data frames before writing them to disk can help.
• If you’re dealing with small batch SQL commands (e.g., UPDATE, DELETE, MERGE, CREATE TABLE AS SELECT, INSERT INTO), consider using Delta Lake partitioned tables.

Streaming Ingestion:

• If your use case involves streaming data with an append pattern to Delta Lake partitioned tables, the extra write latency introduced by Optimize Write may be tolerable.
• Evaluate whether the benefits of reduced file count and optimized file sizes outweigh the additional processing cost during writes.

Avoid Optimize Write:

• If you have non-partitioned tables or well-defined optimization schedules, you might choose to avoid Optimize Write.
• For large tables with specific read patterns, consider whether the extra write latency is acceptable.

VACUUM:

• Remember that the VACUUM command (used for deleting old unreferenced files) will also operate faster with Optimize Write enabled.
• If you don’t need the additional benefits of Delta (transactions, concurrent writes, time travel his....

Remember to monitor the impact of these changes on both write performance and read efficiency. 

Adjustments may be necessary based on your specific workload characteristics.

Happy optimizing! 🚀