https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128120#M48161 <P>We have a Databricks pipeline where the <STRONG>layer</STRONG>&nbsp;reads&nbsp;from several Silver tables to detect PK/FK changes and trigger updates to Gold tables. Normally, this near real-time job has ~3 minutes latency per micro-batch.</P><P>Recently, we noticed that each batch is running <STRONG>much slower</STRONG> (10–20+ minutes), even after scaling from N2-8 to N2-16 for both driver and executors — without noticeable improvement.</P><P>I extracted job metrics and sorted them by AvgCompletionTime. The top bottlenecks are:</P><UL><LI><P><STRONG>Deletion vector operations</STRONG> (number of deletion vector rows read, time spent reading deletion vectors, etc.) with ~6,100–6,300 seconds average completion time per task.</P></LI><LI><P><STRONG>Unified cache operations</STRONG> (unified cache populate time for ParquetFooter, unified cache populate time for RangeDeletionVector, unified cache read/serve bytes...) with ~5,000 seconds average completion time per task.</P></LI></UL><P>From the metrics, it seems that:</P><UL><LI><P>A lot of tasks are reading from disk cache (unified cache) but also spending significant time <STRONG>populating</STRONG> the cache.</P></LI><LI><P>Many Parquet footers, column chunks, and deletion vectors are being cached on the fly.</P></LI><LI><P>Cache hit ratio may be low due to constantly reading new data from CDF.</P></LI></UL><P>My questions are:</P><OL><LI><P>In a near real-time workload where data changes frequently, could <STRONG>unified cache</STRONG> cause more overhead than benefit (due to low hit rate)?</P></LI><LI>Are there recommended strategies to reduce deletion vector overhead in Delta tables (e.g., optimize, vacuum, rewrite)?</LI><LI><P>Any tuning tips for improving batch performance in this case?</P></LI></OL><P>Would appreciate any guidance or best practices from the community.</P><P>Metrics table:</P><P>&nbsp;</P><TABLE border="0" cellspacing="0"><COLGROUP width="382"></COLGROUP><COLGROUP width="121"></COLGROUP><COLGROUP width="135"></COLGROUP><COLGROUP width="145"></COLGROUP><TBODY><TR><TD height="17"><STRONG>MetricName</STRONG></TD><TD><STRONG>TotalMetricValue</STRONG></TD><TD><STRONG>TotalNumberTasks</STRONG></TD><TD><STRONG>AvgCompletionTime</STRONG></TD></TR><TR><TD height="17">number of non-local (rescheduled) scan tasks</TD><TD>10</TD><TD>10</TD><TD>9531.9</TD></TR><TR><TD height="17">unified cache hits count for RangeDeletionVector</TD><TD>162448</TD><TD>14137</TD><TD>6274.97798742138</TD></TR><TR><TD height="17">number of deletion vector rows read from disk cache</TD><TD>1710803</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vector rows read</TD><TD>1710803</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vectors read</TD><TD>162794</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vectors read from disk cache</TD><TD>162794</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">internal.metrics.resultSerializationTime</TD><TD>142</TD><TD>2792</TD><TD>5848.54285714286</TD></TR><TR><TD height="17">unified cache coalesce count for RangeDeletionVector</TD><TD>242</TD><TD>2554</TD><TD>5409.65979381443</TD></TR><TR><TD height="17">time spent reading deletion vectors</TD><TD>63656715484</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read from disk cache</TD><TD>8610813</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read</TD><TD>8610813</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">time spent waiting for fetched deletion vectors</TD><TD>0</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read from memory cache</TD><TD>0</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">unified cache coalesce count for ParquetFooter</TD><TD>67</TD><TD>1113</TD><TD>5191.16279069768</TD></TR><TR><TD height="17">unified cache hits count for ParquetFooter</TD><TD>682285</TD><TD>102155</TD><TD>5115.15193026152</TD></TR><TR><TD height="17">unified cache hits count for ParquetColumnChunk</TD><TD>2418275</TD><TD>102165</TD><TD>5109.88888888889</TD></TR><TR><TD height="17">scan time</TD><TD>4063119</TD><TD>106814</TD><TD>5071.2162818955</TD></TR><TR><TD height="17">number of input batches</TD><TD>1662757</TD><TD>106814</TD><TD>5071.2162818955</TD></TR><TR><TD height="17">unified cache populate time for ParquetFooter</TD><TD>485712457512</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">uncompressed bytes read after filtering</TD><TD>262858290309</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">cache hits size (uncompressed)</TD><TD>261150107387</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">cache hits size</TD><TD>198250435165</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">stable cache serve bytes for ParquetColumnChunk</TD><TD>187484742374</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache serve bytes for ParquetColumnChunk</TD><TD>187484742374</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache read bytes for ParquetColumnChunk</TD><TD>184745766945</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache populate time for RangeDeletionVector</TD><TD>21316692333</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache serve bytes for ParquetFooter</TD><TD>11843089123</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">stable cache serve bytes for ParquetFooter</TD><TD>11843089123</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache read bytes for ParquetFooter</TD><TD>11770827958</TD><TD>109744</TD><TD>5009.97760617761</TD></TR></TBODY></TABLE> Tue, 12 Aug 2025 04:21:16 GMT minhhung0507 2025-08-12T04:21:16Z https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128120#M48161 <P>We have a Databricks pipeline where the <STRONG>layer</STRONG>&nbsp;reads&nbsp;from several Silver tables to detect PK/FK changes and trigger updates to Gold tables. Normally, this near real-time job has ~3 minutes latency per micro-batch.</P><P>Recently, we noticed that each batch is running <STRONG>much slower</STRONG> (10–20+ minutes), even after scaling from N2-8 to N2-16 for both driver and executors — without noticeable improvement.</P><P>I extracted job metrics and sorted them by AvgCompletionTime. The top bottlenecks are:</P><UL><LI><P><STRONG>Deletion vector operations</STRONG> (number of deletion vector rows read, time spent reading deletion vectors, etc.) with ~6,100–6,300 seconds average completion time per task.</P></LI><LI><P><STRONG>Unified cache operations</STRONG> (unified cache populate time for ParquetFooter, unified cache populate time for RangeDeletionVector, unified cache read/serve bytes...) with ~5,000 seconds average completion time per task.</P></LI></UL><P>From the metrics, it seems that:</P><UL><LI><P>A lot of tasks are reading from disk cache (unified cache) but also spending significant time <STRONG>populating</STRONG> the cache.</P></LI><LI><P>Many Parquet footers, column chunks, and deletion vectors are being cached on the fly.</P></LI><LI><P>Cache hit ratio may be low due to constantly reading new data from CDF.</P></LI></UL><P>My questions are:</P><OL><LI><P>In a near real-time workload where data changes frequently, could <STRONG>unified cache</STRONG> cause more overhead than benefit (due to low hit rate)?</P></LI><LI>Are there recommended strategies to reduce deletion vector overhead in Delta tables (e.g., optimize, vacuum, rewrite)?</LI><LI><P>Any tuning tips for improving batch performance in this case?</P></LI></OL><P>Would appreciate any guidance or best practices from the community.</P><P>Metrics table:</P><P>&nbsp;</P><TABLE border="0" cellspacing="0"><COLGROUP width="382"></COLGROUP><COLGROUP width="121"></COLGROUP><COLGROUP width="135"></COLGROUP><COLGROUP width="145"></COLGROUP><TBODY><TR><TD height="17"><STRONG>MetricName</STRONG></TD><TD><STRONG>TotalMetricValue</STRONG></TD><TD><STRONG>TotalNumberTasks</STRONG></TD><TD><STRONG>AvgCompletionTime</STRONG></TD></TR><TR><TD height="17">number of non-local (rescheduled) scan tasks</TD><TD>10</TD><TD>10</TD><TD>9531.9</TD></TR><TR><TD height="17">unified cache hits count for RangeDeletionVector</TD><TD>162448</TD><TD>14137</TD><TD>6274.97798742138</TD></TR><TR><TD height="17">number of deletion vector rows read from disk cache</TD><TD>1710803</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vector rows read</TD><TD>1710803</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vectors read</TD><TD>162794</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">number of deletion vectors read from disk cache</TD><TD>162794</TD><TD>14154</TD><TD>6138.35435435436</TD></TR><TR><TD height="17">internal.metrics.resultSerializationTime</TD><TD>142</TD><TD>2792</TD><TD>5848.54285714286</TD></TR><TR><TD height="17">unified cache coalesce count for RangeDeletionVector</TD><TD>242</TD><TD>2554</TD><TD>5409.65979381443</TD></TR><TR><TD height="17">time spent reading deletion vectors</TD><TD>63656715484</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read from disk cache</TD><TD>8610813</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read</TD><TD>8610813</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">time spent waiting for fetched deletion vectors</TD><TD>0</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">size of deletion vectors read from memory cache</TD><TD>0</TD><TD>108783</TD><TD>5310.43413173653</TD></TR><TR><TD height="17">unified cache coalesce count for ParquetFooter</TD><TD>67</TD><TD>1113</TD><TD>5191.16279069768</TD></TR><TR><TD height="17">unified cache hits count for ParquetFooter</TD><TD>682285</TD><TD>102155</TD><TD>5115.15193026152</TD></TR><TR><TD height="17">unified cache hits count for ParquetColumnChunk</TD><TD>2418275</TD><TD>102165</TD><TD>5109.88888888889</TD></TR><TR><TD height="17">scan time</TD><TD>4063119</TD><TD>106814</TD><TD>5071.2162818955</TD></TR><TR><TD height="17">number of input batches</TD><TD>1662757</TD><TD>106814</TD><TD>5071.2162818955</TD></TR><TR><TD height="17">unified cache populate time for ParquetFooter</TD><TD>485712457512</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">uncompressed bytes read after filtering</TD><TD>262858290309</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">cache hits size (uncompressed)</TD><TD>261150107387</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">cache hits size</TD><TD>198250435165</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">stable cache serve bytes for ParquetColumnChunk</TD><TD>187484742374</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache serve bytes for ParquetColumnChunk</TD><TD>187484742374</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache read bytes for ParquetColumnChunk</TD><TD>184745766945</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache populate time for RangeDeletionVector</TD><TD>21316692333</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache serve bytes for ParquetFooter</TD><TD>11843089123</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">stable cache serve bytes for ParquetFooter</TD><TD>11843089123</TD><TD>109744</TD><TD>5009.97760617761</TD></TR><TR><TD height="17">unified cache read bytes for ParquetFooter</TD><TD>11770827958</TD><TD>109744</TD><TD>5009.97760617761</TD></TR></TBODY></TABLE> Tue, 12 Aug 2025 04:21:16 GMT https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128120#M48161 minhhung0507 2025-08-12T04:21:16Z https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128191#M48172 <P>Hello <a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/135091">@minhhung0507</a>&nbsp;trust all is well at your end. May I know how you captured these job metrics?</P> Tue, 12 Aug 2025 09:39:28 GMT https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128191#M48172 noorbasha534 2025-08-12T09:39:28Z https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128192#M48173 <P><a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/135091">@minhhung0507</a>&nbsp;as per documentation -</P><P>'The actual physical removal of deleted rows (the "hard delete") is deferred until the table is optimized with OPTIMIZE or when a VACUUM operation is run, cleaning up old files.'</P><P>So, based on this, try to optimize the table once the data load finishes &amp; verify metrics in the next run.</P> Tue, 12 Aug 2025 09:43:48 GMT https://community.databricks.com/t5/data-engineering/slow-batch-processing-in-databricks-job-due-to-high-deletion/m-p/128192#M48173 noorbasha534 2025-08-12T09:43:48Z