Monitor CPU Usage | Teradata Vantage - Monitoring CPU Usage - Analytics Database - Teradata Vantage

Vantage 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, Vantage 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

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.

For more information on the DBC.AMPUsage view, see Teradata Vantage™ - Data Dictionary, B035-1092.

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.

Observing Trends

CPU Busy

The ResNode macro reports CPU busy by second averages. Use it for general system analysis.

For ResNode columns, see Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.

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:
   • Memory management allocates memory for the data block.
   • Database software communicates with the file system.
   • File system communicates with the disk controller.
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:
   • Join
   • Sort
   • Aggregate
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:
   • Memory management allocates memory for the data block.
   • Database communicates with the file system.
   • File system communicates with the disk controller.
2. Update row:
   • Database locates row to be updated.
   • Memory management allocates memory for the new data block to be built.
   • Database updates the changed row and copies the old rows.
   • Database communicates with the file system.
   • File system communicates with the disk controller.

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, Vantage is not getting the best performance from the parallel architecture.

Poor Parallel Efficiency

CPU Use

The following macros provide information on CPU use.

For information on these macros, see Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.