SQL throttling - PolarDB - Alibaba Cloud Documentation Center

PolarDB-X provides the SQL throttling feature. You can use this feature to specify limits for the execution of SQL statements that cause issues on database nodes. The issues include request spikes, consumption of a large number of resources, and modifications in the SQL access model. This feature ensures that your PolarDB-X instance runs in a continuous and stable manner. This topic describes how to use the SQL throttling feature.

Supported versions

The SQL throttling feature is supported only for PolarDB-X Enterprise Edition instances whose compute node version is 5.4.9-16167266 or later. Slow SQL throttling trigger is supported only for PolarDB-X Enterprise Edition instances whose compute node version is 5.4.11-16251897 or later.

Note

For information about the instance version naming rules, see Release notes.
For information about how to view the version of an instance, see View and update the version of an instance.

Create a throttling rule

Syntax

CREATE CCL_RULE [ IF NOT EXISTS ] `ccl_rule_name`
ON `database`.`table`
TO '<username>'@'<host>'
FOR { UPDATE | SELECT | INSERT | DELETE }
[ filter_options ]
with_options

filter_options:
    [ FILTER  BY KEYWORD('KEYWORD1', 'KEYWORD2',…) ]

Parameters:

Parameter		Required	Description
Parameters used to identify the SQL statements to be limited for throttling	`ccl_rule_name`	Yes	The name of the throttling rule. Note We recommend that you use backticks (`) to enclose the rule name to distinguish the rule name from SQL keywords.
	`database`.`table`	Yes	The name of the database and the name of the table. Asterisks () are supported as wildcard characters. Note* We recommend that you use backticks (`) to enclose each database name and table name to distinguish the name from SQL keywords.
	`'<username>'@'<host>'`	Yes	The username of the account. You can use a percent sign (%) as a wildcard character in the `host` part.
	`UPDATE \| SELECT \| INSERT \| DELETE`	Yes	The SQL query type. The value can be UPDATE, SELECT, INSERT, or DELETE. Note Each throttling rule applies only to one SQL query type.
	`[ filter_options ]`	No	The filter conditions. Valid values: KEYWORD: When you query the rule, the keywords are displayed in a string in the query result. The string format is `["kwd1","kw2","kw3"...]`. The string can contain a maximum of 512 characters. Note If the keyword matches a parameter value in an SQL statement, the matching is case-sensitive. If the keyword matches any other part of an SQL statement, the matching is case-insensitive. TEMPLATE: The SQL template ID, represented as a 16-bit hexadecimal hash value of the parameterized SQL statement. This value corresponds to the `sql_code` field in the SQL logs. You can execute the `SHOW FULL PROCESSLIST` or `EXPLAIN` statements to check the SQL template ID.
Parameters used to determine throttling actions	`with_options`	Yes	The WITH clause supports the following parameters to determine throttling actions: MAX_CONCURRENCY: the maximum concurrency of the SQL statements that match the rule. Valid values: 0 to 2³¹ - 1. Default value: 0. WAIT_QUEUE_SIZE: the maximum length of the waiting queue. After the maximum concurrency is reached, other SQL statements that match the throttling rule are added to the waiting queue. After the waiting queue reaches the maximum length, an error is reported each time the system detects another SQL statement that matches the throttling rule. Queued SQL statements consume threads. If the queue is long, out-of-memory (OOM) errors can occur. Valid values: 0 to 2³¹ - 1. Default value: 0. WAIT_TIMEOUT: the maximum duration for which an SQL statement can be retained in the queue. If the duration for which an SQL statement is retained in the queue reaches the maximum duration, an error is returned for the SQL statement. Valid values: 0 to 2³¹ - 1. Default value: 600. Unit: seconds. FAST_MATCH: specifies whether to enable caching to accelerate matching. If caching is enabled, the system uses the template ID as a part of the cache key for the statement. The cache value indicates whether the statement matches the throttling rule. This mechanism accelerates the matching process. Valid values: 0 and 1. The value 0 specifies that caching is disabled. The value 1 specifies that caching is enabled. Note When you create a throttling rule, you must specify at least one of the preceding options. The default value of the MAX_CONCURRENCY parameter is 0. If you use the default value, an error can occur each time when an SQL statement that matches the throttling rule is executed. To prevent this issue, set this parameter to a value that is not 0. PolarDB-X is a distributed cloud native database service. A PolarDB-X instance consists of multiple compute nodes. The maximum number of concurrent requests on an instance is the sum of the maximum number of concurrent requests on each node. If the loads on the compute nodes in an instance are unbalanced, the total number of concurrent SQL requests on the instance cannot reach the maximum number of concurrent SQL requests on the instance.

