Thoughts on CPUBusy caclulations.

Database
Enthusiast

Thoughts on CPUBusy caclulations.

A few people in our organization are slightly disbelieving of the CPU utilisation charts I produce, they always focus on the CPU utilization charts, as if it is the only metric to be interested in.(But thats a different post)

We use Linux based TD nodes, and I use the DBC.ResUsagespma table to extract the CPU utilisations.

((CPUUExec+CPUUServ) / (CPUIoWait+CPUUExec+CPUUServ+CPUIdle) ) *100

Does this seem right? Its the sort of calculation that I would use in the Unix world, but I thought I would seek opinion.

25 REPLIES
Senior Apprentice

Re: Thoughts on CPUBusy caclulations.

Of course you're correct :-)

CPUIdle + CPUIoWait = cpu idle
CPUUServ + CPUUExec = cpu busy

Dieter
Enthusiast

Re: Thoughts on CPUBusy caclulations.

Thanks for the confirmation Dieter.

For others I have attached the SQL I use to extract the days CPU Utilisation.

I hope its useful to others.

Random.

select thedate,
cast(cast(cast(TheTime as format '99:99:99.99')
as char(11)) as time(6)) as time_of_day,
((sum(CPUUExec)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_USR,
((sum(CPUUServ)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_SYS,
((sum(CPUIoWait)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_WIO,
((sum(CPUIdle)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_IDLE
from dbc.resusagespma
where TheDate > (CURRENT_DATE - interval '1' DAY)
group by thedate,thetime
order by thedate,thetime;

Enthusiast

Re: Thoughts on CPUBusy caclulations.

thanks @Random_Thought that query help me a lot
Enthusiast

Re: Thoughts on CPUBusy caclulations.

Hi Random,
Could you please let us know what CPU_USR and CPU_SYS are?

Plus
-how can I identify which user is using most of the resources (CPU) and which user(s) is/are waiting for the resources for a given time range?
-Querylog table has totalCPUTime what is this value?
Thanks
Feroz
Enthusiast

Re: Thoughts on CPUBusy caclulations.

I am trying to understand the CPUUExec and CPUUServ
Enthusiast

Re: Thoughts on CPUBusy caclulations.

Shaik,
Hi, I'll post some more information in next couple of days. I've just got back into office after being away for a while.
In brief though, CPU_SYS is the same as CPUSERV, and is the percentage of time the underlying linux/Windows server was executing System type code, such as reading from disk managing the run queues, handling interupts.

CPU_USR is same as CPUEXEC, and is the amount of time spent executing user code.

add both to gether and you will see the total CPU utilisation on the Teradata system.

The IDLE time is divided into CPUidle, which is really idle, and IOWAIT which is where the system is effectively idle, but at least one process is waiting for IO.

If you read some Unix performance books, they may give you a better understanding.

I'll post a better explanation later if it helps.

random.
Enthusiast

Re: Thoughts on CPUBusy caclulations.

Note that these times are multiplied by the number of CPUs on each node (corresponding to their respective rows).
In a heterogeneous system where the NodeTypes of your nodes are different this calculation will not hold true.
Enthusiast

Re: Thoughts on CPUBusy caclulations.

@monisiqbal. Not sure about this. My calculation works out a percentage based on the total. it includes all the CPU seconds available. Thus if you had low power nodes in your environment, they would still be X% utilised even though they do less work than the higher spec ones.

BUSY= (exec+serv) divided by (exec+serv+idle+iowait)

Enthusiast

Re: Thoughts on CPUBusy caclulations.

But the high power nodes would be contributing the CPU % according to their NodeNormFactor (which would be higher).
Consider an example where we have 20% CPU util. for a node of 5550 and let's say 5550 is twice as fast as 5500 which contributes 20%. Then the total CPU contribution should not be 20% because only 5550 would be equaling to 40% w.r.t. 5500.