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.
For others I have attached the SQL I use to extract the days CPU Utilisation.
I hope its useful to others.
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;
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
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.
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.
@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)
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.