by Nailah Bissoon
Big SQL Senior Technical Architect
One of the goals of workload management is to achieve an optimal environment that is neither under utilized nor over saturated. Consult an overview of WLM for Big SQL for more information on the capabilities of workload management. Over saturation of the system is usually caused by resource intensive queries that consume too many resources. Workload management (WLM) is enabled by default. The goal of this blog is to introduce important terminologies of WLM which will be used in subsequent blogs and to explain the default WLM configuration.
Default Big SQL Workload Management Configuration
All requests that enter the system are routed through a default workload. The purpose of the default workload is to channel incoming requests into the default user service class. Within the default user service class are two sub classes. One subclass is a managed subclass and one is an unmanaged subclass.
Queries that are more complex in nature (heavy weight queries) can be identified by large timeron costs. Timeron cost is the CPU and IO estimate of these resources from the Big SQL Optimizers’ perspective. Queries that measure 150000 timerons are treated as heavy weight queries and are mapped to this managed subclass. Only a certain number of queries can execute simultaneously in this managed sub-class. This is how Big SQL protects the system from oversaturation. The number of heavy weight queries that are allowed to execute simultaneously on the system will depend on the system resources. The diagram below shows a depiction of the default WLM configuration.
The default workload management configuration can be obtained by issuing the following command:
db2look -d bigsql -wlm -o wlmcfg.out The figure below is a graphical representation of the default configuration. It is recommended to keep referring to this figure when trying to understand each of the workload management concepts below.
Introduction to WLM Concepts
Workload
The primary point of identification, monitoring, and control of incoming work in the database is done at the workload level. Each unit of work is mapped to a workload definition before execution starts. The role of the workload is to direct incoming work to a specific service class. The default user workload is SYSDEFAULTUSERWORKLOAD. The SYSDEFAULTADMWORKLOAD can be used when altering the default workload. All incoming work is directed to the SYSDEFAULTUSERCLASS service class.
Service and Subclasses
There are two levels to the service class concept: there is a superclass and subclasses. All SQL statements execute in a service class, specifically a subclass. The hierarchy exists because there is often a need to share characteristics or apply identical rules across common subclasses. Resource allocation is done at the service class level. By default, all subclasses are treated the same in terms of resource allocation, i.e. no subclass has a higher priority than the other.
The default user service class is SYSDEFAULTUSERCLASS with SYSDEFAULTMANAGEDSUBCLASS and SYSDEFAULTSUBCLASS subclasses under it. The SYSDEFAULTMANAGEDSUBCLASS is the subclass where heavy weight queries are executed and the SYSDEFAULTSUBCLASS subclass are where light weight queries are executed.
Work Action Set and Work Class Set
A work action set can be applied to a database, workload or service class. All work that is funneled through either the database, workload or service class is first evaluated by the work action set. An important partner to the work action set is the work class set. The work class set is the dictionary which defines what things are of interest. A work class set is made up of different work classes which define the things of interest. A work action set uses the definitions in the work class set to find the work of interest and act upon it. The default work action set is SYSDEFAULTUSERWAS. It is applied to the SYSTEMDEFAULTUSERCLASS service class. This work action states to enable the SYSMANAGEDQUERIES work class.
ALTER WORK ACTION SET "SYSDEFAULTUSERWAS" ALTER WORK ACTION "SYSMAPMANAGEDQUERIES" ENABLE; ALTER WORK ACTION SET "SYSDEFAULTUSERWAS" ENABLE; The default work class set is SYSDEFAULTUSERWCS. This work class defines that queries that exceed a timeron cost of 150000 are of interest.
ALTER WORK CLASS SET "SYSDEFAULTUSERWCS" ALTER WORK CLASS "SYSMANAGEDQUERIES" FOR TIMERONCOST FROM 150000 TO UNBOUNDED ; Threshold
Thresholds are used to maintain stability in the system by identifying work that behaves abnormally. Rules can be defined and if these rules are broken the thresholds tell the system how to proceed. For example, the actions can include STOP EXECUTION or CONTINUE. By default, a CONCURRENTDBCOORDACTIVITIES threshold, SYSDEFAULTCONCURRENT, is applied to the SYSDEFAULTMANAGEDSUBCLASS subclass. The statement below is the snippet of the default threshold from the db2look -wlm output, note that the value 8 is automatically calculated on each cluster:
ALTER THRESHOLD "SYSDEFAULTCONCURRENT" WHEN CONCURRENTDBCOORDACTIVITIES > 8 AND QUEUEDACTIVITIES UNBOUNDED STOP EXECUTION; The threshold is on the QUEUEDACTIVITIES where the UNBOUNDED keyword is used. This means that activities will always be queued when there are greater than 8 concurrent queries running on the system. Think of this more as a CONTIUE rather than STOP EXECUTION operation. Since the SYSDEFAULTMANAGEDSUBCLASS is where heavier weight queries run (>150000 timerons as defined by the work class set), this threshold tells the system to queue activities in that class where the number of concurrent activities is greater than 8.
As the number of nodes increases, the number of concurrent heavy weigh queries may not change by much. This is because even though work is distributed across the nodes, the resource consumption per node may not decrease as the number of nodes increases. However, as the number of nodes increases the time to execute the statement will likely be reduced.
Minor Modifications to the Default Big SQL Workload Management Configuration
The statements below can be used to modify the default WLM configuration. In this case the number of concurrent queries is increased from 8 to 10 and the classification of heavy weight queries is increased from a timeron cost of 150000 to 200000. However, it is not recommended to blinding increase these thresholds without some insight into how the cluster is performing and the resource consumption of the workload.
ALTER THRESHOLD "SYSDEFAULTCONCURRENT" WHEN CONCURRENTDBCOORDACTIVITIES > 10 AND QUEUEDACTIVITIES UNBOUNDED STOP EXECUTION; ALTER WORK CLASS SET "SYSDEFAULTUSERWCS" ALTER WORK CLASS "SYSMANAGEDQUERIES" FOR TIMERONCOST FROM 200000 TO UNBOUNDED ; Conclusion
This blog outlined some of the major workload management concepts and described the default configuration. Future blogs will show how to modify the defaults for more advanced workload management configurations.