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

Answers

• Options

  mosheg Vertica Employee Administrator

  September 2020 edited September 2020

  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/

  1
• Options

  mosheg Vertica Employee Administrator

  September 2020

  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;

  1