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.

Create a throttling rule

Syntax

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

filter_options:
    [ FILTER  BY KEYWORD('KEYWORD1', 'KEYWORD2',…) ]
    [ FILTER  BY TEMPLATE('template_id') ]
    
 with_options:
    WITH MAX_CONCURRENCY = value1 [ , WAIT_QUEUE_SIZE = value2 ] [ , WAIT_TIMEOUT = value3 ] [ ,FAST_MATCH = { 0 , 1 }]

Table 1. 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.
	`'<usename>'@'<host>'`	Yes	The username of the account. You can use a percent sign (%) as a wildcard character in the host variable.
	`UPDATE \| SELECT \| INSERT \| DELETE`	Yes	The type of SQL statements. The value can be UPDATE, SELECT, INSERT Note or DELETE.
	`[ 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 When the system matches the keywords with SQL statements, if the keyword is a parameter value in the SQL statement, the matching is case-sensitive. TEMPLATE: The ID of a template is the value of `sql_code` in the SQL log. The value is expressed in hexadecimal notation and is the hash value of the parameterized SQL statement or template. You can execute the SHOW FULL PROCESSLIST and EXPLAIN statements to check the ID of a template.
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, PolarDB-X 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.

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:
```
mysql> SHOW FULL PROCESSLIST;
```
  The following code provides an example of the query 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)
```
  The select 1 SQL statement matches the selectrulereal throttling rule and is waiting to be processed.
- WAIT_TIMEOUT
  For a queued SQL statement, an error is returned if the SQL statement is not executed after the SQL statement stays in the queue for the time 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.180.55: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
```
  The error message indicates that the SQL statement fails to be executed because the maximum concurrency specified for the selectrulereal throttling rule is reached.
Examples
Create a throttling rule named selectrule to throttle the requests sent from user 'ccltest'@'%'. The rule specifies that a SELECT statement that contains the keyword cclmatched is limited for throttling if the number of concurrent requests that contain the keyword cclmatched has reached 10.
To create this rule, execute the following statement:
```
CREATE CCL_RULE IF NOT EXISTS `selectrule` ON *.* TO 'ccltest'@'%'
FOR SELECT
FILTER BY KEYWORD('cclmatched')
WITH MAX_CONCURRENCY=10;
```

Query a throttling rule

Syntax
- Query a specific throttling rule
  The following code shows the syntax:
```
SHOW CCL_RULE `ccl_rule_name1` [, `ccl_rule_name2` ]
```
- Query all throttling rules
  The following code shows the syntax:
```
SHOW CCL_RULES
```

Examples

You can execute the following statement to query all throttling rules that apply to the current database:

mysql> SHOW CCL_RULES \G

The following code provides an example of query results:

*************************** 1. row ***************************
                     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

Table 2. Parameters
Parameter	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.

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

DROP CCL_RULE [ IF EXISTS ] `ccl_rule_name1` [, `ccl_rule_name2`, ...]

Delete all throttling rules
```
CLEAR CCL_RULES
```

Throttle slow SQL queries

Enable slow SQL throttling
Slow SQL throttling is used to optimize SQL statements of a specific type. By default, SELECT statements are used. After you create a throttling rule for SQL statements of a specific type, you can create another throttling rule for SQL statements of this type to overwrite the existing rule. The following code shows the syntax:
```
SLOW_SQL_CCL GO [ SQL_TYPE [MAX_CONCURRENCY] [SLOW_SQL_TIME] [MAX_CCL_RULE]]
```
- The SQL_TYPE parameter can be set to ALL, SELECT, UPDATE, or INSERT. The default value is SELECT.
- The default value of the MAX_CONCURRENCY parameter is half of the number of the CPU cores.
- The default value of the SLOW_SQL_TIME parameter is the value of the SLOW_SQL_TIME system parameter.
- The default value of the MAX_CCL_RULE parameter is 1000.
How slow SQL throttling works
- The system traverses all sessions in the instance and fetches the IDs of templates used by the slow SQL statements of the specified type.
- The system creates a trigger for throttling slow SQL statements. The trigger is named _SYSTEM_SLOW_SQL_CCL_TRIGGER_{SQL_TYPE}_.
- The system passes the IDs of the templates used by the slow SQL statements to the trigger. Then, the trigger creates a throttling rule.
- The system terminates the queries that contain slow SQL statements that match the throttling rule.
Disable slow SQL throttling
If you delete the trigger, the throttling rule created by execute the SLOW_SQL_CCL statement is deleted at the same time. The following code shows the syntax:
```
SLOW_SQL_CCL BACK
```
You can execute the following statement to query the throttling details:
```
SLOW_SQL_CCL SHOW
```
How do I configure the threshold that is used to identify slow SQL statements?
- Configure parameters in the SLOW_SQL_CCL GO statement.
- Before you enable slow SQL throttling, configure the slow_sql_time parameter. The following code provides an example:
```
set @slow_sql_time=2000;
slow_sql_ccl go;
```
- In the console, configure the SLOW_SQL_TIME system parameter.
Note
You can use the preceding methods to configure the threshold that is used to identify slow SQL statements. In this list, the first method has the highest priority. The last method has the lowest priority. If you use more than one method, the method that has a higher priority overwrites the result of the method that has a lower priority.