Query Band Load Identification

Context

GaussDB(DWS) implements load identification and intra-queue priority control based on query_band. It provides more flexible load identification methods and identifies load queues based on job types, application names, and script names. Users can flexibly configure query_band identification queues based on service scenarios. In addition, priority control of job delivery in the queue is implemented. In the future, priority control of resources in the queue will be gradually implemented.

Administrators can configure the queue associated with query_band and estimate the memory limit based on service scenarios and job types to implement more flexible load control and resource management and control. If query_band is not configured for the service or the user does not associate query_band with an action, the user is associated with the queue and the priority in the queue is used by default.

Prerequisites

use_workload_manager has been set to on, indicating that load management control is enabled.

Procedure

1. Set the associated resource pool to p1, priority to rush, and order to 1 for query_band JobName to abc.

   1 2 3 4 5

   SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','respool=p1;priority=rush',1); gs_wlm_set_queryband_action ----------------------------- t (1 row)

2. Change the associated resource pool to p2 for query_band JobName=abc.

   1 2 3 4 5

   SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','respool=p2'); gs_wlm_set_queryband_action ----------------------------- t (1 row)

3. Change the priority to high for query_band JobName=abc.

   1 2 3 4 5

   SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','priority=high'); gs_wlm_set_queryband_action ----------------------------- t (1 row)

4. Change the order to 3 for query_band JobName=abc.

   1 2 3 4 5

   SELECT * FROM gs_wlm_set_queryband_order('JobName=abc',3); gs_wlm_set_queryband_order ----------------------------- t (1 row)

• The pg_workload_action cross-database system catalog is used to store the query_band action and order. For details, see PG_WORKLOAD_ACTION.
• As an identifier, query_band indicates only load identification. To limit the system catalog size, the default actions (for example, default resource pool or medium priority) are not stored in the system catalog. You can query the specific action of query_band through the pg_queryband_action view.
• The gs_wlm_set_queryband_action function is used to configure query_band actions. The maximum length of the first parameter, that is, the query_band key value pair, is 63 characters. For the second parameter, it is case-insensitive and multiple actions are separated by semicolons (;). order is the default parameter and its default value is -1. query_band supports the following associated actions: resource pool and intra-queue priority. For details, see gs_wlm_set_queryband_action in the resource management function.
• The gs_wlm_set_queryband_order function sets the query_band sequence. The maximum length of the first parameter, that is, the queryband key value pair, is 63 characters. The value of query_band must be greater than or equal to –1. Except the default value –1, the value of query_band must be unique. If query_bands have the same order during order configuration, increase the original query_band order by 1. For details, see gs_wlm_set_queryband_order in the resource management function.
• The gs_wlm_get_queryband_action function queries the query_band action. For details, see gs_wlm_set_queryband_action in the resource management function.
• pg_queryband_action is used to query all query_band actions in the system view. For details, see PG_QUERYBAND_ACTION.
• query_band associated with resource pool: query_band can be associated with queues. During job execution, if a queue is specified by query_band, the queue associated with query_band is used. Otherwise, the queue associated with the user is used.
  • When query_band is associated with a resource pool, an error is reported if the resource pool does not exist, and the association fails.
  • When query_band is associated with a resource pool, the dependency between query_band and the resource pool is recorded.
  • When the resource pool associated with query_band is deleted, a message is displayed indicating that the resource pool fails to be deleted because of the dependency between query_band and the resource pool.
• Intra-queue priority of query_band: query_band can be associated with job priorities, including high, medium, and low. Rush is provided as a special priority (green channel). The default priority is medium. In practice, most jobs use the medium priority, low-priority jobs use the low priority, and privileged jobs use the high priority. It is not recommended that a large number of jobs use the high priority. The rush priority is used only in special scenarios and is not recommended in normal cases.

  The intra-queue priority is used to implement the queuing priority.

  • In the static load management scenario, when the CN concurrency is insufficient, CN global queuing is triggered. The CN global queue is a priority queue.
  • In the dynamic load management scenario, if the DN memory is insufficient, CCN global queuing is triggered. The CCN global queue is a priority queue.
  • When the resource pool concurrency or memory is insufficient, resource pool queuing is triggered. The resource pool queue is a priority queue.

  The preceding priority queues comply with the following scheduling rules:

  • Jobs with a higher priority are scheduled first.
  • After all jobs with a high priority are scheduled, jobs with a low priority are scheduled.
  • In dynamic load management scenarios, the CN global queue does not support the query_band priority.
• The query_band priority is displayed as an integer in the load management view. The mapping between numbers and priorities is as follows:
  • 0: not controlled by load management
  • 1: low
  • 2: medium
  • 4: high
  • 8: rush
• The default action and order are not stored in the pg_workload_action system catalog. If a non-default action is set for query_band, the default action is also displayed when actions are queried. The message <query_band information not found> is displayed when the action and order to be queried are the default query_band action.
• query_band is set in key-value pair mode, that is, 'key=value'. Multiple query_band key-value pairs can be set in a session. Multiple key-value pairs are separated by semicolons (;). The maximum length of both the query_band key-value pair and parameter value is 1024 characters.
• The query_band key-value pair supports the following valid characters: digits 0 to 9, uppercase letters A to Z, lowercase letters a to z, '.', '-', '_', and '#'.
• When multiple query_band key-value pairs are set in a session, the query_band key-value pair with a smaller order number is used as query_band for load identification. The minimum order number is 0, and the default order number –1 is the maximum order number. When the orders are the default order, query_band that is set ahead is used as query_band for load identification. For example, in set query_band='b=1;a=3;c=1'; b=1, indicating the order -1, a=3, indicating the order 4; c=1, indicating the order 1. In this case, c=1 is used as query_band for load identification. This design enables the load administrator to adjust load scheduling.
• Permission control: Except initial users, other users have the permission to set and query query_band only when they are authorized.
• When all running jobs are canceled in batches or the maximum number of concurrent jobs in a queue is 1 and only one queue is running jobs, the CN may be triggered to automatically wake up jobs. As a result, jobs are not delivered by priority.

Query Band Actions

Table 1 lists the actions supported by query_band.

1
2
3
4
5
6
7

SELECT * FROM pg_queryband_action;
 qband | respool_id | respool | priority | qborder
-------+------------+---------+----------+---------
 a=2   |          0 | NULL    | high     |       2
 a=3   |      16388 | p1      | rush     |       1
 a=1   |      16388 | p1      | Medium   |      -1
(3 rows)