DB2 10.5 for Linux, UNIX, and Windows

Default query concurrency management

To ensure that heavier workloads that use column-organized data do not overload the system when many queries are submitted simultaneously, there is a limit on the number of "heavyweight" queries that can run against a database at the same time.

You can implement this limit by using the default workload management concurrency threshold. This threshold is automatically enabled on new databases if you set the value of the DB2_WORKLOAD registry variable to ANALYTICS. You can manually enable the threshold on existing databases.

The processing of queries against column-organized tables is designed to run fast by using the highly parallelized in-memory processing of data. The trade-off for this high performance is that queries that reference column-organized tables also use a relatively large amount of memory and CPU when compared to similar queries processing row-organized table data. As such, the execution of queries that process column-organized tables is optimal when relatively few queries are admitted to the system at a time. When this occurs, the queries can use more processing power and memory, and contention for the processor caches is minimized.

Also, field experience with DB2® workload management has demonstrated that in analytic environments that support mixed workloads, controlling the admission of heavyweight queries into the system yields improvements in both system stability and overall performance. A mixed workload environment is one in which queries might vary widely in their degree of complexity and resource needs. The reason for the improvements is that resource overload on the system is avoided. When the limit on the number of heavyweight queries is reached, the remaining queries are queued and must wait until other queries are complete before they can run. Queuing can help to ensure system stability when many complex ad hoc queries are running on systems that do not have a specific workload management strategy. To further optimize the execution of mixed workloads on your system, review the full range of DB2 workload management capabilities. For details, see Implementing DB2 workload management in a data warehouse.

Default workload management objects for concurrency control

For concurrency control, several default workload management objects are created for new or upgraded databases.

A service subclass, SYSDEFAULTMANAGEDSUBCLASS, under the SYSDEFAULTUSERCLASS superclass, where heavyweight queries run and can be controlled and monitored as a group.
A CONCURRENTDBCOORDACTIVITIES threshold, SYSDEFAULTCONCURRENT, which is applied to the SYSDEFAULTMANAGEDSUBCLASS subclass to control the number of concurrently running queries that are running in that subclass.
A work class set, SYSDEFAULTUSERWCS, and a work class, SYSMANAGEDQUERIES, which identify the class of heavyweight queries to control. The SYSMANAGEDQUERIES work class includes queries that are classified as READ DML (a work type for work classes) and that exceed a timeron threshold that reflects heavier queries.
A work action set, SYSDEFAULTUSERWAS, and work action, SYSMAPMANAGEDQUERIES, which map all queries that fall into the SYSMANAGEDQUERIES work class to the SYSDEFAULTMANAGEDSUBCLASS service subclass.

When you create or upgrade a database, the following behavior applies to the new default objects:

The work action set is enabled by default so that queries that meet the criteria that you specify for the SYSMANAGEDQUERIES work class run in the SYSDEFAULTMANAGEDSUBCLASS service subclass.
If you set the value of the DB2_WORKLOAD registry variable to ANALYTICS, the threshold on the SYSDEFAULTMANAGEDSUBCLASS service subclass is enabled by default for newly created databases only.
Running the DB2 Configuration Advisor causes the work action set and the work action to be enabled if you disabled either of them and causes the threshold to be enabled or disabled, depending on whether you set the DB2_WORKLOAD registry variable to ANALYTICS.
You can manually enable or disable the work action set and the threshold. For more information about manual configuration, see Adjusting the default configuration.

In a customized workload management environment, the expectation is that this default concurrency control might not be required. You can leave it enabled to manage the work that maps to the SYSDEFAULTUSERCLASS superclass or disable it.

The default concurrency limit

A limit on the number of queries that can run concurrently should protect the system from overload but also avoid over-throttling the workload to a point where performance might suffer. This objective can be challenging when a system is running ad hoc analytic queries that range widely in complexity and impact. The default limit is based on a heuristic calculation that factors in system hardware attributes such as the number of CPU sockets, CPU cores, and threads per core. The limit is calculated based on the current host configuration when the default workload management objects for concurrency control are created during database creation or upgrade. The limit is recalculated when you run the DB2 Configuration Advisor against the database. Rerun the DB2 Configuration Advisor if you make changes to the host, such as enabling or removing CPU capacity.

