Archives of the TeradataForum

Message Posted: Tue, 27 Nov 2007 @ 21:12:10 GMT

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

Hmm, very interesting question. We use CPU from DBQL (as you can see by the view, we used to use ResUsage, now we use CPU from DBQL), calibrated by the average max busy to get an average "total".

     CREATE VIEW "DBA"."V_CPU_Calibrated" AS
     /*****
       * Name:    View for (DBQL) logged CPU time CALIBRATED by the average
     maximum (ResUsage) cpu busy percentage
       * Purpose: For use in the CPU forecasting processes.  Average maximum
     cpu busy calculation originally
       *          suggested by Jim LeBlanc of Teradata.
       * Author:  Frank C. Martinez IV
       *****/
     SELECT LogDate,
             SumCPU/AvgMaxBusy AS CalibratedCPU
        FROM (SELECT TheDate,
                     AVERAGE(MaxBusy)/100 AS AvgMaxBusy
                FROM (SELECT TheDate,
                             TheTime,
                             /* Max of the Total Busy Percent */
                             MAX(((CPUUServ +
     CPUUExec)/NULLIFZERO(NCPUs))/Secs) AS MaxBusy
                        FROM dbc.ResUsageSPma
                       GROUP BY 1, 2) AS NodeBusy
               GROUP BY 1) AS MaxBusy
        JOIN (SELECT LogDate,
                     SUM(TotalCPUTime) AS SumCPU
                FROM dba.DBQLogTbl_Hst
               GROUP BY 1) AS CPUSum
          ON TheDate = LogDate;

Then we forecast for each workload in TASM:

     CREATE VIEW "DBA"."V_CPU_by_Workload" AS
     /*****
       * Name:    View for CPU time BY (TASM) WORKLOAD
       * Purpose: For use in the CPU forecasting processes.  Converts the TASM
     workloads in use
       *          as of Aug 2007 into those of interest to the capacity planners.
       * Author:  Frank C. Martinez IV
       * Changed: 20 Aug 2007 - Original version
       *          16 Oct 2007 - Temporary change due to new WD id's.  Needs a
     permanant fix.
       *****/
     SELECT LogDate,
             SUM(CASE SUBSTRING(Description FROM 1 FOR INDEX(Description, '-') - 1)
                    WHEN 'Adhoc'
                       THEN TotalCPUTime
                  END)         AS Adhoc,
             SUM(CASE SUBSTRING(Description FROM 1 FOR INDEX(Description, '-') - 1)
                    WHEN 'Affinium'
                       THEN TotalCPUTime
                  END)         AS Affinium,
             SUM(CASE SUBSTRING(Description FROM 1 FOR INDEX(Description, '-') - 1)
                    WHEN 'Load'
                       THEN TotalCPUTime
                  END)         AS Load,
             SUM(CASE SUBSTRING(Description FROM 1 FOR INDEX(Description, '-') - 1)
                    WHEN 'Microstrategy'
                       THEN TotalCPUTime
                  END)         AS Microstrategy,
             SUM(CASE
                    WHEN Description IS NULL
                      OR Description LIKE 'Console%'
                      OR Description LIKE 'Default%'
                      OR Description LIKE 'Penalty%'
                       THEN TotalCPUTime
              END)             AS Other,
             SUM(TotalCPUTime) AS Total
        FROM dba.DBQLogTbl_Hst
        LEFT OUTER JOIN (SELECT WlcID,
                                Description
                           FROM tdwm.WlcDefs
                          WHERE configid IN (3, 4, 5)
                            AND removedate = 0) WDdesc
          ON WDID = WlcID
       GROUP BY 1;

We've considered doing this by the worse day of the week or something in order to just forecast peak usage, but then again, this works.

iv

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