How to check vertica stauts (such as cpu, memory status) by using query?

HyeontaeJu

How to check vertica stauts (such as cpu, memory status) by using query?

mosheg

1. 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;

2. 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;

3. For CPU util see also: https://www.vertica.com/blog/vsql-visual-storytelling-through-query-language/

mosheg

4. 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;

5. 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;