The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How to check vertica stauts (such as cpu, memory status) by using query?

How to check vertica stauts (such as cpu, memory status) by using query?
0
Answers
Here is a Histogram of Average CPU Usage Percent by Minute query, used in Vadvisor report:
select node_name ,
sum(v80) CountOfAverageLessThan80,
sum(v95) CountOfAverageBetween80and95,
sum(v99) CountOfAverageBetween95and99,
sum(v100) CountOfAverageGreaterThan95,
listagg(vtime||' ' USING PARAMETERS max_length=2048,on_overflow='TRUNCATE') TimeWindowsWithAverageGreaterThan99 ,
max(action)
from (select node_name , case when average_cpu_usage_percent < 80 then 1 else 0 end v80,
case when average_cpu_usage_percent >=80 and average_cpu_usage_percent < 95 then 1 else 0 end v95,
case when average_cpu_usage_percent >=95 and average_cpu_usage_percent < 99 then 1 else 0 end v99,
case when average_cpu_usage_percent >=99 then 1 else 0 end v100 ,
case when average_cpu_usage_percent >=99 then end_time::varchar else null end vtime,
case when average_cpu_usage_percent >=99 then ' Found an instance of CPU utilization over 99%.' else ' ' end action from system_resource_usage) foo
group by 1;
Here is a query to create a Histogram of Average Resident Memory Usage Percent by minute, from the Vadvisor report:
select node_name ,
sum(v80) CountOfAverageLessThan80,
sum(v95) CountOfAverageBetween80and95,
sum(v100) CountOfAverageGreater95,
listagg(vtime||' ' USING PARAMETERS max_length=2048,on_overflow='TRUNCATE') TimeWindowsWithAverageGreaterThan99,
max(action) from (select node_name ,
case when average_memory_usage_percent < 80 then 1 else 0 end v80,
case when average_memory_usage_percent >=80 and average_memory_usage_percent < 95 then 1 else 0 end v100 ,
case when average_memory_usage_percent >=95 then 1 else 0 end v95 ,
case when average_memory_usage_percent >=95 then end_time::varchar(20) else null end vtime,
case when average_memory_usage_percent >=95 then ' Found an instance of resident memory saturation.' else ' ' end action from system_resource_usage) foo
group by 1;
For CPU util see also: https://www.vertica.com/blog/vsql-visual-storytelling-through-query-language/
If you want to check top 10 queries with high CPU Cycles use the following query (written in the past by Maurizio Felici):
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;
To see the top 10 queries with high acquired memory do:
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;