Mixed Workload Design Priority Guidelines White Paper

Hewlett-Packard Company—528875-001
Contention for CPU Resources
Various techniques are used to ensure high priority workloads are not impacted by CPU intensive low
priority workloads such as SQL query activity. The message queue for DP2 services is priority ordered,
and the DP2 process will wait on the ready-list at the request priority until it reaches the head of the
ready-list. Once DP2 is dispatched, it will process the request at the normal DP2 priority (220).
If the current request is a lower priority SQL query, DP2 will periodically yield the CPU to processes with
a priority higher than the currently active request. The frequency is limited to avoid the extra overhead of
context switching.
Contention for DP2 Resources
In addition to the check for CPU contention, DP2 will also check the message queue to determine if there is
contention for data access services. If there is a new request for service with a higher priority than the
currently active request and it is not handled by another process in the group, then the current request will
be preempted.
Contention for I/O Resources
SQL query workloads can be highly optimized with predicate evaluation performed directly by the data
access manager. This is most notable with a query that consumes all data in the table. Such a query can be
throttled in the face of higher priority work by reducing the rate of read-ahead.
The amount of reduction in read-ahead is limited. If the read-ahead is reduced to zero (single block I/O),
the impact to the I/O system can be as bad as too much read-ahead in most cases.
Query Deferral
Simple contention checks are not sufficient to prevent impact to high priority transaction response time if
the amount of resources typically consumed by high priority workloads is below capacity. Any available
resources that allow some service to lower priority workloads can result in some transaction response time
delays due to the CPU and I/O context switching.
To overcome this problem, lower priority SQL query workloads may be periodically deferred by DP2. The
duration of deferral is directly related to the priority of the query. The lower the priority, the longer the
deferral period, and the higher the priority, the shorter the deferral period.
The deferral can be viewed as a time slice. SQL query workloads are typically processed by the data
access manager at one-second intervals, where DP2 processes work during that period, with a re-drive
mechanism between the SQL file system and DP2 every second. The interval may be smaller for
contention, buffer overruns, and other conditions, such as lock conflicts.
If the priority of the query is above 150, a DP2 process will spend the entire 1-second interval working on
the query before invoking the re-drive. If the priority is 150 or less, DP2 will delay work on the query for
some fraction of the 1-second interval. The delay period is inversely proportional to the request priority.
This means that the relative priority of the SQL query acts as a throttle in the face of higher priority
transaction workloads. The lower the priority of the SQL query, the less impact to transaction response
time, and the higher the priority of the SQL query, the more impact to transaction response time.
At the same time, the SQL query run in the face of higher priority transaction workloads will run faster and
consume more resources when run at higher priority, and will run slower and consume less resources when
run at lower priority.