Monitoring CPU Usage
Teradata Database records both normalized and raw CPU measures. Normalized CPU time is derived by applying a CPU scaling factor to the node level raw CPU time.
The standard co-existence scaling factors for all node types are pre-defined in PDE startup parameter files in the Open PDE startup.txt file. The per-node values are added to the vconfig and tosgetpma() structures by PDE startup for use by other components. In this way, Teradata Database provides accurate performance statistics for mixed node systems, particularly with respect to CPU skewing and capacity planning, that is, usable capacity.
The formula is:
Node_Scaling_Factor * Node_CPU_Time
The scaling factor values are derived from:
Currently, the 5100 is the base scaling factor value (1.00).
For AMP level reporting in DBQL and AMPUsage, the formula is:
Node_Scaling_Factor * AMP_CPU_Time
ResUsage and DBC.AMPUsage View
The DBC.AMPUsage view displays CPU usage information differently than the way usage information is displayed in ResUsage data.
|
This facility… |
Provides… |
|
ResUsage |
metrics on the system, without making distinctions by individual user or account ID. |
|
DBC.AMPUsage view |
AMP usage by individual user or account ID. AMPUsage counts logical I/Os, not physical. Some CPU seconds used by the system cannot be accounted for in AMPUsage. Therefore, ResUsage CPU metrics will always be larger than AMPUsage metrics. Typically, AMPUsage captures about 70-90% of ResUsage CPU time. |
For more information on the DBC.AMPUsage view, see Data Dictionary.
Using the ResNode Macro Set
For capacity planning, generally only ResUsageSpma is required. This is the only table required to make use of the ResNode macro set.
Important information from ResNode includes:
Observing Trends
ResUsage data is most useful in seeing trends when there is a reasonably long history (more than a year) available for comparison. Use this data to answer questions, such as:
CPU Busy
The ResNode macro reports CPU busy by second averages. Use it for general system analysis.
The macro contain the Avg CPU Busy column, which is the average CPU utilization for all nodes. Avg CPU Busy % is a measure of how often multiple CPUs in a node were busy during a log period.
For ResNode columns, see Resource Usage Macros and Tables.
CPU Tasks in a DSS Environment
The following lists how CPU tasks are carried out on the node during DSS operations.
1 Prepare for read:
2 Qualify rows. Determine if the row satisfies the WHERE clause condition(s).
Most DSS operations require full table scans in which the WHERE clause condition check is relatively time-consuming. Full table scans generally result from SQL statements whose WHERE clause does not provide a value for an index or partition elimination.
3 Process rows:
4 Format qualifying rows for spool output.
CPU Tasks During Transaction and Batch Maintenance Processing
The following describes how the CPU tasks are carried out on the node during a transaction batch maintenance processing.
Notice that the qualify rows activity is missing from the table. In transaction processing, it is more common for the WHERE clause to provide a value for the PI or USI. The read itself qualifies rows. Transaction processing typically avoids further conditional checks against non-indexed columns. All of these CPU tasks occur on the nodes.
1 Prepare for read:
2 Update row:
Parallel Node Efficiency
Parallel note efficiency is a measure of how evenly the workload is shared among the nodes. The more evenly the nodes are utilized, the higher the parallel efficiency.
Parallel node efficiency is calculated by dividing average node utilization by maximum node utilization. Parallel node efficiency does not consider the heaviness of the workload. It only looks at how evenly the nodes share that workload.
The closer parallel node efficiency is to 100%, the better the nodes work together. When the percentage falls below 100%, one or a few nodes are working much harder than the others in the time period. If node parallel efficiency is below 60% for more than one or two 10-minute log periods, Teradata Database is not getting the best performance from the parallel architecture.
Poor Parallel Efficiency
Possible causes of poor node parallel efficiency include:
Poor parallel efficiency can also occur at the AMP level. Common causes of poor AMP parallel efficiency include:
CPU Use
The following macros provide information on CPU use.
|
Macro |
Table |
Purpose |
|
ResCPUByNode |
SPMA |
Report of how each individual node is utilizing CPUs |
|
ResCPUByPE |
SVPR |
Report of how each Parsing Engine (PE) utilizes the CPUs on irrespective node |
|
ResCPUByAMP |
SVPR |
Report of how each AMP utilizes the CPUs on the respective node |
For information on the above macros, see Resource Usage Macros and Tables.