Archives of the TeradataForum

Message Posted: Tue, 06 Jul 2004 @ 08:35:12 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Siva-

Here is a write-up on this topic.

-dave.clark

The column CPUTime from the view AMPUsage (dbc.acctg) returns the total CPU time in seconds (with resolution of .01 seconds) used on an AMP (or on all AMPs if SUM aggregate is specified). Here are some examples in which this information is of value:

o) when comparing the relative execution time of a session or query to another.

o) tracking the CPU usage associated with a group of users

o) identifying the relative workload for given time frame(s)

o) identifying candidates potentially responsible for a system saturation or resource bottleneck

o) identifying CPU skew

There are a couple of notable exceptions to the collection of information. The first is that it will only log CPU consumption for SQL requests which go through parser. This includes most of the Teradata utilities such as BTEQ, Teradata SQL Assistant and TPUMP but it does not include the Archive/Recovery (ARC) utility and it also will not include any activity outside of Teradata. The second exception is that if a users query is aborted, the usage recorded by the system will include all the CPU and I/O up to and including the last completed step. The CPU and I/O for the step that was in progress at the time of the abort will NOT be recorded.

When you are trying to assess the CPU capacity for a Teradata system, AMPUsage information is a useful component of this analysis but it must be coupled with ResUsage data and UNIX SAR data. The reason for this is that AMPUsage data does not factor in the CPU consumption associated with system overhead. For this, you will have to use ResUsage data. You can get the number of seconds that the CPU's are busy by adding CPUUSERV + CPUUEXEC from the table DBC.REsUsageSPMA. For example to obtain the % of time the CPUs were busy....

     SELECT
     TheDate, TheTime,
     SUM((CPUUServ + CPUUExec) / NULLIFZERO(VAPPs))/ SUM(Secs)
                                      (Format 'ZZ9%', Named AvgCpubusy, TITLE
     'Avg//CPU//bsy')
     FROM DBC.ResUsageSpma
     GROUP BY VPROC1, THEDATE, THETIME
     ORDER BY VPROC1,THEDATE, THETIME;

If you are able to identify one or more prolonged periods in which ResUsage shows CPU Busy is at 100% you should double check this activity level with the UNIX SAR run queue (sar -q). The "how busy is busy?" measure states that, when the ResUsage is registering 100% CPU usage, the UNIX run queue should be occupied (%runocc) 100% of the time across all nodes. The run queue depth (runq-sz) is platform and workload dependent but we should show a number of processes queued up (perhaps 20+) and the queue should be comparable across all like nodes.

Using this information, you can now derive a calculation to extrapolate the total consumption and total allocated consumption from AMPUsage. In other words

     ResUsage SUM(CPUUServ + CPUUExec) = 100% available CPU
     AMPUsage SUM(CPUTime) * (overhead CPU factor ) =  ResUsage SUM(CPUUServ + CPUUExec)

Other sites have determined that the AMPUsage values are somewhere between 75% and 85% of ResUsage, so the "overhead CPU factor" is 1.15 and 1.25.

Of course you will have to adjust this value to meet your needs. If you have a coexistent system with a mixture of nodes you will have to multiply the "overhead CPU factor" by an additional "relative performance factor". For example, the performance premium of an NCR5350 is 1.20 relative to the NCR5250. Check with GSS to identify the relative performance factor of other nodes.

	<Prev	Next>		<<First	<Prev	Next>	Last>>