Louis_Frolio
Databricks Employee
Databricks Employee

‎04-23-2025 06:39 AM

To calculate CPU usage, CPU idle time, and memory usage per cluster per day, you can use the system.compute.node_timeline system table. However, since the data in this table is recorded at per-minute granularity, it’s necessary to aggregate the data to a daily level.
Below is a SQL query that you can use to calculate daily averages for CPU usage, CPU idle time, and memory utilization for each cluster:
WITH per_cluster_daily AS (
  SELECT
    cluster_id,
    DATE_TRUNC('DAY', start_time) AS day,
    AVG(cpu_user_percent + cpu_system_percent) AS avg_cpu_usage_percent, -- Average CPU usage as the sum of user and system CPU
    AVG(cpu_idle_percent) AS avg_cpu_idle_percent,                      -- Average CPU idle time percentage
    AVG(mem_used_percent) AS avg_memory_usage_percent                   -- Average memory usage percentage
  FROM
    system.compute.node_timeline
  WHERE
    start_time >= CURRENT_DATE - INTERVAL 30 DAYS -- Limit data to the last 30 days (optional)
  GROUP BY
    cluster_id,
    DATE_TRUNC('DAY', start_time)
)
SELECT
  cluster_id,
  day,
  avg_cpu_usage_percent,
  avg_cpu_idle_percent,
  avg_memory_usage_percent
FROM
  per_cluster_daily
ORDER BY
  cluster_id,
  day;
 
Note: - This query uses columns like cpu_user_percent, cpu_system_percent, cpu_idle_percent, and mem_used_percent directly from the system.compute.node_timeline table, as these metrics are captured at per-minute granularity.
0 Kudos