SQL Concurrency Control

To address issues such as surges in database request traffic, high-resource-consuming statement access, and changes in the SQL access model, PolarDB-X provides node-level SQL throttling to restrict the execution of SQLs that cause the aforementioned problems. This ensures the continuous and stable operation of instances. This article explains how to use the SQL throttling feature.

Creating Throttling Rules

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 | | Mandatory | Description | |----------------------------------------------|------------------------|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Throttling rule matching parameters | ccl_rule_name | Yes | The name of the throttling rule. Note To avoid conflicts with SQL keywords, it is recommended to enclose the rule name with backticks () on both sides. | | Throttling rule matching parameters | ```database.table``` | Yes | The names of the database and table, with support for using an asterisk (*) to match anything. Note To avoid conflicts with SQL keywords, it is recommended to enclose the database and table names with backticks () on both sides. | | Throttling rule matching parameters | '<usename>'@'<host>' | Yes | The account name, where the Host part can use the percent sign (%) to match anything. | | Throttling rule matching parameters | UPDATE | SELECT | INSERT | DELETE | Yes | Type of SQL statement. Currently supported types are UPDATE, SELECT, INSERT, and DELETE. Note Each throttling rule only supports one type of SQL statement.| | Throttling rule matching parameters | [ filter_options ] | Optional | Filtering conditions, supporting the following two types: Keywords (KEYWORD): When viewing throttling rules, the keyword list will be converted into a string format of ["kwd1","kw2","kw3"...] in the query results, supporting up to 512 characters. Note If the keyword is a parameter value in the SQL statement, the matching is case-sensitive. If the keyword is another word in the SQL statement, the matching is case-insensitive. Template (TEMPLATE): The template number is the sql_code value in SQL logs, which is a hexadecimal representation of the hash value of the parameterized SQL statement (SQL template). You can view the template number using the SHOW FULL PROCESSLIST and EXPLAIN commands. | | Throttling rule behavior control parameters | with_options | Yes | The WITH option supports the following four parameters to control the behavior of the throttling rule: MAX_CONCURRENCY: The maximum concurrency for SQL statements that match this throttling rule. Exceeding this limit will place them into a waiting queue. The range of values is [0~2^31 - 1], with the default value being 0. WAIT_QUEUE_SIZE: The maximum length of the waiting queue when concurrency is exceeded. If the queue length surpasses this value, the SQL statements will return an error. Statements in the queue still occupy thread resources, and having too many queued can also potentially exhaust memory. The range of values is [0~2^31 - 1], with the default value being 0. WAIT_TIMEOUT: The longest waiting time for SQL statements in the waiting queue. If this waiting time is exceeded, the SQL statements will return an error. The range of values is [0~2^31 - 1], in seconds, with the default value being 600. FAST_MATCH: Whether to enable caching to expedite matching. Once enabled, PolarDB-X 2.0 will cache the template number as part of the Cache key and the matching result as the value to speed up the matching process. The range of values is 0 for off and 1 for on, with the default setting being on. Note: When creating a throttling rule, at least one of the above four behavior control parameters must be specified. When MAX_CONCURRENCY has the default value (0), it may cause all matched SQL statements to return an error. In such a case, it is advised to explicitly set this parameter to a non-zero value. PolarDB-X 2.0 is a distributed cloud-native database with the computing layer composed of multiple nodes, hence the sum of the concurrency for each node is the maximum concurrency for the entire instance. In situations of load imbalance, the restricted SQL concurrency across the entire instance may not reach the maximum concurrency. | [Parameter Explanation]

Note: Throttling will only be applied based on the WITH options of the rule if an SQL statement satisfies all the matching parameter conditions.

Throttling Results

After an SQL matches a rule, depending on the parameters configured in the WITH options of the throttling rule, there can be several outcomes:
- RUN (Runnable): If the concurrency has not yet reached the maximum concurrency (i.e., the value of the MAX_CONCURRENCY parameter), the SQL will execute normally and will not be throttled.
- WAIT (Waiting): If concurrency has reached the maximum but the waiting queue length has not yet reached its maximum (i.e., the value of the WAIT_QUEUE_SIZE parameter), the SQL enters a waiting state until it either enters the runnable (RUN) state or hits the wait timeout (WAIT_TIMEOUT) state.
You can view SQL statements waiting due to matching throttling rules with the following command:
```
  mysql> SHOW FULL PROCESSLIST;
```

An example of the returned result is as follows:

```sql
+----+---------------+-----------------+----------+-------------------------------+------+-------+-----------------------+-----------------+
| 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)
```



From the above query results, it can be seen that the SQL statement select 1 is in a waiting (Waiting) state due to the throttling rule selectrulereal.

WAIT_TIMEOUT (Wait Timeout)

After an SQL statement enters the waiting state, if the waiting time exceeds the longest waiting time (i.e., the value of the WAIT_TIMEOUT parameter), the statement will return an error.

