SQL throttling

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 }]
```
| 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. | | Parameters used to identify the SQL statements to be limited for throttling | 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. | | Parameters used to identify the SQL statements to be limited for throttling | '<usename>'@'<host>' | Yes | The username of the account. You can use a percent sign (%) as a wildcard character in the host variable. | | Parameters used to identify the SQL statements to be limited for throttling | UPDATE \| SELECT \| INSERT \| DELETE | Yes | The type of SQL statements. The value can be UPDATE, SELECT, INSERT
Note or DELETE. | | Parameters used to identify the SQL statements to be limited for throttling | [ 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.
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:{#ae3ba44226lip}
```
  +----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
  | 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:{#ae3bf26126i8f}
```
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{SQLTYPE}.
- 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.

SQL throttling

SQL throttling

Create a throttling rule

Query a throttling rule

Delete a throttling rule

Throttle slow SQL queries

results matching ""

No results matching ""