Query to get memory and disk usage, need suggestions if it can be improved as my target is to get the real time values in terms of total and actual usage

Help Workload Profiling
,
1

select
memory_values.NODE_ID AS NODE_ID,
memory_values.node_type AS NODE_TYPE,
memory_values.host_name AS HOST_NAME,
memory_values.tot_memory AS TOT_MEMORY,
memory_values.used_memory AS USED_MEMORY,
storage_values.TOT_DISK_SPACE AS TOT_DISK_SPACE,
storage_values.USED_DISK_SPACE AS USED_DISK_SPACE
from
(SELECT
mv.node_id AS NODE_ID,
mv.node_type AS NODE_TYPE,
mv.IP_ADDR as HOST_NAME,
(mv.VARIABLE_VALUE)/1024 as TOT_MEMORY,
IFNULL(SUM(mv.VARIABLE_VALUE - mv2.VARIABLE_VALUE), 0) / 1024 AS USED_MEMORY
FROM
INFORMATION_SCHEMA.MV_GLOBAL_STATUS mv
INNER JOIN
INFORMATION_SCHEMA.MV_GLOBAL_STATUS mv2
ON
mv.NODE_ID = mv2.NODE_ID
WHERE
mv.VARIABLE_NAME = ‘Total_server_memory’ AND
mv2.VARIABLE_NAME = ‘Buffer_manager_cached_memory’ AND
mv.NODE_TYPE in (‘LEAF’,‘MA’)
group by mv.node_id,mv.node_type,mv.IP_ADDR,mv.VARIABLE_VALUE ) memory_values,
(
SELECT
NODE_ID AS NODE_ID,
type AS NODE_TYPE,
max(MOUNT_TOTAL_B)/1024/1024/1024 TOT_DISK_SPACE,
MAX(MOUNT_USED_B) /1024/1024/1024 AS USED_DISK_SPACE
FROM
INFORMATION_SCHEMA.MV_SYSINFO_DISK
WHERE
TYPE IN (‘LEAF’,‘MA’)
GROUP BY
NODE_ID,type
) storage_values
where memory_values.node_id=storage_values.NODE_ID
ORDER BY NODE_ID;

2

awaiting expert opinion

3

@muhammadosama.hassan -

Query to get memory usage

select n.ip_addr, 
       s.site_name as workspace,
       g.variable_value as  Total_server_memory,
       n.MAX_MEMORY_MB,
       m.cgroup_total_b/1024/1024 as cgroup_total_mb,
       (g.variable_value + ((m.cgroup_total_b/1024/1024) - MAX_MEMORY_MB))/(m.cgroup_total_b/1024/1024) as memory_utilized
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
     information_schema.MV_GLOBAL_STATUS g on n.ip_addr = g.ip_addr left join 
     information_schema.mv_sysinfo_mem m on n.ip_addr = m.ip_addr
where g.variable_name = 'Total_server_memory'

Query to get disk usage

select n.ip_addr, 
       s.site_name as workspace,
       d.mount_total_b,
       d.mount_used_b,
       d.mount_used_b/d.mount_total_b as disk_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
     information_schema.mv_sysinfo_disk d on n.ip_addr = d.ip_addr
1 Like