CPU Usage in terms of load average and utilization percentage on nodes

Help Workload Profiling
, ,
1

greetings,

i am looking for query to get the load average along with the CPU load Percentage per node for monitoring report that is going to run once on daily basis at the peak hours of business. what query does the dashboard uses to get the same?

Thanks and Regards,
M. Osama

2

You will be able to get the information by querying information_schema.mv_sysinfo_cpu table.

CPU utilization metrics are cumulative, hence you will have to take snapshots of the data at different time intervals and then measure the CPU utilization by calculating the lag between two time periods t. Below is an example query, assuming you have take snapshots of data into a temp table mv_sysinfo_cpu from information_schema.mv_sysinfo_cpu

with temp as (
            SELECT   *, 
                  lag(total_used_cumulative_ns) OVER w    total_used_cumulative_ns_l, 
                  lag(timestamp_ns) OVER w timestamp_ns_l
            from (
                  select ip_addr,
                        total_used_cumulative_ns,
                        timestamp_ns,
                        cfs_quota_ns,
                        cfs_period_ns,
                        time_sec /*log timestamp based on data pull*/
                  from mv_sysinfo_cpu) x 
            window w AS (partition BY ip_addr ORDER BY time_sec))
select n.ip_addr, 
       case when (s.site_name = '' or s.site_name is NULL or s.site_name = 'singlestore-central') then 'singlestore-default' else s.site_name end as workspace,
       t.time_sec,
       (((total_used_cumulative_ns - total_used_cumulative_ns_l)/(timestamp_ns - timestamp_ns_l)) / (cfs_quota_ns/cfs_period_ns)) as cpu_utilization
from information_schema.mv_nodes n  left join 
     information_schema.availability_groups a on n.availability_group = a.availability_group_id left join 
     information_schema.sites s on a.site_id = s.site_id left join
     temp as t on n.ip_addr = t.ip_addr
3

So alternative the the CPU code that works in our env. We don’t use cg service or cgroups enabled.
systemctl status ‘cg*’ – found nothing… anyway… this is what we came up with.

SET @CPUWindow =3;
WITH CPUutilization AS (
	SELECT 
		 t.ip_addr
		,t.time_sec
		,IF(t.ip_addr IS NULL,AVG(((t.total_used_cumulative_ns - t.total_used_cumulative_ns_l)/(t.timestamp_ns - t.timestamp_ns_l)) / t.num_cpus)
			,(((t.total_used_cumulative_ns - t.total_used_cumulative_ns_l)/(t.timestamp_ns - t.timestamp_ns_l)) / t.num_cpus)) cpu_utilization
	FROM (
		SELECT *
			,LAG(total_used_cumulative_ns) OVER w total_used_cumulative_ns_l
			,LAG(timestamp_ns) OVER w timestamp_ns_l
		FROM (
			SELECT ip_addr,total_used_cumulative_ns,timestamp_ns,num_cpus,cfs_period_ns,NOW(6) time_sec
			FROM INFORMATION_SCHEMA.mv_sysinfo_cpu
			UNION
			SELECT NULL,NULL,NULL,NULL,NULL,SLEEP(@CPUWindow) -- Sleep #
			UNION
			SELECT ip_addr,total_used_cumulative_ns,timestamp_ns,num_cpus,cfs_period_ns,ADDDATE(NOW(6),INTERVAL @CPUWindow SECOND) time_sec
			FROM INFORMATION_SCHEMA.mv_sysinfo_cpu
		) D
		WINDOW w AS (PARTITION BY ip_addr ORDER BY time_sec)
	) t
	WHERE t.total_used_cumulative_ns_l IS NOT NULL
	GROUP BY ROLLUP(t.ip_addr)
	ORDER BY t.ip_addr
)
SELECT
	 NVL(d.ip_addr,'Overall') ip_addr
	,d.time_sec
	,ROUND(d.cpu_utilization * 100,0) cpu_utilization
FROM CPUutilization d;

Hopefully it’s helpful to someone!