CPU usage and idle time metrics from system tables

[santhiya]

I need to get my compute metric, not from the UI...the system tables has not much informations, node_timeline has per minute record metric so it's difficult to calculate each compute CPU usage per day. Any way we can get the CPU usage,CPU idle time,Memory Usage for each clusters per day? I need a sql query for getting this details

[BigRoux]

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.