Note

The parameters that are included in the WITH clause take effect only on SQL statements that meet all conditions that are included in the parameters that are used to identify the SQL statements to be limited for throttling.

Examples

Create a throttling rule named selectrule to enforce a maximum concurrency of 10 for SELECT queries that are executed by the 'ccltest'@'%'user and contain the cclmatched keyword.

CREATE CCL_RULE IF NOT EXISTS `selectrule` ON *.* TO 'ccltest'@'%'
FOR SELECT
FILTER BY KEYWORD('cclmatched')
WITH MAX_CONCURRENCY=10;

Note

SELECT queries that are executed by the ccltest account from any IP and contain the cclmatched keyword have a maximum concurrency of 10.

Throttling results

After the system detects an SQL statement that matches the throttling rule, the SQL statement enters the following statements based on the WITH options specified in the throttling rule:

RUN
If the concurrency of SQL statements that match the rule does not reach the value specified by MAX_CONCURRENCY, the SQL statement is executed as expected.

WAIT

If the concurrency reaches the maximum concurrency but the number of queued SQL statements has not reached the value specified by WAIT_QUEUE_SIZE, the SQL statement enters the WAIT state and then enters the RUN or WAIT_TIMEOUT state.

You can execute the following statement to query the queued SQL statements that match the throttling rule:

SHOW FULL PROCESSLIST;

Sample result:

+----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
| ID | USER          | HOST            | DB       | COMMAND                       | TIME | STATE | INFO                  | SQL_TEMPLATE_ID |
+----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
|  2 | polardbx_root | ***.*.*.*:62787 | polardbx | Query                         |    0 |       | show full processlist | NULL            |
|  1 | polardbx_root | ***.*.*.*:62775 | polardbx | Query(Waiting-selectrulereal) |   12 |       | select 1              | 9037e5e2        |
+----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
2 rows in set (0.08 sec)

Note

The select 1 SQL statement matches the selectrulereal throttling rule and is waiting to be processed.

WAIT_TIMEOUT
A queued SQL statement fails to be executed and returns an error if its wait time exceeds the duration specified by WAIT_TIMEOUT.
For example, if you set the WAIT_TIMEOUT option to 10s for a throttling rule and the SELECT sleep(11) statement that matches the throttling rule stays in the queue for 10s, the following error is returned:
```
ERROR 3009 (HY000): [11a07e23fd800000][30.225.XXX.XX:8527][polardbx]Exceeding the max concurrency 0 of ccl rule selectrulereal after waiting for 10060 ms
```
KILL
If the system detects an SQL statement that matches the throttling rule when the maximum concurrency and the maximum queue length are reached, the client receives an error. The error message indicates that the maximum concurrency is reached and contains the name of the throttling rule that the SQL statement matches.
For example, if you execute the SELECT 1; statement when the maximum concurrency and the maximum queue length has reached and the statement matches the throttling rule, the following error is returned:
```
ERROR 3009 (HY000): [11a07c4425c00000][**.***.***.**:8527][polardbx]Exceeding the max concurrency 0 of ccl rule selectrulereal
```
Note
SELECT 1; fails to be executed beacause the maximum concurrency specified by selectrulereal is exceeded.

Query a throttling rule

Query a specific throttling rule

To view specific rules, specify their names in the following statement and separate the names with commas (,).

Syntax:

SHOW CCL_RULE `ccl_rule_name1` [, `ccl_rule_name2` ]

Example:

SHOW CCL_RULE `selectrulereal`

Sample result:

                     NO.: 1
               RULE_NAME: selectrulereal
                 RUNNING: 2
                 WAITING: 29
                  KILLED: 0
         MATCH_HIT_CACHE: 21374
             TOTAL_MATCH: 21406
       ACTIVE_NODE_COUNT: 2
MAX_CONCURRENCY_PER_NODE: 1
WAIT_QUEUE_SIZE_PER_NODE: 100
            WAIT_TIMEOUT: 600
              FAST_MATCH: 1
                SQL_TYPE: SELECT
                    USER: ccltest@%
                   TABLE: *.*
                KEYWORDS: ["SELECT"]
              TEMPLATEID: NULL
            CREATED_TIME: 2020-11-26 17:04:08

Fields:

