Excessive use of the disk I/O

PlacidoAntonioPlacidoAntonio Community Edition User


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



  • Options
    moshegmosheg Vertica Employee Administrator
    edited February 2022
    1. Check when the high disk usage happens, and look for patterns, and is it read or write oriented or both
    result1 as (select
        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
          ( 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;
    1. 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

    2. Does it continue after reboot?

Leave a Comment

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