by Nailah Bissoon
Big SQL Senior Technical Architect

The needs of the business may dictate that certain queries or workloads should be prioritized higher than others. Prioritizing of workloads must be done at the CPU and memory level. In this blog, we will show how to map applications to workloads and how to assign CPU and memory resources to them such that one workload is prioritized higher than the other. Consult an overview of WLM for Big SQL for more information on the capabilities of workload management.

Recall that workloads are mapped to service classes and resources are controlled at the service class level. More details can be found in Big SQL Workload Management Terminologies. There is no direct way to use the ALTER SERVICE CLASS statement to control the percentage of memory that is used by each workload. However, an indirect way is to use the methodology described in Big SQL Workload Management Establishing Gatekeeper Concurrency Controls to ‘distribute’ the available shared sort memory between workloads. It is recommended to create gatekeeper concurrency controls before using CPU prioritization techniques because the most effective way to control work is to control when it can start. If CPU prioritization techniques are used on their own then the system could still have very high run queues (as reported by vmstat).

In Big SQL Workload Management – Stage 1, monitoring was done at the system and workload level. In this stage, long running and resource intensive queries were identified. In Big SQL Workload Management – Stage 2, gatekeeper concurrency controls were applied at the system level. In this stage, gatekeeper concurrency controls will be applied at the workload level to manage the memory resources across the workloads, and CPU limits will be applied to each workload.

Example Use Case – Prioritizing One Workload Ahead of Another

Consider, two workloads running on the system, one workload can be identified by the application name ‘db2jcc_application’ and the other can be identified by the application name ‘GreenTea’. The CREATE WORKLOAD statement can be used to create the two workloads based on their application names. Workloads can also be created based on userids, roles etc, for more information consult the CREATE WORKLOAD section of the DB2 knowledge center. According to the business, the ‘db2jcc_application’ workload should be prioritized slightly higher than the ‘GreenTea’ workload, so it is assigned 60% of the CPU and memory resources. The steps to setting up an advanced workload management configuration for this use case will be shown below.

1. Monitor Workloads

To determine which queries from each workload can execute simultaneously on the system, monitoring and analysis at the workload level needs to be done. Recall the methodology presented in Big SQL Workload Management – Stage 1 to analyze queries on the system, the same sort of analysis needs to be done but this time at a workload level. This is important because if each workload has resource intensive queries, then the number of the resource intensive queries that can execute simultaneously on the system from each workload should be limited. Since the applications are mapped to the workloads, some statistics of these applications can be collected and analyzed based on the application names via queries against Event Monitor Tables such as:


WITH V1 AS (SELECT CASE WHEN QUERY_COST_ESTIMATE < 150000 THEN 'TRIVIAL' WHEN QUERY_COST_ESTIMATE BETWEEN 150000 AND 1000000 THEN 'SIMPLE' WHEN QUERY_COST_ESTIMATE BETWEEN 1000000 AND 6000000 THEN 'MEDIUM' WHEN QUERY_COST_ESTIMATE > 6000000  THEN 'COMPLEX' END AS TARGET_WORKCLASS, QUERY_COST_ESTIMATE, APPL_NAME, SORT_SHRHEAP_TOP, timestampdiff(4,TIME_COMPLETED - TIME_STARTED) AS LIFETIME_DURATION FROM ACTIVITY_DB2ACTIVITIES), V2 AS (SELECT TARGET_WORKCLASS, QUERY_COST_ESTIMATE, APPL_NAME, SORT_SHRHEAP_TOP, LIFETIME_DURATION FROM V1) SELECT TARGET_WORKCLASS, COUNT(*) AS COUNT, SUM(LIFETIME_DURATION) AS TOT_LIFETIME_MINS, AVG(SORT_SHRHEAP_TOP)  AS AVG_SORT_MEM4K, MAX(SORT_SHRHEAP_TOP) AS MAX_SORT_MEM4K, MAX(LIFETIME_DURATION) AS MAX_LIFETIME_MINS, INT(SUM(LIFETIME_DURATION)/COUNT(*)) AS AVG_LIFETIME_MINS FROM V2 WHERE APPL_NAME LIKE 'db2jcc_application' GROUP BY TARGET_WORKCLASS,APPL_NAME ORDER BY TARGET_WORKCLASS;

