Skip to content

Latest commit

 

History

History
189 lines (149 loc) · 7.99 KB

sql-statement-alter-resource-group.md

File metadata and controls

189 lines (149 loc) · 7.99 KB
title summary
ALTER RESOURCE GROUP
Learn the usage of ALTER RESOURCE GROUP in TiDB.

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 {{{ .starter }}} and {{{ .essential }}} clusters.

Synopsis

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.

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   | 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.

See also