Find the query with high memory,cpu and I/O in last 6 hours

TarunKumar

I need to find which query is fired in vertica database in last 6 hours with maximum RAM,CPU and i/o of the vertica node. Intent if to find such query and optimized the same

LenoyJ

If you're on >9.1, there a system table called Query Consumption that has CPU Cycles, I/O and some network details. See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_CONSUMPTION.htm

For memory, you can probably check the data in Query Requests, Resource Acquisitions and/or Query Profiles system tables:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_REQUESTS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PROFILES.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_ACQUISITIONS.htm

You may want to increase retention periods for these in your data collector policy to store for 6 hours worth of data:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/ConfiguringDataRetentionPolicies.htm

Also, Vertica's Management Console has most of what you're looking for in a HTML interface.

mosheg

In addition try the following:
Top 10 queries with high CPU Cycles in last 6 hours:
SELECT transaction_id,statement_id, cpu_cycles_us, duration_ms
FROM QUERY_CONSUMPTION
WHERE request_type='QUERY' AND
success AND
( NOW() - end_time) < '6 hour'
ORDER BY 3 DESC,4
LIMIT 10;

Top 10 queries with high acquired memory in last 6 hours:
select
qr.transaction_id, qr.statement_id,
ra.pool_name,
sum(qr.request_duration_ms) as request_duration_ms,
sum(qr.memory_acquired_mb) as memory_acquired_mb
from
v_monitor.query_requests qr
left outer join v_monitor.resource_acquisitions ra
on qr.statement_id = ra.statement_id and
qr.transaction_id = ra.transaction_id
where
qr.is_executing is false and
( NOW() - qr.start_timestamp) < '6 hour' and
upper(regexp_substr(qr.request, '\w+')::char(8)) in ('SELECT', 'WITH') and
qr.memory_acquired_mb > 0
group by 1,2,3
order by 5 DESC
limit 10;

Determine all spills in last 6 hours:
SELECT DISTINCT transaction_id, statement_id, request_id, event_type, event_description
FROM dc_execution_engine_events
WHERE (NOW() - time) < '6 hours'
AND event_type ILIKE '%SPILL%'
;