Databricks notebook sometime takes too long to run query (even on empty table)

[Retko]

Hi,

sometime I notice that running a query takes too long - even simple queries - and next time when I run same query it runs much faster. I have cluster running (DBR 10.4 LTS • 5 workers) and it has constantly several workers.

An Example of query is simple select on table which I truncated before, so I know it is empty, and I do something like:

# 
df = spark.sql(
  f"""
select count(*) from table_name
  """
)
 
display(df)

First time it took 1.3 minutes and running it again took 0.6 sec.

It seems to happen quite often, as if waiting for something to start even though it should be started and running.

Do you have some explanation for this behavior and how I can help it?

Thank you!

[UmaMahesh1]

Hi @Retko Okter​ 

Two things might answer your question.

1. When you are calling an action for the first time, the table gets delta cached in memory and copies of files will be stored on local node's storage because of which you will be able to run queries much faster.
2. I might sound silly, but if you enabled autoscaling for the cluster, do check out in the event log of spark ui if the cluster is Upscaling or Downscaling. When cluster is in the process of acquiring/removing new nodes, your query obviously gets delayed.

Hope this helps.

Cheers..

[Harun]

I agree with the @Retko Okter

To support the second point, find the below explanation,​

Optimized autoscaling

• Firstly, scales up from min to max in 2 steps.
• Secondly, can scale down even if the cluster is not idle by looking at shuffle file state.
• Thirdly, scales down based on a percentage of current nodes.
• And, on job clusters, scales down if the cluster is underutilized over the last 40 seconds.
• Lastly, on all-purpose clusters, scales down if the cluster is underutilized over the last 150 seconds.

Standard autoscaling

• Firstly, starts with adding 8 nodes. Thereafter, scales up exponentially, but can take many steps to reach the max.
• Secondly, scales down only when the cluster is completely idle and it has been underutilized for the last 10 minutes.
• Lastly, scales down exponentially, starting with 1 node.

[-werners-]

are you sure you are the only person using the cluster?

[Geeta1]

Hey @Retko Okter​ , If its a all-purpose cluster and multiple users are using it, then the workload maybe high and results take time.

[j_afanador]

Probably the cluster is always in use and the query always falls into the processing query, or the cluster auto stops every time that you use it.