ALTER RESOURCE GROUP
The ALTER RESOURCE GROUP statement is used to modify a resource group in a database.
Synopsis
- AlterResourceGroupStmt
- IfExists
- ResourceGroupName
- ResourceGroupOptionList
- DirectResourceGroupOption
- ResourceGroupPriorityOption
- ResourceGroupRunawayOptionList
- DirectResourceGroupRunawayOption
- ResourceGroupRunawayWatchOption
- ResourceGroupRunawayActionOption
- BackgroundOptionList
- DirectBackgroundOption
AlterResourceGroupStmt ::=
"ALTER" "RESOURCE" "GROUP" IfExists ResourceGroupName ResourceGroupOptionList
IfExists ::=
('IF' 'EXISTS')?
ResourceGroupName ::=
Identifier
| "DEFAULT"
ResourceGroupOptionList ::=
DirectResourceGroupOption
| ResourceGroupOptionList DirectResourceGroupOption
| ResourceGroupOptionList ',' DirectResourceGroupOption
DirectResourceGroupOption ::=
"RU_PER_SEC" EqOpt LengthNum
| "PRIORITY" EqOpt ResourceGroupPriorityOption
| "BURSTABLE"
| "BURSTABLE" EqOpt "MODERATED"
| "BURSTABLE" EqOpt "UNLIMITED"
| "BURSTABLE" EqOpt "OFF"
| "QUERY_LIMIT" EqOpt '(' ResourceGroupRunawayOptionList ')'
| "QUERY_LIMIT" EqOpt '(' ')'
| "QUERY_LIMIT" EqOpt "NULL"
| "BACKGROUND" EqOpt '(' BackgroundOptionList ')'
| "BACKGROUND" EqOpt '(' ')'
| "BACKGROUND" EqOpt "NULL"
ResourceGroupPriorityOption ::=
LOW
| MEDIUM
| HIGH
ResourceGroupRunawayOptionList ::=
DirectResourceGroupRunawayOption
| ResourceGroupRunawayOptionList DirectResourceGroupRunawayOption
| ResourceGroupRunawayOptionList ',' DirectResourceGroupRunawayOption
DirectResourceGroupRunawayOption ::=
"EXEC_ELAPSED" EqOpt stringLit
| "PROCESSED_KEYS" EqOpt intLit
| "RU" EqOpt intLit
| "ACTION" EqOpt ResourceGroupRunawayActionOption
| "WATCH" EqOpt ResourceGroupRunawayWatchOption "DURATION" EqOpt stringLit
ResourceGroupRunawayWatchOption ::=
EXACT
| SIMILAR
ResourceGroupRunawayActionOption ::=
DRYRUN
| COOLDOWN
| KILL
| "SWITCH_GROUP" '(' ResourceGroupName ')'
BackgroundOptionList ::=
DirectBackgroundOption
| BackgroundOptionList DirectBackgroundOption
| BackgroundOptionList ',' DirectBackgroundOption
DirectBackgroundOption ::=
"TASK_TYPES" EqOpt stringLit
| "UTILIZATION_LIMIT" EqOpt LengthNum
TiDB supports the following DirectResourceGroupOption, where Request Unit (RU) is a unified abstraction unit in TiDB for CPU, IO, and other system resources.
| Option | Description | Example |
|---|---|---|
RU_PER_SEC | Rate of RU backfilling per second | RU_PER_SEC = 500 indicates that this resource group is backfilled with 500 RUs per second |
PRIORITY | The absolute priority of tasks to be processed on TiKV | PRIORITY = HIGH indicates that the priority is high. If not specified, the default value is MEDIUM. |
BURSTABLE | Whether to allow the resource group to overuse the available remaining system resources | Starting from v9.0.0, the following three modes are supported: OFF indicates that the resource group is not allowed to overuse any remaining system resources. MODERATED indicates that the resource group is allowed to overuse remaining system resources to a limited extent. UNLIMITED indicates that the resource group can overuse remaining system resources without limitation. If no value is specified for BURSTABLE, the MODERATED mode is enabled by default. |
QUERY_LIMIT | When the query execution meets this condition, the query is identified as a runaway query and the corresponding action is executed. | QUERY_LIMIT=(EXEC_ELAPSED='60s', ACTION=KILL, WATCH=EXACT DURATION='10m') indicates that the query is identified as a runaway query when the execution time exceeds 60 seconds. The query is terminated. All SQL statements with the same SQL text will be terminated immediately in the coming 10 minutes. QUERY_LIMIT=() or QUERY_LIMIT=NULL means that runaway control is not enabled. See Runaway Queries. |
BACKGROUND | Configure the background tasks. For more details, see Manage background tasks. | BACKGROUND=(TASK_TYPES="br,stats", UTILIZATION_LIMIT=30) indicates that the backup and restore and statistics collection related tasks are scheduled as background tasks, and background tasks can consume 30% of the TiKV resources at most. |
Examples
Create a resource group named rg1 and modify its properties.
DROP RESOURCE GROUP IF EXISTS rg1;
Query OK, 0 rows affected (0.22 sec)
CREATE RESOURCE GROUP IF NOT EXISTS rg1
RU_PER_SEC = 100
BURSTABLE;
Query OK, 0 rows affected (0.08 sec)
SELECT * FROM information_schema.resource_groups WHERE NAME ='rg1';
+------+------------+----------+-----------+-------------+------------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE | QUERY_LIMIT | BACKGROUND |
+------+------------+----------+-----------+-------------+------------+
| rg1 | 100 | MEDIUM | OFF | NULL | NULL |
+------+------------+----------+-----------+-------------+------------+
1 rows in set (1.30 sec)
ALTER RESOURCE GROUP rg1
RU_PER_SEC = 200
PRIORITY = LOW
QUERY_LIMIT = (EXEC_ELAPSED='1s' ACTION=COOLDOWN WATCH=EXACT DURATION '30s');
Query OK, 0 rows affected (0.08 sec)
SELECT * FROM information_schema.resource_groups WHERE NAME ='rg1';
+------+------------+----------+-----------+----------------------------------------------------------------+------------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE | QUERY_LIMIT | BACKGROUND |
+------+------------+----------+-----------+----------------------------------------------------------------+------------+
| rg1 | 200 | LOW | OFF | EXEC_ELAPSED='1s', ACTION=COOLDOWN, WATCH=EXACT DURATION='30s' | NULL |
+------+------------+----------+-----------+----------------------------------------------------------------+------------+
1 rows in set (1.30 sec)
Modify the BACKGROUND option for the default resource group.
ALTER RESOURCE GROUP default BACKGROUND = (TASK_TYPES = "br,ddl", UTILIZATION_LIMIT=30);
Query OK, 0 rows affected (0.08 sec)
SELECT * FROM information_schema.resource_groups WHERE NAME ='default';
+---------+------------+----------+------------------+-------------+-------------------------------------------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE | QUERY_LIMIT | BACKGROUND |
+---------+------------+----------+------------------+-------------+-------------------------------------------+
| default | UNLIMITED | MEDIUM | UNLIMITED | NULL | TASK_TYPES='br,ddl', UTILIZATION_LIMIT=30 |
+---------+------------+----------+------------------+-------------+-------------------------------------------+
1 rows in set (1.30 sec)
MySQL compatibility
MySQL also supports ALTER RESOURCE GROUP. However, the acceptable parameters are different from that of TiDB so that they are not compatible.