Let us say i have 8 node system with 2 HSN ie ( 6 Active nodes ) and i have couple of PE only nodes also. ie 6 ( Active Nodes) + 2 HSN's + 2 PE only nodes..
Case1: My total CPU across all the nodes would be calculated for teh 6 active nodes ? Should i exclude the nodeids for the 2 PE only nodes or should i be considering the parser CPU for the 2 PE nodes also?
Case 2: If i have PM COD as 73 then when i calculate the total CPU available is it going to reflect 100% or should i have subtract 27% from the overall and refer that as 100 %?
Case3: If my PM COD is 100% but my WL COD is set to 80% how should i assume the CPU calculation?
The base query that i will use is the below:
sel xx.year_of_calendar,week_of_month,xx.Logdate, case when xx.HOURR between 08 and 17 then 'Business Hours' else 'Non-Business-Hours' end "PATTErN",
case when month_of_year=1 then 'Jan'
when month_of_year=2 then 'Feb'
when month_of_year=3 then 'Mar'
when month_of_year=4 then 'Apr'
when month_of_year=5 then 'May'
when month_of_year=6 then 'Jun'
when month_of_year=7 then 'Jul'
when month_of_year=8 then 'Aug'
when month_of_year=9 then 'Sep'
when month_of_year=10 then 'Oct'
when month_of_year=11 then 'Nov'
when month_of_year=12 then 'Dec' End "Monthh",
select thedate as "LOGDATE"
,substr(cast(thetime as char(8)),1,2) as HOURR
--,case extract (cast(cast(cast(TheTime as format '99:99:99.99') as char(11))) as time(6)) as time_of_day
,sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle) as Total_CPU
,sum(CPUUExec + CPUUServ) as CPU_BUSY
,sum(CPUIoWait + CPUIdle ) as CPU_IDLE,
sum(CPUUExec + CPUUServ) / sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)* 100 as "CPU_BUSY_PERCT"
,sum(CPUIoWait + CPUIdle )/sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle) * 100 as "CPU_IDLE_PERCT"
from housekeeping_v.resusagespma a join sys_calendar.calendar b
on LOGDATE = b.calendar_date
where b.day_of_week not in (1,7) /* Weekends sat and Sun not considered */
and a.nodeid not in (9506,9508,110,210) /* These are PE Only Nodes and HSN nodes So excluded */
and Logdate between date -180 and date -1
group by 1,2,3,4,5,6
group by 1,2,3,4,5
order by 1,2;
Appreciate any inputs ..
1) Typically the CPU utilization on PE-only nodes is not a factor. I would probably ignore them (or consider them only in isolation). I would not exclude HSNs, though. Nodes only log to ResUsage while they are part of the active configuration.
2) In this case, you probably could consider ResUsage CPU% to represent percentage of the "allowed" capacity (73% of total platform capacity) though you may want to ask your Teradata account team / Teradata support to confirm this. There are multiple ways to impose COD limits. One could disable some CPU cores, which reduces the number of CPU seconds available. Or one can throttle the CPUs, in which case the number of CPU seconds doesn't change but each CPU second is capable of less work (so for example a system could appear 100% "busy", when it's doing 75% of the work that would be possible without COD). Those methods result in fairly coarse capacity "steps", so PM COD might be fine-tuned by also imposing workload limits.
3) With only workload limits in place, the "reserved" capacity looks like idle CPU. So when the system is using 100% of the "allowed" capacity it would appear only 80% busy (in other words, you may want to divide the reported CPU by the COD factor of 80% to obtain a value relative to current system allowed capacity).