Archives of the TeradataForum

Message Posted: Fri, 24 Aug 2001 @ 17:50:24 GMT

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

Ruth,

John Hall's explanation of the data in ResUsageSPMA is very good. Here are a couple of things to consider, based on my experiences.

The Wait I/O recorded by UNIX (CPUSYSOH in ResUsageSPMA) can sometimes be mis-leading. UNIX always tries to keep the CPU busy doing User Exec work or User Services work. If a process makes a request for IO that requires it to wait, UNIX will start work on another process in the queue, if one is available. The result is that the wait time for the first process is not recorded. In other words, the the usage numbers are recorded from the CPU perspective, not from a process perspective.

This is important because monitoring Wait IO in and of itself can lead to possible false conclusions. Recently, at a customer with which I am working, we made some tuning changes and also upgraded to V2R4 from V2R3. When executing the same workload, we noticed that our Wait IO increased from about 18% to almost 30%. This was accompanied by an overall decrease in CPU busy time (CPUUExec + CPUUServ). We discovered that the increase was attributable to the fact that, since the workload was being performed more efficiently, the CPU ended uphaving more periods of time when all it could do was wait on IO.

Secondly, the proportion of CPUUExec to CPUUServ can sometimes be very telling. As a very general rule of thumb, we consistently noticed that when a highly CPU intensive query is running on the system, the CPUUExec time increases significantly compared to the CPUUServ time. In particular, a query that produces a cartisian product join will cause this to happen. A good visual way to see in real time is through the DUC charts within Teradata Manager. The node level CPU chart will show the vast majority of the work being done by CPUUExec. My experience has been that, when the workload is very efficient, the CPUUExec and CPUUServ are fairly balanced over time.

Hope you find this useful.

Regards,

Thomas F. Stanek
TFS Consulting

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