This document describes how to use resource groups to manage transaction concurrency and CPU and memory resource allocation in YMatrix. After a resource group is defined, it can be assigned to one or more database roles to control the resources those roles can use.
YMatrix uses Linux control groups to manage database resources, and uses Runaway for memory accounting, tracking, and management.
The following table lists the configurable resource group parameters:
| Parameter | Description | Value Range | Default |
|---|---|---|---|
| CONCURRENCY | Maximum number of concurrent transactions allowed in the resource group, including active and idle transactions. | [0 - max_connections] | 20 |
| CPU_MAX_PERCENT | Maximum percentage of CPU resources the resource group can use. | [1 - 100] | -1 (disabled) |
| CPU_WEIGHT | Scheduling priority of the resource group. | [1 - 500] | 100 |
| CPUSET | Specific CPU logical cores (or logical threads in hyper-threading) reserved for this resource group. | Depends on the system core configuration | -1 |
| MEMORY_QUOTA | Memory limit assigned to the resource group. | Integer (MB) | -1 (disabled; uses statement_mem as the per-query memory limit) |
| MIN_COST | Minimum cost for a query plan to be included in the resource group. | Integer | 0 |
| IO_LIMIT | Sets device I/O usage to manage the maximum read/write throughput and the maximum number of read/write operations per second. Set this value per tablespace. | [2 - 4294967295 or max] |
-1 |
Note!
Resource groups do not take effect forSET/RESET/SHOWcommands.
When a user runs a query, YMatrix evaluates the query state against the set of limits defined for the resource group.
CONCURRENCY controls the maximum number of concurrent transactions allowed in a resource group. The default limit is 20. A value of 0 means that this resource group is not allowed to run queries.
If the resource limit has not been reached and the query would not exceed the concurrency limit of the resource group, YMatrix runs the query immediately. If the maximum number of concurrent transactions has been reached, YMatrix queues any transaction submitted after the resource group reached its CONCURRENCY limit, and runs it after other queries complete.
gp_resource_group_queuing_timeout specifies how long a queued transaction waits before it is canceled. The default is 0, meaning that transactions are queued indefinitely.
Bypassing Resource Group Allocation Limits
gp_resource_group_bypass enables or disables the concurrency transaction limit of resource groups so that queries can execute immediately. When set to true, queries skip the concurrency limit of the resource group. In this case the memory quota of the query is allocated according to statement_mem; if memory is insufficient, the query fails. This parameter can be set only in a single session, not within a transaction or function.gp_resource_group_bypass_catalog_query controls whether system catalog queries bypass the resource limits of resource groups. The default is true. It applies to cases such as database GUI clients running catalog queries to fetch metadata. Their resource allocation is outside resource groups, with a per-query memory quota of statement_mem.gp_resource_group_bypass_direct_dispatch controls whether direct dispatch queries bypass the limits of resource groups. When set to true, such queries are no longer constrained by the CPU or memory limits of their assigned resource group and execute immediately. In this case the memory quota of the query is allocated according to statement_mem; if memory is insufficient, the query fails. This parameter can be set only in a single session, not within a transaction or function.YMatrix allocates CPU resources in two ways:
Different CPU resource allocation modes can be configured for different resource groups on the same cluster, but a single resource group can use only one mode. The CPU allocation mode of a resource group can also be changed at runtime.
gp_resource_group_cpu_limit determines the maximum percentage of system CPU resources allocated to resource groups on each segment node.
Allocating CPU Resources by Core
CPUSET sets the CPU cores reserved for a resource group. When CPUSET is configured for a resource group, YMatrix disables CPU_MAX_PERCENT and CPU_WEIGHT for that group and sets their values to -1.
Usage notes:
'1;1,3-4' uses core 1 on the master node, and cores 1, 3, and 4 on segments.Allocating CPU Resources by Percentage
CPU_MAX_PERCENT sets the upper limit of CPU resources that a segment can use; for example, setting it to 40 means that at most 40% of the available CPU resources can be used. When tasks in a resource group are idle and consume no CPU time, the remaining time is collected into a global pool of unused CPU cycles, from which other resource groups can borrow.
CPU_WEIGHT sets the scheduling priority of the current group. The default is 100, and the value range is 1 to 500. It specifies the relative share of CPU time available to tasks in the resource group.
Usage notes:
CPU_MAX_PERCENT is set to 100 for all groups), the first resource group receives 50% of the total CPU time, and the other two groups receive 25% each.CPU_MAX_PERCENT set to 100) is added, the first group can use only 33% of the CPU, and the remaining groups receive 16.5%, 16.5%, and 33% respectively.Configuration Example
| Group Name | CONCURRENCY | CPU_MAX_PERCENT | CPU_WEIGHT |
|---|---|---|---|
| default_group | 20 | 50 | 10 |
| admin_group 10 | 70 | 30 | |
| system_group | 10 | 30 | 10 |
| test | 10 | 10 | 10 |
default_group), the available CPU percentage determined by the CPU_WEIGHT parameter is 10/(10+30+10+10)=16%. This means that when system load is high, they can use at least 16% of the CPU resources. When the system has idle CPU resources, they can use more, because the hard limit (set by CPU_MAX_PERCENT) is 50%.admin_group), the available CPU percentage when system load is high is 30/(10+30+10+10)=50%. When the system has idle CPU resources, the upper limit of resources they can use is determined by the hard limit of 70%.test), the available CPU percentage is 10/(10+30+10+10)=16%. However, because the hard limit (determined by CPU_MAX_PERCENT) is 10%, they can use at most 10% of the resources even when the system is idle.MEMORY_QUOTA is the maximum amount of memory reserved for the resource group on a Segment. It is the total memory that the worker processes of all queries on the Segment can consume during query execution. The amount of memory allocated to a query is the group memory limit divided by the group concurrency limit: MEMORY_QUOTA / CONCURRENCY.
If a query needs more memory, use gp_resgroup_memory_query_fixed_mem to set the required memory amount for the query at the session level. This setting can override and exceed the memory allocated by the resource group.
Usage notes
gp_resgroup_memory_query_fixed_mem is set, its value is used and the resource group setting is bypassed.gp_resgroup_memory_query_fixed_mem is not set, MEMORY_QUOTA / CONCURRENCY is used as the memory allocated to the query.MEMORY_QUOTA is not set, the memory allocation for queries defaults to statement_mem.gp_workfile_limit_files_per_query limit is reached, YMatrix generates an out-of-memory (OOM) error.Configuration Example
Consider a resource group named adhoc with MEMORY_QUOTA set to 1.5 GB and CONCURRENCY set to 3. By default, each statement submitted to this group is allocated 500 MB of memory. Now consider the following sequence of events:
ADHOC_1 submits query Q1, overriding gp_resgroup_memory_query_fixed_mem to 800 MB. Statement Q1 is admitted into the system.ADHOC_2 submits query Q2, using the default 500 MB.Q1 and Q2 are still running, user ADHOC_3 submits query Q3, using the default 500 MB.Q1 and Q2 have already used 1300 MB of the resource group's 1500 MB. However, if the system has enough available memory for query Q3 at some point, Q3 can run normally.ADHOC_4 submits query Q4, with gp_resgroup_memory_query_fixed_mem set to 700 MB.
Because query Q4 bypasses the resource group limits, it runs immediately.Special usage notes
gp_resource_group_bypass or gp_resource_group_bypass_catalog_query is set to bypass resource group limits, the memory limit of the query is statement_mem.MEMORY_QUOTA / CONCURRENCY) < statement_mem, statement_mem is used as the fixed amount of memory allocated to the query.statement_mem is max_statement_mem.MIN_COST limit use statement_mem as their memory quota.IO_LIMIT limits the maximum disk read/write I/O throughput and the maximum number of read/write I/O operations per second allocated to queries of a specific resource group. It ensures the usage of high-priority resource groups and avoids excessive use of disk bandwidth. The value of this parameter must be set per tablespace.
Note!
IO_LIMITis supported only on Cgroup v2.
Disk I/O limits can be configured with the following parameters:
* to set limits for all tablespaces.rbps and wbps limit the maximum disk read/write throughput of the resource group, in MB/s. The default is max, meaning no limit.riops and wiops limit the maximum number of read/write I/O operations per second of the resource group. The default is max, meaning no limit. Configuration Notes
If the IO_LIMIT parameter is not set, the default values of rbps, wpbs, riops, and wiops are set to max, which means disk I/O is unlimited. If only some values of IO_LIMIT are set (for example, rbps), the unset parameters default to max (in this example, wbps, riops, and wiops default to max).
cgroup version configured in your environment by checking the file system mounted by default during system startup:stat -fc %T /sys/fs/cgroup/For cgroup v1, the output is tmpfs. For cgroup v2, the output is cgroup2fs.
Starting from 6.8.4, when you use the resource group module of MatrixUI, the manual check above is not required: the module automatically detects the resource group version supported by the cluster nodes.
If you do not need to change the cgroup version, skip to Configuring cgroup v1 or Configuring cgroup v2 to complete the configuration.
To switch from cgroup v1 to v2, run the following commands as root:
grubby --update-kernel=/boot/vmlinuz-$(uname -r) --args="systemd.unified_cgroup_hierarchy=1"vim /etc/default/grub
# add or modify: GRUB_CMDLINE_LINUX="systemd.unified_cgroup_hierarchy=1"
update-grubTo switch from cgroup v2 to v1, run the following commands as root:
grubby --update-kernel=/boot/vmlinuz-$(uname -r) --args="systemd.unified_cgroup_hierarchy=0 systemd.legacy_systemd_cgroup_controller"vim /etc/default/grub
# add or modify: GRUB_CMDLINE_LINUX="systemd.unified_cgroup_hierarchy=0"
update-grubIf you want to continue using cgroup v1, make sure that every memory.limit_in_bytes file under /sys/fs/cgroup/memory/gpdb (including /sys/fs/cgroup/memory/gpdb/memory.limit_in_bytes and /sys/fs/cgroup/memory/gpdb/[OID]/memory.limit_in_bytes) has no limit value. If it does, run:
echo -1 >> memory.limit_in_bytesThen restart the host for the changes to take effect.
Note!
You must use the superuser or a user withsudoaccess to edit this file.
vi /etc/cgconfig.confAdd the following information to the configuration file.
group gpdb {
perm {
task {
uid = mxadmin;
gid = mxadmin;
}
admin {
uid = mxadmin;
gid = mxadmin;
}
}
cpu {
}
cpuacct {
}
cpuset {
}
memory {
}
} This content configures the CPU, CPU accounting, CPU set, and memory control groups managed by the mxadmin user.
cgroup service on every node of the YMatrix cluster.cgconfigparser -l /etc/cgconfig.conf
systemctl enable cgconfig.service
systemctl start cgconfig.servicecgroup directorygrep cgroup /proc/mounts cgroup directory, /sys/fs/cgroup.tmpfs /sys/fs/cgroup tmpfs ro,nosuid,nodev,noexec,mode=755 0 0
cgroup /sys/fs/cgroup/systemd cgroup rw,nosuid,nodev,noexec,relatime,xattr,release_agent=/usr/lib/systemd/systemd-cgroups-agent,name=systemd 0 0
cgroup /sys/fs/cgroup/devices cgroup rw,nosuid,nodev,noexec,relatime,devices 0 0
cgroup /sys/fs/cgroup/net_cls,net_prio cgroup rw,nosuid,nodev,noexec,relatime,net_prio,net_cls 0 0
cgroup /sys/fs/cgroup/pids cgroup rw,nosuid,nodev,noexec,relatime,pids 0 0
cgroup /sys/fs/cgroup/freezer cgroup rw,nosuid,nodev,noexec,relatime,freezer 0 0
cgroup /sys/fs/cgroup/cpu,cpuacct cgroup rw,nosuid,nodev,noexec,relatime,cpuacct,cpu 0 0
cgroup /sys/fs/cgroup/perf_event cgroup rw,nosuid,nodev,noexec,relatime,perf_event 0 0
cgroup /sys/fs/cgroup/hugetlb cgroup rw,nosuid,nodev,noexec,relatime,hugetlb 0 0
cgroup /sys/fs/cgroup/memory cgroup rw,nosuid,nodev,noexec,relatime,memory 0 0
cgroup /sys/fs/cgroup/cpuset cgroup rw,nosuid,nodev,noexec,relatime,cpuset 0 0
cgroup /sys/fs/cgroup/blkio cgroup rw,nosuid,nodev,noexec,relatime,blkio 0 0 ls -l <cgroup_mount_point>/cpu/gpdb
ls -l <cgroup_mount_point>/cpuset/gpdb
ls -l <cgroup_mount_point>/memory/gpdbIf the directories exist and are owned by mxadmin:mxadmin, the cgroup resource management for the YMatrix database is configured successfully.
Configure the system to mount cgroups-v2 by default at system startup, using the systemd system and service manager as the root user.
grubby --update-kernel=ALL --args="systemd.unified_cgroup_hierarchy=1"Restart the system for the changes to take effect.
reboot nowCreate the directory /sys/fs/cgroup/matrixdb6.service, add all necessary controllers, and make sure the mxadmin user has read and write permissions on it.
mkdir -p /sys/fs/cgroup/matrixdb6.service
echo "+cpuset +io +cpu +memory" | tee -a /sys/fs/cgroup/cgroup.subtree_control
chown -R mxadmin:mxadmin /sys/fs/cgroup/matrixdb6.serviceAfter running the above commands, you may encounter an invalid parameter error. This is because cgroup v2 does not support controlling real-time processes, and the cpu controller can be enabled only when all real-time processes are in the root cgroup. In this case, find all real-time processes, move them to the root cgroup, and then re-enable the controller.
Make sure mxadmin has write permission on /sys/fs/cgroup/cgroup.procs. After the cluster starts, this moves the YMatrix processes from the user to /sys/fs/cgroup/matrixdb6.service/ to manage the postmaster service and all its required helper processes.
chmod a+w /sys/fs/cgroup/cgroup.procsBecause resource groups manage the cgroup files manually, the settings above are lost after a system reboot. Add the following bash script to systemd so that it runs automatically during system startup. Perform the following steps as the root user:
Create matrixdb6.service
vim /etc/systemd/system/matrixdb6.serviceWrite the following content to matrixdb6.service. If the user is not mxadmin, replace it with the corresponding user.
[Unit]
Description=Greenplum Cgroup v2 Configuration Service
[Service]
Type=simple
WorkingDirectory=/sys/fs/cgroup/matrixdb6.service
Delegate=yes
Slice=-.sliceExecCondition=bash -c '[ xcgroup2fs = x$(stat -fc "%%T" /sys/fs/cgroup) ] || exit 1' ExecStartPre=bash -ec " \ chown -R mxadmin:mxadmin .; \ chmod a+w ../cgroup.procs; \ mkdir -p helper.scope" ExecStart=sleep infinity ExecStartPost=bash -ec "echo $MAINPID > /sys/fs/cgroup/cgroup.procs;" [Install] WantedBy=basic.target
3. Reload the `systemd` daemon and enable the service:systemctl daemon-reload systemctl enable gpdb.service
## Enabling Resource Groups
1. Set the `gp_resource_manager` server configuration parameter to `"group"`gpconfig -c gp_resource_manager -v "group"
2. Restart the YMatrix database clustermxstop -arf
After resource groups are enabled, any transaction submitted by a role is directed to the resource group assigned to that role, and is subject to the concurrency, memory, and CPU limits of that resource group.
YMatrix creates the role resource groups `admin_group`, `default_group`, and `system_group` by default. When resource groups are enabled, any role that is not explicitly assigned a resource group is assigned to the default group for its role function. `SUPERUSER` roles are assigned to `admin_group`, non-administrator roles to `default_group`, and system processes to `system_group`. No role can be manually assigned to `system_group`.
The configuration of each role resource group is as follows.
Parameter | admin_group | default_group | system_group
--- | --- | --- | ---
CONCURRENCY | 10 | 5 | 0
CPU_MAX_PERCENT | 10 | 20 | 10
CPU_WEIGHT | 100 | 100 | 100
CPUSET | -1 | -1 | -1
IO_LIMIT | -1 | -1 | -1
MEMORY_LIMIT | -1 | -1 | -1
MIN_COST | 0 | 0 | 0
## Creating Resource Groups
The `CREATE RESOURCE GROUP` command creates a new resource group. When creating a resource group for roles, provide a name and a CPU resource allocation mode (cores or percentage). A `CPU_MAX_PERCENT` or `CPUSET` limit value must be provided.
**Usage Example**
Create a resource group named `rgroup1` with a CPU limit of 20, a memory limit of 250, a CPU priority of 500, and a minimum cost of 50:CREATE RESOURCE GROUP rgroup1 WITH (CONCURRENCY=20, CPU_MAX_PERCENT=20, MEMORY_QUOTA=250, CPU_WEIGHT=500, MIN_COST=50);
The CPU and memory limits are shared by every role assigned to `rgroup1`.
The `ALTER RESOURCE GROUP` command updates the limits of a resource group.ALTER RESOURCE GROUP rg_role_light SET CONCURRENCY 7; ALTER RESOURCE GROUP exec SET MEMORY_QUOTA 30; ALTER RESOURCE GROUP rgroup1 SET CPUSET '1;2,4';
> ***Note!***
> The `CONCURRENCY` value of `admin_group` cannot be set or changed to 0.
The `DROP RESOURCE GROUP` command drops a resource group. To drop a role's resource group, the group must not be assigned to any role, and there must be no active or waiting transactions in the resource group.
DROP RESOURCE GROUP exec;
## Configuring Automatic Query Termination Based on Memory Usage
YMatrix supports Runaway detection. For queries managed by resource groups, a query can be terminated automatically based on the amount of memory it uses.
The related configuration parameters are as follows:
- `gp_vmem_protect_limit`: Sets the amount of memory that all postgres processes in an active segment instance can consume. If a query causes this limit to be exceeded, memory is not allocated and the query fails.
- `runaway_detector_activation_percent`: When resource groups are enabled, if the amount of memory used exceeds `gp_vmem_protect_limit` * `runaway_detector_activation_percent`, YMatrix terminates queries managed by resource groups (excluding queries in the `system_group` resource group) based on memory usage. Termination starts with the query consuming the most memory and continues until memory usage falls below the specified percentage.
## Assigning Resource Groups to Roles
- Use the `RESOURCE GROUP` clause of the `CREATE ROLE` or `ALTER ROLE` command to assign a resource group to a database role.ALTER ROLE bill RESOURCE GROUP rg_light; CREATE ROLE mary RESOURCE GROUP exec;
A resource group can be assigned to one or more roles. If a role hierarchy has been defined, the resource group assigned to a parent role does not propagate down to members of that role group.
- To remove the resource group assignment from a role and assign the default group instead, change the role's group assignment to `NONE`.ALTER ROLE mary RESOURCE GROUP NONE;
## Monitoring Resource Group Status
- View resource group limitsSELECT * FROM gp_toolkit.gp_resgroup_config;
- View resource group query statusSELECT * FROM gp_toolkit.gp_resgroup_status;
- View memory usage of resource groups on each hostSELECT * FROM gp_toolkit.gp_resgroup_status_per_host;
- View the resource groups assigned to rolesSELECT rolname, rsgname FROM pg_roles, pg_resgroup WHERE pg_roles.rolresgroup=pg_resgroup.oid;
- View running and pending queries of resource groupsSELECT query, rsgname,wait_event_type, wait_event FROM pg_stat_activity;
- Cancel running or queued transactions in a resource group
To manually cancel a running or queued transaction, you must first determine the process id or pid associated with the transaction. Once you have the process id, call `pg_cancel_backend()` to terminate the process.
The steps are as follows:
- First run the following query to view the process information associated with all currently active or idle statements in all resource groups. If the query returns no results, there are no running or queued transactions in any resource group.SELECT rolname, g.rsgname, pid, waiting, state, query, datname
FROM pg_roles, gp_toolkit.gp_resgroup_status g, pg_stat_activity
WHERE pg_roles.rolresgroup=g.groupid
AND pg_stat_activity.usename=pg_roles.rolname; - Example query resultrolname | rsgname | pid | waiting | state | query | datname
---------+----------+---------+---------+--------+------------------------ -+---------
sammy | rg_light | 31861 | f | idle | SELECT * FROM mytesttbl; | testdb
billy | rg_light | 31905 | t | active | SELECT * FROM topten; | testdb
```SELECT pg_cancel_backend(31905);Note!
Do not use the operating systemKILLcommand to cancel any YMatrix database process.
A user with superuser privileges can run the gp_toolkit.pg_resgroup_move_query() function to move a running query from one resource group to another without stopping the query. This function can speed up a long-running query by moving it to a resource group with more resources or better availability.
pg_resgroup_move_query() moves only the specified query to the target resource group; queries submitted later are still assigned to the original resource group.
Note!
Only active or running queries can be moved to a new resource group. Queued or pending idle queries cannot be moved because of concurrency or memory limits.
pg_resgroup_move_query() requires the process id or pid of the running query and the name of the resource group to move the query to.
pg_resgroup_move_query( pid int4, group_name text );As described in "Cancel running or queued transactions in a resource group", you can use the gp_toolkit.gp_resgroup_status view to list the name, id, and status of each resource group.
When the pg_resgroup_move_query() function is called, the running query is subject to the limits configured for the target resource group, including concurrent task limits, memory limits, and so on.
gp_resource_group_queuing_timeout is set, for the specified number of milliseconds.pg_resgroup_move_query() attempts to hand control of the slot to the target process for up to the number of milliseconds specified by gp_resource_group_move_timeout. If the target process cannot handle the move request within the gp_resource_group_queuing_timeout time, the database returns an error.pg_resgroup_move_query() is canceled after the target process has obtained the slots, the segment processes are not moved to the new group and the target process keeps the slots. This inconsistent state is fixed when the transaction ends, or by any next command scheduled by the target process in the same transaction.After a query is moved, there is no guarantee that the queries currently running in the target resource group will not exceed the group's memory quota. In this case, one or more running queries in the target resource group may fail, including the moved query. Reserve enough global shared memory for resource groups to minimize the likelihood of this happening.