For example, if a throttling rule is set with a maximum waiting time of 10 seconds, executing SELECT sleep(11) will result in an error due to waiting timeout, as shown in the following example:
```
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 (Terminated)

When both the concurrency and the waiting queue length have reached their maximum values, the client will receive an error message indicating that the maximum concurrency has been exceeded. The error message will include the name of the matched throttling rule.

For example, if SELECT 1; is executed after both concurrency and waiting queue length have reached their maximum values, the following error will occur:
```
ERROR 3009 (HY000): [11a07c4425c00000][**.***.***.**:8527][polardbx]Exceeding the max concurrency 0 of ccl rule selectrulereal
```
The above result indicates that the SQL statement SELECT 1; failed to execute due to exceeding the maximum concurrency set by the throttling rule selectrulereal.

Example

Suppose you need to create a rule named select rule to limit SQL statements with the keyword ccl matched, initiated by the user 'ccltest'@'%', and performing a SELECT operation on any table, while setting the maximum concurrency to 10.

The rule creation statement is as follows:

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

Viewing Throttling Rules

Syntax
- Use the following command to view the specific rules:
```
SHOW CCL_RULE `ccl_rule_name1` [, `ccl_rule_name2` ]
```
- Use the following command to view all the throttling rules in the current database:
```
SHOW CCL_RULES
```
  The returned result is as follows:
```
*************************** 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
```
| Parameter | Description | |--------------------------|---------------------------------------------------------------------------------------| | NO. | Match priority, the smaller the number, the higher the priority. | | RULE_NAME | The name of the throttling rule. | | RUNNING | The number of SQL statements that match the throttling rule and are running normally. | | WAITING | The number of queries that match the throttling rule and are in the waiting queue. | | KILLED | The number of SQL statements that match the throttling rule and have been killed. | | MATCH_HIT_CACHE | The number of SQL statements that match the throttling rule and hit the cache. | | TOTAL_MATCH | The total number of times that the throttling rule was matched. | | ACTIVE_NODE_COUNT | The number of compute nodes that have SQL throttling enabled. | | MAX_CONCURRENCY_PER_NODE | The concurrency on each compute node. | | WAIT_QUEUE_SIZE_PER_NODE | The maximum length of the waiting queue on each compute node. | | WAIT_TIMEOUT | The maximum waiting time for SQL statements in the waiting queue. | | FAST_MATCH | Whether to enable caching to speed up match performance. | | SQL_TYPE | The type of SQL statements. | | USER | The username. | | TABLE | The database table. | | KEYWORDS | The list of keywords. | | TEMPLATEID | The identifier for the SQL template. | | CREATED_TIME | Creation time (local time), in the format yyyy-MM-dd HH:mm:ss. |

Deleting Throttling Rules

Note: Deleted throttling rules will take effect immediately, and SQL statements in the waiting queue under that rule will all be executed normally.

To delete specified throttling rules:

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

To delete all throttling rules:
```
CLEAR CCL_RULES
```

Throttling Slow SQL

One-click enabling by statement type, by default for SELECT type. The command of the same statement type will update the setting. The syntax structure is as follows:
```
SLOW_SQL_CCL GO [ SQL_TYPE [MAX_CONCURRENCY] [SLOW_SQL_TIME] [MAX_CCL_RULE]]
```
- SQL_TYPE values: ALL, SELECT, UPDATE, INSERT, default is SELECT.
- MAX_CONCURRENCY: default is half the number of CPU cores.
- SLOW_SQL_TIME: default is the value of the system parameter SLOW_SQL_TIME.
- MAX_CCL_RULE: default is 1000.
Actions:
- Traverse all sessions of the entire instance and identify the TemplateId of slow SQL of that statement type.
- Create a throttling trigger for slow SQL, named: SYSTEMSLOW_SQL_CCL_TRIGGER{SQL_TYPE}.
- Pass the TemplateId of slow SQL to the throttling trigger, which creates throttling rules.
- Kill all slow TemplateId queries of that statement type.
One-click disabling deletes the throttling triggers created by SLOW_SQL_CCL, along with the throttling rules created by the trigger. The syntax structure is as follows:
```
SLOW_SQL_CCL BACK
```
To view the throttling status. The syntax structure is as follows:
```
SLOW_SQL_CCL SHOW
```
An inner join in plan_cache and ccl_rules with the TemplateId as the join key.
How to intervene in the threshold for slow SQL?
- Configure the syntax in SLOW_SQL_CCL GO.
- Before enabling SQL throttling with one click, set the user variable slow_sql_time as follows:
```
set @slow_sql_time=2000;
slow_sql_ccl go;
```

Note: The later configuration method will override the earlier one.

SQL Concurrency Control

SQL Concurrency Control

Creating Throttling Rules

Viewing Throttling Rules

Deleting Throttling Rules

Throttling Slow SQL

results matching ""

No results matching ""