Adjusting the default configuration

Although the objective is to provide basic query management for analytic data mart deployments without the need for manual intervention, there might be scenarios where adjustments to the default configuration are a good idea.

Only the following subset of workload management DDL statements is supported by the default workload management objects for concurrency control in DB2 Version 10.5.

You can enable or disable the mapping of heavyweight READ DML queries to the SYSDEFAULTMANAGEDSUBCLASS subclass, as shown in the following examples:

# Enable the default work action mapping so that
# heavyweight queries are mapped to the
# SYSDEFAULTMANAGEDSUBCLASS subclass
ALTER WORK ACTION SET SYSDEFAULTUSERWAS ENABLE

# Disable the default work action mapping so that all
# queries are mapped to the SYSDEFAULTSUBCLASS subclass
ALTER WORK ACTION SET SYSDEFAULTUSERWAS DISABLE

You can adjust the timeron range for the mapping of heavyweight READ DML queries, as shown in the following example:

# Modify the default work class timeron range from A to B
ALTER WORK CLASS SET SYSDEFAULTUSERWCS
  ALTER WORK CLASS SYSMANAGEDQUERIES FOR TIMERONCOST FROM <A> TO <B>

You can enable or disable the concurrency threshold on the SYSDEFAULTMANAGEDSUBCLASS subclass, as shown in the following example:

# Enable the concurrency threshold
ALTER THRESHOLD SYSDEFAULTCONCURRENT ENABLE

# Disable the concurrency threshold
ALTER THRESHOLD SYSDEFAULTCONCURRENT DISABLE

You can adjust the concurrency limit, as shown in the following example:

# Change the concurrency limit to [N]
ALTER THRESHOLD SYSDEFAULTCONCURRENT
  WHEN CONCURRENTDBCOORDACTIVITIES > [N] CONTINUE

The following optional clauses are supported when you alter the SYSDEFAULTCONCURRENT threshold:

alter-threshold-exceeded-actions
AND QUEUEDACTIVITIES

Tuning the settings

The recommended method for tuning the default object settings is to examine the overall system resource usage with a full workload and to perform incremental adjustments that are based on whether the system resources appear to be under-utilized or over-utilized. A system with a run queue depth of approximately 10 times the number of CPU cores and physical memory usage under 100% is considered to be in a fully used and healthy state.

The following recommendations represent a starting point for tuning the default concurrency threshold value and the default work class timeron range when the system appears to be under-utilized or over-utilized. This guidance applies to a non-customized workload management environment. For a more comprehensive set of recommendations that applies to monitoring both system use and workload characteristics, see Implementing DB2 workload management in a data warehouse.

If the system appears to be under-utilized, take the following steps:
1. Examine the WLM_QUEUE_TIME_TOTAL metric, which is reported by various system-level table functions and the statistics event monitor, to determine whether queries in the system are accumulating time by waiting on concurrency thresholds.
  1. If no such queue time is being accumulated, the system is running under peak capacity, and no tuning is necessary.
  2. If queue time is being accumulated, take the following steps:
    1. Monitor the amount of work that is running in the SYSDEFAULTSUBCLASS and SYSDEFAULTMANAGEDSUBCLASS classes.
    2. If it appears that too large a proportion of the workload is running within the SYSMANAGEDQUERIES class, consider incrementally increasing the TIMERONCOST minimum for the SYSMANAGEDQUERIES class.
2. If the distribution of managed and unmanaged work appears reasonable, consider incrementally increasing the concurrency limit that is specified by the SYSDEFAULTCONCURRENT threshold until system resource usage reaches the target level.
If the system appears to be over-utilized, take the following steps:
1. Monitor the amount of work that is running in the SYSDEFAULTSUBCLASS and SYSDEFAULTMANAGEDSUBCLASS classes.
2. If it appears that too small a proportion of the workload is running within the SYSMANAGEDQUERIES class, consider incrementally decreasing the TIMERONCOST minimum for the SYSMANAGEDQUERIES class.
3. If the distribution of managed and unmanaged work appears reasonable, consider incrementally decreasing the concurrency limit that is specified by the SYSDEFAULTCONCURRENT threshold until system resource usage is back within the target range.