How show the CPU load of a vertica process

maks

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?

mosheg

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:

with
result1 as (select
    node_name,
    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
from
    cpu_usage
where
      ( 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;

maks

@mosheg thanks a lot!