Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Excessive use of the disk I/O


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?




  • moshegmosheg 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.