cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Retko
Contributor

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!

5 REPLIES 5

UmaMahesh1
Honored Contributor III

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..

Uma Mahesh D

Harun
Honored Contributor

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-
Esteemed Contributor III

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

Geeta1
Valued Contributor

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
Contributor II

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group