Monitoring service class compliance with resource entitlements
Monitor elements that report actual resource usage help you to determine the resource requirements of the workload running in a service class, and to tune resource entitlements. However, actual resource usage is not always a good indicator for compliance with respect to resource entitlement.
Queries are admitted for execution based on their estimated or observed peak memory usage. Otherwise, resource over-commitment might occur when usage peaks line up across concurrently executing workloads. However, memory usage tends to be non-uniform over the lifetime of the query. Consequently, the average actual sort memory utilization tends to understate the true memory demands of the queries that are executing concurrently. The actual average usage is frequently lower than the entitlement, and so, by definition, does not reflect peak usage.
The following monitor elements help you to understand the decisions made by the
adaptive workload manager with respect to admission of queries and compliance with resource entitlements:
- AGENT_LOAD_TRGT_DEMAND_AVG
- Average demand for threading resources by work running in the service class and controlled by the adaptive workload manager. The threading demand is expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores)
- AGENT_LOAD_TRGT_DEMAND_TOP
- Peak demand for threading resources by work running in the service class and controlled by the adaptive workload manager. The threading demand is expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores) (wlm_agent_load_trgt x number of physical cores)
- SORT_SHRHEAP_DEMAND_AVG
- Average demand for shared sort memory by work running in the service class and controlled by the adaptive workload manager. The shared sort memory demand is expressed as a percentage of the configured shared sort memory (sheapthres_shr)
- SORT_SHRHEAP_DEMAND_TOP
- Average demand for shared sort memory by work running in the service class and controlled by the adaptive workload manager. The shared sort memory demand is expressed as a percentage of the configured shared sort memory (sheapthres_shr)
- ADM_OVERFLOWS
- Reports the number of queries that the adaptive workload manager admitted for execution which were allowed to exceed the configured entitlement for a service class. Queries are allowed to overflow the resource entitlement if their resource requirements exceed the entitlement. In such a case the service class would be drained of other work first before the overflow is allowed.
The resource demand elements report the estimated peak usage over the monitored time interval. The main distinction between the demand monitor elements and the actual utilization monitor elements is that the resource demand for a query is constant during the lifetime of a query. For example, if a query has an estimated peak sort memory requirement of 50 pages, the shared sort demand for the query is 50 pages throughout its lifetime. However, the actual peak allocation of the 50 pages might not be a constant (e.g. maybe the peak memory usage only occurs at the start or end of query execution). The demand metrics provide a better understanding of the admission decisions made by the adaptive workload manager since admission is made based on demand to ensure enough resources are available if the peak usages of currently running queries overlap.
The demand metrics for memory are often higher than the actual utilization metrics, because peak memory usage might not overlap. However, there might also be cases where actual utilization exceeds demand. For example, in cases where the memory required for a query has been under-estimated.
All the previously described monitor elements are reported by various service class statistics
interfaces, including the following interfaces:
- Statistics Event monitor (superclassstats and scstats logical data groups)
- MON_GET_SERVICE_SUBCLASS_STATS table function
- MON_GET_SERVICE_SUPERCLASS_STATS table function
The previously described monitor elements report the historic peak and average resource demand
observed over time. The following monitor elements report the current resource demand. These monitor
elements are available in the MON_GET_SERVICE_CLASS and MON_GET_SERVICE_SUPERCLASS table functions:
- AGENT_LOAD_TRGT_DEMAND
- Current demand for threading resources by work concurrently running in the service class and controlled by the adaptive workload manager. The threading demand is expressed as a percentage of the total threading resources (wlm_agent_load_trgt x number of physical cores)
- SORT_SHRHEAP_DEMAND
- Current demand for shared sort memory by work concurrently running in the service class and controlled by the adaptive workload manager. The shared sort memory demand is expressed as a percentage of the configured shared sort memory (sheapthres_shr)
Examples
- Example 1: There are 2 service classes:
- S1 has 25% resource entitlements.
- S2 has 75% resource entitlements.
SELECT STATISTICS_TIMESTAMP, SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 20) as SUPERCLASS, DECIMAL(MAX(RESOURCE_ENTITLEMENT), 5, 2) ENTITLEMENT, DECIMAL(MAX(RESOURCE_ENTITLEMENT * (MINRESOURCESHAREPCT) / 100), 5, 2) AS MINIMUM_ENTITLEMENT, CASE WHEN MAX(AGENT_LOAD_TRGT_DEMAND_AVG) > MAX(SORT_SHRHEAP_DEMAND_AVG) THEN 'THREADS' ELSE 'SORT MEMORY' END AS CONSTRAINED_RES, DECIMAL(MAX( CASE WHEN AGENT_LOAD_TRGT_DEMAND_AVG > SORT_SHRHEAP_DEMAND_AVG THEN AGENT_LOAD_TRGT_DEMAND_AVG ELSE SORT_SHRHEAP_DEMAND_AVG END ), 5, 2) AS CONSTRAINED_RES_PCT, DECIMAL(MAX(AGENT_LOAD_TRGT_DEMAND_AVG), 5, 2) as AGENT_LOAD_DEMAND_AVG, DECIMAL(MAX(SORT_SHRHEAP_DEMAND_AVG), 5, 2) AS SORT_SHRHEAP_DEMAND_AVG, DECIMAL(MAX(ADM_QUEUED_ACT_LOAD), 5, 2) AS QUEUED_LOAD FROM SUPERCLASSSTATS_EVMONSTATISTICSU1 A, SYSCAT.SERVICECLASSES B WHERE A.SERVICE_CLASS_ID = B.SERVICECLASSID AND A.SERVICE_SUPERCLASS_NAME IN (‘S1',’S2') GROUP BY STATISTICS_TIMESTAMP, SERVICE_SUPERCLASS_NAME, MINRESOURCESHAREPCT ORDER BY STATISTICS_TIMESTAMP ASCThe output shows that over the monitored time interval, sort memory is the limiting resource with respect to concurrency. The resource demand values show that the entitlements are being respected (that is, that S2 is getting a higher percentage of the resources than S1). Note that the resource demand does not align exactly with the entitlement. This is expected, because the sort memory required by queries does not fit perfectly into the 25%/75% resource split. So, we see from this output that service class S2 is, on average, slightly over its 75% entitlement in the first interval with 81% demand, and slightly under in the second interval with 66% demand, as the adaptive workload manager adjusts admission to try to achieve the 25%/75% balance of resource usage over time.STATISTICS_TIMESTAMP SUPERCLASS ENTITLEMENT MINIMUM_ENTITLEMENT CONSTRAINED_RES CONSTRAINED_RES_PCT AGENT_LOAD_DEMAND_AVG SORT_SHRHEAP_DEMAND_AVG -------------------------- ---------- ----------- ------------------- --------------- ------------------- --------------------- ----------------------- 2019-05-01-14.51.31.681770 S1 24.99 0.00 SORT MEMORY 25.49 10.43 25.49 2019-05-01-14.51.31.681770 S2 74.99 0.00 SORT MEMORY 81.02 58.14 81.02 2019-05-01-14.52.06.424844 S1 24.99 0.00 SORT MEMORY 40.33 17.00 40.33 2019-05-01-14.52.06.424844 S2 74.99 0.00 SORT MEMORY 66.58 49.98 66.58 …The following query can be used to compute the overall average demand across all collection intervals (or across a subset of intervals, if a predicate is applied to the statistics timestamp):WITH MAX_RESOURCES(STAISTICS_TIMESTAMP, SUPERCLASS, RESOURCE_ENTITLEMENT, CONSTRAINED) AS (SELECT STATISTICS_TIMESTAMP, SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 20) as SUPERCLASS, RESOURCE_ENTITLEMENT, DECIMAL(MAX( CASE WHEN AGENT_LOAD_TRGT_DEMAND_AVG > SORT_SHRHEAP_DEMAND_AVG THEN AGENT_LOAD_TRGT_DEMAND_AVG ELSE SORT_SHRHEAP_DEMAND_AVG END ), 5, 2) FROM SUPERCLASSSTATS_EVMONSTATISTICSU1 where SERVICE_SUPERCLASS_NAME IN ('S1','S2') GROUP BY STATISTICS_TIMESTAMP, SERVICE_SUPERCLASS_NAME, RESOURCE_ENTITLEMENT) SELECT SUPERCLASS, DECIMAL(RESOURCE_ENTITLEMENT, 5,2) AS RESOURCE_ENTITLEMENT, DECIMAL(AVG(CONSTRAINED), 5, 2) AS AVG_RESOURCE_DEMAND FROM MAX_RESOURCES GROUP BY SUPERCLASS, RESOURCE_ENTITLEMENTThis output shows that over time, the adaptive workload manager was able to admit queries such that the average resource demand was very close to the entitlements.SUPERCLASS RESOURCE_ENTITLEMENT AVG_RESOURCE_DEMAND ---------- -------------------- ------------------- S1 24.99 26.50 S2 74.99 75.32 -
Example 2: You notice that a service class S1 is using fewer resources than its entitlement. You run the following query to examine the resource demand and load over the intervals of low resource utilization.
SELECT STATISTICS_TIMESTAMP, SUBSTR(SERVICE_SUPERCLASS_NAME, 1, 20) as SUPERCLASS, DECIMAL(MAX(RESOURCE_ENTITLEMENT), 5, 2) ENTITLEMENT, DECIMAL(MAX(RESOURCE_ENTITLEMENT * (MINRESOURCESHAREPCT) / 100), 5, 2) AS MINIMUM_ENTITLEMENT, CASE WHEN MAX(AGENT_LOAD_TRGT_DEMAND_AVG) > MAX(SORT_SHRHEAP_DEMAND_AVG) THEN 'THREADS' ELSE 'SORT MEMORY' END AS CONSTRAINED_RES, DECIMAL(MAX( CASE WHEN AGENT_LOAD_TRGT_DEMAND_AVG > SORT_SHRHEAP_DEMAND_AVG THEN AGENT_LOAD_TRGT_DEMAND_AVG ELSE SORT_SHRHEAP_DEMAND_AVG END ), 5, 2) AS CONSTRAINED_RES_PCT, DECIMAL(MAX(AGENT_LOAD_TRGT_DEMAND_AVG), 5, 2) as AGENT_LOAD_DEMAND_AVG, DECIMAL(MAX(SORT_SHRHEAP_DEMAND_AVG), 5, 2) AS SORT_SHRHEAP_DEMAND_AVG, DECIMAL(MAX(ADM_RUNNING_ACT_LOAD), 5, 2) AS RUNNING_LOAD, DECIMAL(MAX(ADM_QUEUED_ACT_LOAD), 5, 2) AS QUEUED_LOAD FROM SUPERCLASSSTATS_EVMONSTATISTICSU1 A, SYSCAT.SERVICECLASSES B WHERE A.SERVICE_CLASS_ID = B.SERVICECLASSID AND A.SERVICE_SUPERCLASS_NAME IN (‘S1') GROUP BY STATISTICS_TIMESTAMP, SERVICE_SUPERCLASS_NAME, MINRESOURCESHAREPCT ORDER BY STATISTICS_TIMESTAMP ASCThe second interval shows that the service class S1 was using only 5% of the resources even though it is entitled to 25%. Examining the queued load metric, you see that it is less than 1 (it is 0.38). This means that over the duration of the 1-hour time interval, an activity was queued less than half the time. The absence of queuing indicates that there was not enough work running in service class S1 to make use of the full 25% resource entitlement. Consequently, a utilization lower than the entitlement is expected.STATISTICS_TIMESTAMP SUPERCLASS ENTITLEMENT MINIMUM_ENTITLEMENT CONSTRAINED_RES CONSTRAINED_RES_PCT AGENT_LOAD_DEMAND_AVG SORT_SHRHEAP_DEMAND_AVG RUNNING_LOAD QUEUED_LOAD -------------------------- ---------- ----------- ------------------- --------------- ------------------- --------------------- ----------------------- ------------ ----------- 2019-05-01-14.51.31.681770 S1 24.99 0.00 THREADS 25.49 25.49 10.43 3.83 28.11 2019-05-01-15.52.06.424844 S1 24.99 0.00 THREADS 5.33 5.33 1.00 5.91 0.38 2019-05-01-16.52.42.009030 S1 24.99 0.00 THREADS 20.19 20.19 10.27 3.83 28.13Conversely, if the demand were significantly higher than the entitlement, examining the queued load for other service classes can help identify whether any service classes are not using their full entitlement. This excess capacity could then be allocated to other service classes that need it more.