TARGET_WORKCLASS COUNT                             TOT_LIFETIME_MINS AVG_SORT_MEM4K       MAX_SORT_MEM4K       MAX_LIFETIME_MINS AVG_LIFETIME_MINS
---------------- --------------------------------- ----------------- -------------------- -------------------- ----------------- -----------------
COMPLEX                                      1296.              9469              1008840              9723719                50                 7
MEDIUM                                       2688.             22666               204519              2443022               446                 8
SIMPLE                                        864.               336               113298               541049                 8                 0
TRIVIAL                                      2110.                 0                  102                11814                 0                 0

WITH V1 AS … FROM V2 WHERE APPL_NAME LIKE 'GreenTea' GROUP BY TARGET_WORKCLASS,APPL_NAME ORDER BY TARGET_WORKCLASS;

TARGET_WORKCLASS COUNT                             TOT_LIFETIME_MINS AVG_SORT_MEM4K       MAX_SORT_MEM4K       MAX_LIFETIME_MINS AVG_LIFETIME_MINS
---------------- --------------------------------- ----------------- -------------------- -------------------- ----------------- --------------- --
COMPLEX                                      4433.            194634               116783              1559199                54                 43
MEDIUM                                         13.               581                99800               175337                45                 44
SIMPLE                                         52.               797                82685               204496                45                 15
TRIVIAL                                      1638.                 0                  315               121272                 0                  0

The MAX_SORT_MEM4K values reported for each application and target work class are needed when determining the concurrency threshold values for each of the intended work classes.

2. Define Work Classes For Each Workload

The characteristics of one workload maybe different from another, as a result, the definition of a complex query for one workload maybe different from that of another. It maybe useful to have different work class definitions for each workload. For the ‘db2jcc’ application, examining the output of the query above, since there is a good representation of queries across the target work classes, four work classes will be created for the TRIVIAL, SIMPLE, MEDIUM and COMPLEX queries. For the ‘GreenTea’ workload, since there are only few queries in the MEDIUM and SIMPLE classes, it is not beneficial to create four work classes. One TRIVIAL and one COMPLEX class will suffice, which is very similar to the Default Big SQL Workload Management Configuration.

3. Apply Gatekeeper Concurrency Controls For Each Workload

In Big SQL Workload Management – Stage 2, gatekeeper concurrency controls were applied at the system level, all work entering the system was assigned to the same workload and only a certain number of SIMPLE, MEDIUM or COMPLEX queries were allowed to execute simultaneously on the system. The concurrency threshold values for each work class was determined by dividing the available shared sort memory for each work class by the maximum shared sort memory usage for each work class. When there are multiple workloads, the available shared sort memory needs to be divided among the workloads and then divided among the work classes within each workload. The available shared sort memory on the cluster can be determined by running this command after executing the workloads:


db2 get db cfg for bigsql | grep SHEAPTHRES_SHR
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(12914436)

Since, 60% of the shared sort memory resources will be assigned to the ‘db2jcc’ application and 40% to the ‘GreenTea’ application. This means that


Shared sort memory for ‘db2jcc’ application = 60% x 12914436 4K pages= 7748661 4K pages

It was decided that concurrency thresholds are not needed for the TRIVIAL class since these queries are very light weight. The shared sort memory for this workload needs to be divided among the remaining work classes. In the calculations below we assume that no prioritization is needed across the work classes. If it is decided that within the workload certain work classes should be given higher priority than the others, then instead of evenly distributing the shared sort memory across the work classes, the weights can be increased for work classes with higher priority. The following calculation can be used to determine the concurrency threshold values for each of the work classes, observe the consideration for the 60% (0.6) resource assignment here:


COMPLEX concurrency threshold= 12914436*0.6/3/9723719=1
MEDIUM concurrency threshold = 12914436*0.6/3/2443022=1
SIMPLE concurrency threshold = 12914436*0.6/3/541049=4

