How show the CPU load of a vertica process

maksmaks Community Edition User

Hello, Could you please help me with the following issue? On my vertica cluster, the load on the CPU has increased and I want to understand what kind of load on the CPU is given by the vertica process itself, and not together with other processes in the system. I looked at the System Bottlenecks report through the management console, but, unfortunately, only the total CPU load of all processes is displayed there. Also looked with query below but there is also only total CPU load

select node_name,average_cpu_usage_percent,end_time from system_resource_usage; node_name | average_cpu_usage_percent | end_time

How can I see how much CPU vertica consumes without taking into account other processes?



  • moshegmosheg Vertica Employee Administrator

    Try to use the the “top” program for dynamic real-time view of the Vertica process via this command:

    top -p $(pidof vertica)

    Or use “pidstat” for report statistics on Linux specific tasks.
    It can be installed via root:
    On Ubuntu/ Debian with: apt install sysstat
    On RHEL/Centos with: yum install sysstat

    For example, display CPU average every 10 seconds only for Vertica process id (pid):

    pidstat 10 -u -p $(pidof vertica)

    Or with -t to display statistics for threads associated with the selected tasks.

    pidstat 10 -ut -p $(pidof vertica)

    Indeed, the Vertica CPU_USAGE in V_MONITOR Schema record the average CPU usage percent for all the processes.
    Use the following to see the cpu usage differences between the cluster nodes:

    result1 as (select
        time_slice(start_time, 1,'hour') as hour_slice,
        round(avg(average_cpu_usage_percent)) as avg_cpu,
        round(max(average_cpu_usage_percent)) as max_cpu
          ( NOW() - start_time) < '2 hour'
    group by 1,2
    order by 1),
    result2 as (select row_number() over(order by hour_slice) as row_id, node_name, hour_slice, avg_cpu, max_cpu
                from result1 order by hour_slice,node_name),
    norm1 as (select round(max(max_cpu)/40)+1 as norm_factor1 from result2),
    norm2 as (select round(max(max_cpu)/40)+1 as norm_factor2 from result2),
    chart1 as (select row_id, repeat('*',(max_cpu / norm_factor1)::int ) || repeat(' ',40 - (max_cpu / norm_factor1)::int) as max_cpu_chart from result2, norm1),
    chart2 as (select result2.row_id, insert(max_cpu_chart, (avg_cpu / norm_factor2)::int +1, 1, ']') as avg_and_max_cpu_chart
                   from result2, norm2, chart1
                   where result2.row_id = chart1.row_id)
    SELECT hour_slice, RIGHT(node_name,4) AS Node, avg_cpu, max_cpu,avg_and_max_cpu_chart as '[average] and *max* cpu usage'
    FROM result2, chart2
    WHERE result2.row_id = chart2.row_id
    ORDER BY hour_slice, node_name;
  • maksmaks Community Edition User

    @mosheg thanks a lot!

Leave a Comment

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