Field	Description
NO.	The priority of the rule. A smaller value indicates a higher priority.
RULE_NAME	The name of the throttling rule.
RUNNING	The number of SQL statements that match the throttling rule and that are executed as expected.
WAITING	The number of queued requests that are sent by using SQL statements that match the throttling rules.
KILLED	The number of SQL statements that match the throttling rule and that are terminated.
MATCH_HIT_CACHE	The number of SQL statements that match the throttling rule and whose template IDs are found in the cache keys.
TOTAL_MATCH	The total number of times for which the throttling rule is matched.
ACTIVE_NODE_COUNT	The number of compute nodes for which SQL throttling is enabled.
MAX_CONCURRENCY_PER_NODE	The maximum number of concurrent SQL statements on each compute node.
WAIT_QUEUE_SIZE_PER_NODE	The maximum length of the waiting queue on each compute node.
WAIT_TIMEOUT	The maximum duration for which an SQL statement can be retained in the waiting queue.
FAST_MATCH	Specifies whether to enable cache to accelerate the matching process.
SQL_TYPE	The type of SQL statements.
USER	The username.
TABLE	The name of the database.
KEYWORDS	The list of keywords.
TEMPLATEID	The ID of the SQL template.
CREATED_TIME	The local time when the throttling rule was created. The time is in the `yyyy-MM-dd HH:mm:ss` format.

View all throttling rules

SHOW CCL_RULES;

Delete a throttling rule

Note

After a throttling rule is deleted, the throttling rule immediately becomes invalid and the queued SQL statements can be executed as expected.

Delete a specific throttling rule
To delete specific throttling rules, specify their names in the following statement and separate the names with commas (,).
Syntax:
```
DROP CCL_RULE [ IF EXISTS ] `ccl_rule_name1` [, `ccl_rule_name2`, ...]
```
Example:
```
DROP CCL_RULE IF EXISTS `selectrulereal`;
```
Delete all throttling rules:
```
CLEAR CCL_RULES;
```

Slow SQL throttling trigger

Enable the slow SQL throttling trigger

The slow SQL throttling trigger is a mechanism designed to automatically create throttling rules for SQL queries that exceed a specified execution time threshold.

Syntax:

SLOW_SQL_CCL GO ['SQL_TYPE'[MAX_CONCURRENCY] [SLOW_SQL_TIME] [MAX_CCL_RULE]]

Example:

SLOW_SQL_CCL GO 'SELECT' 2 5 2;

Note

Parameters:
- SQL_TYPE: the SQL query type. Valid values: ALL, SELECT, UPDATE, and INSERT. Default value: SELECT.
- MAX_CONCURRENCY: the maximum number of concurrent SQL queries allowed for the automatically created throttling rule. By default, the value is half the number of CPU cores.
- SLOW_SQL_TIME: the threshold execution time of slow SQL queries. Default value: the value of the system parameter SLOW_SQL_TIME. Unit: milliseconds.
- MAX_CCL_RULE: the maximum number of throttling rules that can be created. Default value: 1000.
How the slow SQL throttling trigger works:
1. Scans all sessions across the instance to identify the template IDs of all slow SQL queries of the specified type.
2. Creates a slow SQL throttling trigger. The trigger is named in the _SYSTEM_SLOW_SQL_CCL_TRIGGER_{SQL_TYPE}_format.
3. Transmits the template IDs of all slow SQL queries to the trigger. The trigger then creates a throttling rule for the identified template IDs
4. Terminates all SQL queries with the identified template IDs that exceed the MAX_CONCURRENCY limit.

View the slow SQL throttling trigger

SLOW_SQL_CCL SHOW;

Fields in the result:

Field	value
NO.	1
SCHEMA	__cdc__
TEMPLATE_ID	1394f5db
SQL	SELECT * FROM test
RULE_NAME	AUTO__SYSTEM_SLOW_SQL_CCL_TRIGGER_SELECT____cdc___1394f5db_9e1c7f6d
RUNNING	0
WAITING	0
KILLED	0
TOTAL_MATCH	0
ACTIVE_NODE_COUNT	4
MAX_CONCURRENCY_PER_NODE	2
CREATED_TIME	2025-04-23 00:00:00

Adjust the slow SQL threshold

You can adjust the slow SQL threshold by using one of the following methods, listed in descending order of priority:

Specify SLOW_SQL_TIME in the SLOW_SQL_CCL GO statement.
Specify SLOW_SQL_TIME before executing SLOW_SQL_CCL GO.
Example:
```
SET @slow_sql_time=2000;
SLOW_SQL_CCL GO;
```
Specify SLOW_SQL_TIME in the PolarDB-X console.

Disable the slow SQL throttling trigger

When you disable the slow SQL throttling trigger created by using the SLOW_SQL_CCL GO statement, the throttling rules created by the trigger are also deleted.

SLOW_SQL_CCL BACK;