Note, that the concurrency threshold values for the COMPLEX and MEDIUM classes are the same so in this case we could eliminate one of the work classes for simplicity and adjust the timeron cost range for the remaining work classes. However, since the initial settings could be gradually increased to improve performance, the SIMPLE, MEDIUM and COMPLEX work classes are kept.
For the ‘GreenTea’ application, based on the number of queries in each work class, we decided that there would be 2 work classes, TRIVIAL and COMPLEX. Since we do not need to create any concurrency control for the TRIVIAL work class, the concurrency value for the COMPLEX work class needs to be determined using the following calculation, observe the consideration for the 40% (0.4) assignment here:


COMPLEX concurrency threshold= 12914436*0.4/3/1559199=1

4. Assign CPU Resources For Each Workload

Assignment of CPU resources to workloads can be applied at the service class level. CPU resources are distributed via CPU SHARES. The default number of CPU SHARES for each service class is 1000. If a workload needs to be prioritized higher than another, the number of CPU SHARES can be increased. For example, if there are two workloads, and one workload should be given 40% of the CPU resources and the other 60%, then 40000 CPU SHARES can be assigned to the first and 60000 CPU shares can be assigned to the second. For the two workloads above, the following statements can be used to alter the service classes for each workload:


ALTER SERVICE CLASS "GREENTEA" 
SOFT CPU SHARES 40000;
ALTER SERVICE CLASS "DB2JCC"
SOFT CPU SHARES 60000;

If one workload is not executing then the workload that is running could be given all the CPU resources on the system. This can be done at the service class level with the SOFT CPU SHARES keyword as shown above. If the workload is never allowed to use more than 40% of the CPU resources, then HARD CPU SHARES can be used or the CPU LIMIT 40 keyword can be applied at the service class level.

5. Update Database Manager WLM configurations

In order to enable the DB2 WLM Dispatcher and utilize the Dispatcher CPU shares, the following updates are needed to the database manager:


db2 attach to bigsql;
db2 update dbm cfg using wlm_dispatcher yes;
db2 update dbm cfg using wlm_disp_cpu_shares yes;
db2 detach;

WLM Advanced Configuration for this Use Case

For the use case described above, once the steps above are completed, the commands below can be used to create the workloads, service classes, sub classes, work action set, work class set and thresholds such that the ‘db2jcc’ application is given 60% of the resources and the ‘GreenTea’ application is given 40% of the resources.


SET WORKLOAD TO SYSDEFAULTADMWORKLOAD;

CREATE SERVICE CLASS "GREENTEA" 
SOFT CPU SHARES 40000
DISABLE;

CREATE SERVICE CLASS "DB2JCC"
SOFT CPU SHARES 60000
DISABLE;

CREATE SERVICE CLASS "COMPLEX_GREENTEA " UNDER "GREENTEA" 
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

CREATE SERVICE CLASS "TRIVIAL_GREENTEA " UNDER "GREENTEA" 
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

CREATE SERVICE CLASS "COMPLEX_DB2JCC" UNDER "DB2JCC"
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

CREATE SERVICE CLASS "MEDIUM_DB2JCC" UNDER "DB2JCC"
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

CREATE SERVICE CLASS "SIMPLE_DB2JCC" UNDER "DB2JCC"
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

CREATE SERVICE CLASS "TRIVIAL_DB2JCC" UNDER "DB2JCC"
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITHOUT DETAILS
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
DISABLE;

---------------------------------------------
--DDL Statements for Work Class Sets

-----------------------------------------------
CREATE WORK CLASS SET "GREENTEA_WCS"
(
  WORK CLASS "TRIVIAL_COST_GREENTEA"
        WORK TYPE ALL
        FOR TIMERONCOST FROM 0 TO 150000,

   WORK CLASS "COMPLEX_COST_GREENTEA" 
        WORK TYPE ALL
        FOR TIMERONCOST FROM 150000.0 TO UNBOUNDED 
);

