ALTER RESOURCE GROUP

The ALTER RESOURCE GROUP statement is used to modify a resource group in a database.

Note

This feature is not available on TiDB Serverless clusters.

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 stringLit
|   "PRIORITY" EqOpt ResourceGroupPriorityOption
|   "BURSTABLE"
|   "BURSTABLE" EqOpt Boolean
|   "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
|   "ACTION" EqOpt ResourceGroupRunawayActionOption
|   "WATCH" EqOpt ResourceGroupRunawayWatchOption "DURATION" EqOpt stringLit
ResourceGroupRunawayWatchOption ::=
    EXACT
|   SIMILAR
ResourceGroupRunawayActionOption ::=
    DRYRUN
|   COOLDOWN
|   KILL
BackgroundOptionList ::=
    DirectBackgroundOption
|   BackgroundOptionList DirectBackgroundOption
|   BackgroundOptionList ',' DirectBackgroundOption
DirectBackgroundOption ::=
    "TASK_TYPES" EqOpt stringLit

TiDB supports the following DirectResourceGroupOption, where Request Unit (RU) is a unified abstraction unit in TiDB for CPU, IO, and other system resources.

Note

• The ALTER RESOURCE GROUP statement can only be executed when the global variable tidb_enable_resource_control is set to ON.
• The ALTER RESOURCE GROUP statement supports incremental changes, leaving unspecified parameters unchanged. However, both QUERY_LIMIT and BACKGROUND are used as a whole and cannot be partially modified.
• Currently, only the default resource group supports modifying the BACKGROUND configuration.

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   | NO        | 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      | NO        | 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");

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   | YES       | NULL        | TASK_TYPES='br,ddl' |
+---------+------------+----------+-----------+-------------+---------------------+
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.

See also

• DROP RESOURCE GROUP
• CREATE RESOURCE GROUP
• Request Unit (RU)