Execution of queries, consume high CPU and MEMORY resources.

[ISAAC VELASCO]

hello community.

We have the following issue with the PM vertical database.
The following queries are executed remotely.
The problem is that when executing them, the database resources are affected in high CPU and MEMORY consumption.
EVEN the database enters a zombie status since it never returns to nominal values ​​of CPU usage and when observing the vertical process the CPU consumption is 95% total.
In the servers we have 12 CPUs, a total of 1200% of use and the use becomes constant between 1000% and 1100%.

I hope you can help me to know why this happens or to know if there is any way to execute the queries in a different or optimized way.
#### Device Huawei ####

with nom as
(
SELECT d.item_id
,i.item_display_description
FROM dauser.dim_item d
JOIN dauser.dim_item i
ON d.device_primary_ip = i.device_primary_ip
JOIN dauser.dim_item_type t
ON d.item_id = t.item_id
WHERE d.item_id = i.device_item_id
and t.unqualified_name = 'device'
and i.item_display_description LIKE '%CPU%'
),
devices as
( SELECT e.item_id id
,d.item_name nombre
,TO_TIMESTAMP (I.tstamp) fecha
,i.im_reachability avai
,e.item_display_description
FROM nom e
JOIN dauser.dim_item d
ON d.item_id = e.item_id
JOIN dauser.dim_item_type t
ON d.item_id = t.item_id
JOIN dauser.dim_item c
ON d.device_primary_ip = c.device_primary_ip
JOIN dauser.reach_rate i
ON d.item_id = i.item_id
WHERE t.unqualified_name = 'device'
AND i.tstamp > ${UNIXTIME_START}
AND i.tstamp <= ${UNIXTIME_END}
GROUP BY e.item_id
,d.item_name
,TO_TIMESTAMP (i.tstamp)
,i.im_reachability
,e.item_display_description
ORDER BY TO_TIMESTAMP (i.tstamp) desc
),
cpu AS (SELECT e.id id
,e.nombre nombre
,e.fecha fecha
,e.avai avai
,AVG(o.im_utilization) cpu1
,MAX(o.im_utilization) cpu2
,e.item_display_description
FROM devices e
JOIN dauser.dim_item d
ON e.id = d.item_id
JOIN dauser.dim_item_type t
ON d.item_id = t.item_id
JOIN dauser.dim_item c
ON d.device_primary_ip = c.device_primary_ip
JOIN dauser.nrm_cpustats_rate o
ON c.item_id = o.item_id
WHERE e.fecha = TO_TIMESTAMP (o.tstamp)
GROUP BY e.id
,e.nombre
,e.fecha
,e.avai
,e.item_display_description
ORDER BY 3
),
memoria AS (SELECT e.id id
,e.nombre nombre
,e.avai avai
,e.cpu1
,e.cpu2
,AVG(u.im_utilization) memo
,e.fecha fecha
,e.item_display_description
FROM cpu e
JOIN dauser.dim_item d
ON e.id = d.item_id
JOIN dauser.dim_item_type t
ON d.item_id = t.item_id
JOIN dauser.dim_item c
ON d.device_primary_ip = c.device_primary_ip
JOIN dauser.physical_memstats_rate u
ON c.item_id = u.item_id
WHERE d.item_name = e.nombre
AND t.unqualified_name = 'device'
AND e.fecha = TO_TIMESTAMP (u.tstamp)
GROUP BY e.id
,e.nombre
,e.fecha
,e.avai
,e.cpu1
,e.cpu2
,e.item_display_description
ORDER BY 6),
final AS (SELECT e.nombre nombre
,e.avai "Availability : Availability - Average"
,e.cpu1 "CPU - Utilization - Average"
,e.cpu2 "CPU - Utilization - Maximum"
,e.memo "Memory - Utilization - Average"
,MAX(p.im_temperaturecelsius) "Environmental Sensor - Temperature Alternate - Temperature in degrees Celsius - Average"
,e.fecha Fecha
,e.item_display_description "Nombre CPU"
FROM memoria e
JOIN dauser.dim_item d
ON e.id = d.item_id
JOIN dauser.dim_item_type t
ON d.item_id = t.item_id
JOIN dauser.dim_item c
ON d.device_primary_ip = c.device_primary_ip
JOIN dauser.nrm_temp_env_sensor_alternate_rate p
ON c.item_id = p.item_id
WHERE e.fecha = TO_TIMESTAMP (p.tstamp)
GROUP BY e.nombre
,e.fecha
,e.avai
,e.cpu1
,e.cpu2
,e.memo
,e.item_display_description
ORDER BY 7)
select *
from final
ORDER BY 8;

BEST REGARDS,
Isaac Velasco.

[Catalin Farcasanu]

This is why you should use OpenAPI to query the data and not direct querying of Vertica.