Excessive use of the disk I/O

Answers

• mosheg Vertica Employee Administrator

  February 2022 edited February 2022

  1. Check when the high disk usage happens, and look for patterns, and is it read or write oriented or both

  with
  result1 as (select
      node_name,
      time_slice(start_time, 1,'hour') as hour_slice,
      round(avg(READ_KBYTES_PER_SEC)) as Read_KB_per_Sec,
      round(max(WRITTEN_KBYTES_PER_SEC)) as Written_KB_per_Sec
  from
      IO_USAGE
  where
        ( NOW() - start_time) < '2 day'
  group by 1,2
  order by 1),
  result2 as (select row_number() over(order by hour_slice) as row_id, node_name, hour_slice, Read_KB_per_Sec, Written_KB_per_Sec
              from result1 order by hour_slice,node_name),
  norm1 as (select round(max(Written_KB_per_Sec)/40)+1 as norm_factor1 from result2),
  norm2 as (select round(max(Written_KB_per_Sec)/40)+1 as norm_factor2 from result2),
  
  chart1 as (select row_id, repeat('*',(Written_KB_per_Sec / norm_factor1)::int ) || repeat(' ',40 - (Written_KB_per_Sec / norm_factor1)::int) as Written_KB_per_Sec_chart from result2, norm1),
  chart2 as (select result2.row_id, insert(Written_KB_per_Sec_chart, (Read_KB_per_Sec / norm_factor2)::int +1, 1, ']') as avg_and_Written_KB_per_Sec_chart
                 from result2, norm2, chart1
                 where result2.row_id = chart1.row_id)
  SELECT hour_slice, RIGHT(node_name,4) AS Node, Read_KB_per_Sec, Written_KB_per_Sec,avg_and_Written_KB_per_Sec_chart as '[Read_KB_per_Sec] and *Written_KB_per_Sec* IO Usage'
  FROM result2, chart2
  WHERE result2.row_id = chart2.row_id
  ORDER BY hour_slice, node_name;
  
  

  2. Check which process cause the high disk usage from Linux shell prompt: iotop -n 1 -b -o
     If iotop is not installed do: yum install iotop

  3. Does it continue after reboot?

  0