• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Resource Load Management
  5. Priority Scheduling
  6. CPU Priority Scheduling

CPU Priority Scheduling

Priority scheduling can be based on CPU or I/O usage. The former puts new tasks in a resource pool based on the priority of associated cgroups, and the latter suspends I/O requests in the database. This section mainly describes CPU priority scheduling.

Prerequisites

Resource load management has been enabled.

Context

After the number of concurrent tasks of a resource pool reaches the upper limit, new tasks enter the waiting queue of the resource pool.

Queue in load management shows the queue mechanism for executing waiting tasks when resource load management is enabled.

Figure 1 Queue in load management

Users' tasks enter queues of CNs, queues of resource pools, and then resource pools for execution. The process is described as follows:

  1. Users' tasks are distributed by the Linux Virtual Server (LVS) to queues of CNs. Alternatively, tasks are directly distributed by users to the queues. On any CN, tasks are processed in the first-in-first-out (FIFO) principle, which means that a task entering the queue of a CN earlier than another task enters the following step before the other task.
  2. In the queue of any CN, a task is distributed to the queue of the resource pool corresponding to the task according to the following rules: based on the following rules:
    • The tasks in the queue of a CN are distributed in the FIFO principle.
    • A task is distributed to the queue of the resource pool bound with the user who initiates the task. If no resource pool is bound with the user, the task is distributed to the queue of the default resource pool default_pool.
  3. The tasks in the queue of a resource pool are executed in the FIFO principle. If the idle resources in a resource pool meet the requirement of the first task (counted from bottom to top) in the queue of the resource pool, the task enters the resource pool for execution.

Procedure

  • Adjust resource pool priorities.

    If the resource proportion of a resource pool changes, change the priority of the resource pool accordingly. You can change the priority by modifying attributes in the resource pool.

    ALTER RESOURCE POOL respool2 WITH(CONTROL_GROUP = "Rush");
    ALTER RESOURCE POOL
    NOTE:

    The preceding statement changes the attribute of the cgroup associated with a resource pool to Rush.

  • Adjust the priority of the current user. You can change the priority of a user by changing the resource pool it binds to. For example:
    ALTER USER user1 WITH RESOURCE POOL 'respool2';
    ALTER ROLE

    Where user1 is the user whose priority is to be changed, and respool2 is the resource pool it is to be bound to. You need the sysadmin permission to change the priority.

  • Adjust the priority of the current session.

    You can change the cgroup bound with the current session to adjust the priorities of the tasks in the session. For example:

    SET CGROUP_NAME="Rush";
    SET

    View the cgroup bound to the current session.

    SHOW CGROUP_NAME;
  • Manually changing the positions of tasks in queues.

    If the priority of a task must be increased or a task must be executed at an earlier time, an administrator can change the position of the task in the queue.

    1. Query the PID of the task in the queue of a CN.
      SELECT * FROM pg_stat_activity where state = 'pending';
    2. Move the task to the first position of the queue.
      SELECT pg_wlm_jump_queue(pid);
    3. Change the priority of the task to High during the task execution. You can query the PID and thread ID of the task using the pg_stat_activity and pg_session_wlmstat view, respectively.
      SELECT gs_wlm_switch_cgroup(threadid, 'High');
    NOTE:

    Only the administrator can adjust the priorities of tasks being executed.