15.00 - Performance Periods - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

Performance Periods

Performance Periods associate Performance Groups with Allocation Groups. A Performance Period is a value pair that specifies a limit condition (“milestone”) and an Allocation Group. Performance Periods are specified when Performance Groups are defined. Each Performance Group can specify from one to eight Performance Periods. For more information on defining Performance Groups, see “schmon -p” on page 762.

Every request is assigned to a Performance Group based on the logon process and by TASM, if enabled. The Performance Group controls how resources are allocated to session tasks, by means of Performance Periods. Performance Periods determine which Allocation Groups govern tasks, based on time and resource criteria. A task assigned to a particular Performance Group can be switched among different Allocation Groups based on the criteria defined in the Performance Group's Performance Periods

Performance Groups typically have only a single Performance Period. This is the case for the default Performance Groups in the default Resource Partition. Sessions controlled by these Performance Groups will have their resource allocation determined by a single Allocation Group.

Optionally, Performance Groups can have more than one Performance Period. When this is the case, sessions controlled by these Performance Groups can have their Allocation Group dynamically changed as system conditions change. Consequently, tasks running in those sessions may be assigned different priorities as those tasks run.

Performance Period Components

The following table describes the components of a Performance Period.

 

Component...

Defines the...

Milestone Type

 

type of threshold used to define each Performance Period for a Performance Group. You can express types in the following units:

  • Time-of-day (T)
  • Session resource usage (S or R)
  • Query resource usage (Q)
  • For more information, see “Performance Period Types and Limits” on page 711.

    Milestone Limit

    value of the threshold used to change Performance Periods for a Performance Group. You can express this value in the following units:

  • A valid time-of-day, such as 0800 for 8:00 a.m.
  • A number of seconds of CPU usage.
  • For more information, see “Performance Period Types and Limits” on page 711.

    Allocation Group

     

    number of the Allocation Group used to control sessions during this Performance Period.

    For more information, see “Allocation Groups” on page 715.

    The following sections describe milestone limits and Allocation Groups.

    Performance Period Types and Limits

    A Performance Group definition specifies the type of Performance Periods the group will use. If a Performance Group has multiple Performance Periods, they must all be the same type. There are three types of Performance Periods, based on the types of milestones they monitor:

  • Query resource Performance Periods monitor the accumulated CPU time used by each query. When the threshold for the Performance Period is exceeded, the query is transferred to the next Performance Period and so placed under the control of a different Allocation Group. Typically, long-running queries are switched to progressively lower priority Allocation Groups. This prevents these queries from competing against the shorter-running queries in higher priority Allocation Groups.
  • Note: Teradata recommends that query resource Performance Periods not be used with online components of Teradata Applications, such as Demand Change Management (DCM) and Customer Relationship Management (CRM). This prevents tactical online queries, which require quick responses, from being demoted.

  • Session resource Performance Periods monitor the accumulated CPU time used by each session. When the threshold for the Performance Period is exceeded, the session is transferred to the next Performance Period, and the tasks belonging to that session are placed under the control of a different Allocation Group. Typically, the change is to a lower‑ priority Allocation Group. Consequently access to resources will be progressively reduced for longer-running sessions.
  • Time-of-day Performance Periods monitor the clock time, and optionally the day of the week. These types of Performance Periods are used to dynamically switch the Allocation Group of a session based on changes in business priority of different work at different times of day. For example, a session can be switched to a higher-priority Allocation Group after business hours or on weekends, when competition for database resources is not high.
  • All Performance Period monitoring occurs on a per-node basis.

    Note: Each Performance Group can specify only a single Performance Period type. All Performance Periods defined for a Performance Group must use the same milestone units.

    If a Performance Group uses only one Performance Period, the type is irrelevant because sessions assigned to that Performance Group will remain under the control of the single Allocation Group specified by the Performance Period.

    The following table shows the relationship between Performance Period types and milestone limit units.

     

    Performance Period Type

    Milestone Limit Units

    Query resource usage

    Seconds to hundredths of seconds, representing an amount of query CPU resource consumption per node. For example, .02.

    Session resource usage

    Seconds to hundredths of seconds, representing an amount of session CPU resource consumption per node. For example, 300.

    Time-of-day

    Minutes of military time, representing time periods during a 24-hour day. For example, 0800 is 8:00 A.M.

    Resource Usage Milestones

    When milestone limits are expressed in units of query or session resource usage, these limits apply to the total resource usage of all tasks working on behalf of a query or session on a node. If multiple Performance Periods are defined for a Performance Group, the specified resource usage milestone value is an upper limit. When a query or session under the control of a Performance Group exceeds the limit defined for the current Performance Period, the Performance Period with the next-higher limit and its associated Allocation Group take control.

    The following command defines a Performance Group with two Performance Periods. For more information on defining Performance Groups, see “schmon -p” on page 762.

    schmon -p 10 H1 1 Q 15 9 0 33

    When a query under that control of Performance Group10 begins executing, all of its tasks will be under the control of Allocation Group 9 until 15 seconds of CPU time are used by the query on the node. At that time, Allocation Group 33 assumes control of the query until the query completes.

    Note: The time to accumulate 10 seconds of CPU time might be different from 10 seconds of wall clock time.

    Time-of-Day Milestones

    When you express milestone limits in units of time-of-day, the respective Performance Period and its associated Allocation Group control every task assigned to the Performance Group, up until the time specified in that Performance Period.

    The actual time-of-day selected for the Performance Periods represents an upper limit of time. When this threshold is passed, the Performance Period with the next higher time-of-day and its associated Allocation Group take control. This Performance Period will change as the day proceeds through the 24-hour cycle. When the last-specified Performance Period expires, the first Performance Period and its Allocation Group take control of the tasks assigned to the Performance Group.

    Suppose you rely on a window of time at night to perform batch work. To keep your query users from interfering with night work without shutting them out completely, do the following:

    1 Between 8:00 a.m. and 5:00 p.m., give the query users an assigned weight of 20.

    2 Between 5:00 p.m. and 11:00 p.m., reduce the assigned weight to 10.

    3 Between 11:00 p.m. and 8:00 a.m., further reduce the assigned weight to 5.

    At the same time, you can increase the weighting of any batch work. Suppose you raise the assigned weight of the batch work Performance Group from 5 to 30 between 11:00 p.m. and 8:00 a.m. In doing so, you have helped the important batch work to finish, even if users issue queries after hours.

    Day-of-Week

    Optionally, you can use a day-of-week specification with a time-of-day milestone for a Performance Period to indicate days when the Performance Period is to be active. This specification might indicate one or more individual days, or a range of days, but not both. Days are numbered sequentially from 0 to 6, Sunday through Saturday. A range of days is indicated by two day numbers separated by a hyphen.

    If a day-of-week specification is present for one Performance Period, then day-of-week must be present for all.

    A time-of-day milestone for a Performance Period defines the end of a time period during which the associated Allocation Group is to control tasks. The beginning of each period is the end of the preceding period. This might be on a previous day if day of week has been specified.

    If you do not use a day-of-week specification, then the specified periods apply to all days. In this case, the beginning of the first period is the end of the last period of the day. You might specify one period to define the Allocation Group to be used for the entire day. The milestone time given for this period is immaterial but must be a valid time.

    Suppose you want to provide different levels of service on weekdays, Saturday, and Sunday.

    On weekdays, one Performance Period is in effect between 0800 and 1800, another beginning at 1800 and continuing until 0800 the next morning.

    On Saturday the Performance Period from 1800 on Friday continues until 1400 and then switches to another Performance Period that continues through Sunday and on until 0800 Monday morning. The Performance Period specification is as follows, where day of the week is denoted by enclosing / characters:

  • /1/ 0800 5 Monday, use AG 5 until 0800
  • /2-5/ 0900 7 Tuesday through Friday, use AG 7 until 0900
  • /1-5/ 1800 2 Monday through Friday, use AG 2 until 1800. After 1800, use the next higher period.
  • /6/ 1400 7 Saturday, use AG 7 until 1400. After 1400, use the next Performance Period, which is the one specified for 0800 Monday that uses AG 5.
  • Note: If the Time Zone (TZ) is not set correctly, Priority Scheduler will not work as expected. To view the TZ see the files in the /user/lib64 directory and the man pages for zic and environ.

    If the Time Zone is changed, the current Priority Scheduler settings should be dumped (using the schmon -c command) and re-executed after PDE comes up so that the limit values in the system gdo are correct.

    Using cron With Priority Scheduler

    Using cron or a similar scheduler program to issue schmon commands at specific times adds flexibility in controlling how work is prioritized by Teradata Database.

    Time-of-day milestones allow you to change priorities at specific times of day and on specific days of the week. However, if a Performance Group uses time-of-day milestones, CPU resource utilization (query milestones) cannot also be considered when adjusting the priority of work controlled by that Performance Group. Using a scheduling program to run schmon at specific times allows both time and CPU resources to be considered when work is prioritized.

    One strategy is to define all Performance Groups to use only CPU resource usage (session or query) milestone types, and use cron jobs to additionally adjust priorities based on times of day, irrespective of CPU resources.

    An additional benefit of using cron with Priority Scheduler is that this technique allows you to change any of the Priority Scheduler settings on a regular basis. For example, you might run schmon -b regularly to adjust the relative weights or CPU limits of different Resource Partitions to match the typical usage patterns of your database. Whereas Performance Groups determine the prioritization of individual jobs, changes to a Resource Partition affect all jobs assigned to its Performance Groups. In some situations, this type of broad, time-based prioritization control is useful.

    For more information on schmon options, see “Schmon Utility (schmon) (SLES 10 only)” on page 725.