Excessive use of the disk I/O

PlacidoAntonio

Hi,

I am currently using Vertica version 9.2.1 in which I have hosted different schemas and projects. My problem is that from the vertica console I am seeing that the database disk usage is very high even when there are no ETLs running against the database. How can I reduce this intensive disk usage?

Image: https://imgur.com/xKDYdH5

Thanks.

mosheg

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?