CREATE WORK CLASS SET "DB2JCC_WCS"
(
  WORK CLASS "TRIVIAL_COST_DB2JCC"
        WORK TYPE ALL
        FOR TIMERONCOST FROM 0 TO 150000,

  WORK CLASS "SIMPLE_COST_DB2JCC" 
        WORK TYPE ALL
        FOR TIMERONCOST FROM 150000 TO 1000000.0,

  WORK CLASS "MEDIUM_COST_DB2JCC" 
        WORK TYPE ALL 
        FOR TIMERONCOST FROM 1000000.0 TO 6000000.0,

  WORK CLASS "COMPLEX_COST_DB2JCC" 
        WORK TYPE ALL
        FOR TIMERONCOST FROM 6000000.0 TO UNBOUNDED 
);


---------------------------------------------
-- DDL Statements for Work Action Sets

-----------------------------------------------

CREATE WORK ACTION SET "GREENTEA_WAS"
 FOR SERVICE CLASS "GREENTEA"
 USING WORK CLASS SET "GREENTEA_WCS"
(
-- Direct trivial cost to the Trivial subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_TRIVIAL_GREENTEA"
        ON WORK CLASS "TRIVIAL_COST_GREENTEA"
        MAP ACTIVITY WITHOUT NESTED TO "TRIVIAL_GREENTEA",
-- Direct complex cost to the Complex subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_COMPLEX_GREENTEA" 
        ON WORK CLASS "COMPLEX_COST_GREENTEA"
        MAP ACTIVITY WITHOUT NESTED TO "COMPLEX_GREENTEA" 
) 
DISABLE;

CREATE WORK ACTION SET "DB2JCC_WAS"
 FOR SERVICE CLASS "DB2JCC"
 USING WORK CLASS SET "DB2JCC_WCS"
(
-- Direct trivial cost to the Trivial subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_TRIVIAL_DB2JCC"
        ON WORK CLASS "TRIVIAL_COST_DB2JCC"
        MAP ACTIVITY WITHOUT NESTED TO "TRIVIAL_DB2JCC",
-- Direct simple cost to the Simple subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_SIMPLE_DB2JCC" 
        ON WORK CLASS "SIMPLE_COST_DB2JCC"
        MAP ACTIVITY WITHOUT NESTED TO "SIMPLE_DB2JCC", 
-- Direct medium cost to the Medium subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_MEDIUM_DB2JCC" 
        ON WORK CLASS "MEDIUM_COST_DB2JCC"
        MAP ACTIVITY WITHOUT NESTED TO "MEDIUM_DB2JCC",  
-- Direct complex cost to the Complex subclass and allow any nested SQL
-- statement to be re-evaluated
  WORK ACTION "MAP_COMPLEX_DB2JCC" 
        ON WORK CLASS "COMPLEX_COST_DB2JCC"
        MAP ACTIVITY WITHOUT NESTED TO "COMPLEX_DB2JCC" 
) 
DISABLE;

---------------------------------------------
-- DDL Statements for Thresholds

-----------------------------------------------
-- Create disabled activity concurrency threshold for complex subclass
CREATE THRESHOLD "COMPLEX_GREENTEA_CONCURRENCY"
        FOR SERVICE CLASS "COMPLEX_GREENTEA" UNDER "GREENTEA"
        ACTIVITIES ENFORCEMENT DATABASE 
                DISABLE 
        WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES UNBOUNDED
                CONTINUE;

-- Create disabled activity concurrency threshold for simple subclass
CREATE THRESHOLD "SIMPLE_DB2JCC_CONCURRENCY"
        FOR SERVICE CLASS "SIMPLE_DB2JCC" UNDER "DB2JCC"
        ACTIVITIES ENFORCEMENT DATABASE 
                DISABLE 
        WHEN CONCURRENTDBCOORDACTIVITIES > 4 AND QUEUEDACTIVITIES UNBOUNDED
                CONTINUE;

