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

HyeontaeJuHyeontaeJu Vertica Customer

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

Answers

  • moshegmosheg Vertica Employee Administrator
    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/

  • moshegmosheg Vertica Employee Administrator
    1. 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;

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file