I want receive allerts when memory or other resources usage is close to limit (e.g., 85% from total).
See Dashboard Cluster Usage panel contains TOTAL MEMORY USAGE.
There 3 values:
Used
Reserved
Total - just as I receive with INFORMATION_SCHEMA.LMV_SYSINFO_DISK
But both Used values from this view are not equal to value from a dashboard panel and there is not reserved value. Can I ask what is source of it ?
Thanks!
Are you talking about our cloud portal or Studio? Can you post a screen shot?
Let me check with the developers and see if we can get you the query behind that.
If you are highly motivated and what to see what SQL Studio is emitting for various things, you can look in mv_activites_extended and mv_queries, and use some “like” filters on query text.
@hanson, I not was able to catch Mem Used & Reserved formula - and CPU usage, too - to receive the same values as in Studio for cluster and for nodes.
About Mem at least HOST_USED_B from INFORMATION_SCHEMA.LMV_SYSINFO_MEM gives SUM of Used and Reserved without splitting . It’s OK for my needs.
So, now my goal is CPU usage formula.
Thank you again
Hello @ira ,
The query that Studio uses are the following:
Memory Usage:
SELECT
IFNULL(SUM(mv.VARIABLE_VALUE - mv2.VARIABLE_VALUE), 0) * 1024 * 1024 AS memoryUsageB
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 = 'LEAF';
Available Memory (Total - Used = Reserved):
SELECT
SUM(MAX_MEMORY_MB) * 1024 * 1024 AS availableMemoryB
FROM (
SELECT
MAX(MAX_MEMORY_MB) AS MAX_MEMORY_MB
FROM
INFORMATION_SCHEMA.MV_NODES
WHERE
TYPE='LEAF'
GROUP BY
IP_ADDR
);
And for total:
SELECT
SUM(totalMemoryB) AS totalMemoryB
FROM (
SELECT
MAX(
IF(
CGROUP_TOTAL_B > POW(2, 62) OR CGROUP_TOTAL_B = -1,
HOST_TOTAL_B,
CGROUP_TOTAL_B
)
) AS totalMemoryB
FROM
INFORMATION_SCHEMA.MV_SYSINFO_MEM
WHERE
TYPE='LEAF'
GROUP BY
IP_ADDR
);
For disk usage, we just use:
Usage:
WITH
mv_query AS (
SELECT
MAX(MOUNT_USED_B) AS diskUsageB
FROM
INFORMATION_SCHEMA.MV_SYSINFO_DISK
WHERE
TYPE = 'LEAF'
GROUP BY
IP_ADDR
)
SELECT
SUM(diskUsageB) AS diskUsageB
FROM
mv_query;
Total:
WITH
mv_query AS (
SELECT
MAX(MOUNT_TOTAL_B) AS diskTotalB
FROM
INFORMATION_SCHEMA.MV_SYSINFO_DISK
WHERE
TYPE = 'LEAF'
GROUP BY
IP_ADDR
)
SELECT
SUM(diskTotalB) AS diskTotalB
FROM
mv_query;