-- Create disabled activity concurrency threshold for medium subclass
CREATE THRESHOLD "MEDIUM_DB2JCC_CONCURRENCY"
        FOR SERVICE CLASS "MEDIUM_DB2JCC" UNDER "DB2JCC"
        ACTIVITIES ENFORCEMENT DATABASE 
                DISABLE 
        WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES UNBOUNDED
                CONTINUE;

-- Create disabled activity concurrency threshold for complex subclass
CREATE THRESHOLD "COMPLEX_DB2JCC_CONCURRENCY"
        FOR SERVICE CLASS "COMPLEX_DB2JCC" UNDER "DB2JCC"
        ACTIVITIES ENFORCEMENT DATABASE 
                DISABLE 
        WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES UNBOUNDED
                CONTINUE;

ALTER SERVICE CLASS "GREENTEA" ENABLE;
ALTER SERVICE CLASS "COMPLEX_GREENTEA" UNDER GREENTEA ENABLE;
ALTER SERVICE CLASS "DB2JCC" ENABLE;
ALTER SERVICE CLASS "COMPLEX_DB2JCC" UNDER "DB2JCC" ENABLE;
ALTER SERVICE CLASS "MEDIUM_DB2JCC" UNDER "DB2JCC" ENABLE;
ALTER SERVICE CLASS "SIMPLE_DB2JCC" UNDER "DB2JCC" ENABLE;
ALTER SERVICE CLASS "TRIVIAL_DB2JCC" UNDER "DB2JCC" ENABLE;

ALTER WORK ACTION SET "DB2JCC_WAS" ENABLE;
ALTER WORK ACTION SET "GREENTEA_WAS" ENABLE;

ALTER THRESHOLD "COMPLEX_GREENTEA_CONCURRENCY" ENABLE;
ALTER THRESHOLD "SIMPLE_DB2JCC_CONCURRENCY" ENABLE;
ALTER THRESHOLD "MEDIUM_DB2JCC_CONCURRENCY" ENABLE;
ALTER THRESHOLD "COMPLEX_DB2JCC_CONCURRENCY" ENABLE;

--------------------------------------
 --DDL Statements for Workload
--------------------------------------

CREATE WORKLOAD "GREENTEA_WORKLOAD"  APPLNAME('GreenTea*')
SERVICE CLASS "GREENTEA"
POSITION AT 1
COLLECT ACTIVITY DATA
ON ALL DATABASE PARTITIONS
WITH DETAILS
COLLECT AGGREGATE ACTIVITY DATA BASE
COLLECT ACTIVITY METRICS NONE;

CREATE WORKLOAD "DB2JCC_WORKLOAD" APPLNAME('db2jcc*')
SERVICE CLASS "DB2JCC"
POSITION AT 2
COLLECT ACTIVITY DATA
ON ALL DATABASE PARTITIONS
WITH DETAILS
COLLECT AGGREGATE ACTIVITY DATA BASE
COLLECT ACTIVITY METRICS NONE;

SET WORKLOAD TO AUTOMATIC ;  

The following diagram, displays the newly created service classes, work class sets, work action sets and thresholds for the db2jcc workload and the GreenTea workload:

Conclusion

This blog demonstrated how to setup an advanced workload management configuration that involved execution of two workloads on the system. One workload was given 40% of the CPU and memory resources and the second workload was given 60% of the resources. After setting up this advanced WLM configuration, continue to monitor the system especially if new work is being added to the workloads.

1 comment on"Big SQL Workload Management – Prioritizing workloads ahead of others – Stage 3"

  1. […] Important applications may need to be given more resources compared to others to meet their SLAs. Workload management can be used to group work into workloads according to user or application name. A percentage of CPU resources can be assigned to each workload. From a memory resource allocation perspective, the same technique used in Big SQL WLM – Stage 2 can be used to allow certain workloads or groups of queries to be given a greater memory resources than others. For a use case description of how to assign memory and CPU resources to two workloads, see Big SQL Workload Management – Stage 3 – Prioritizing workload ahead of others. […]

Join The Discussion

Your email address will not be published. Required fields are marked *