SQL throttling

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',...) ]

Match parameters

These parameters determine which SQL statements the rule targets.

Filter options

• KEYWORD -- Matches SQL statements that contain the specified keywords. Keywords appear as a JSON string ["kwd1","kw2","kw3"...] in query results. The string has a maximum of 512 characters.

  • If a keyword matches a parameter value: matching is case-sensitive.

  • If a keyword matches any other part of the statement: matching is case-insensitive.

• TEMPLATE -- The SQL template ID: a 16-bit hexadecimal hash of the parameterized SQL statement. This corresponds to the sql_code field in SQL logs. Run SHOW FULL PROCESSLIST or EXPLAIN to find the template ID.

WITH clause parameters

The WITH clause controls how matched statements are throttled. Specify at least one parameter.

Example

Create a rule named selectrule that limits SELECT statements from the ccltest user (any host) containing the keyword cclmatched to a maximum concurrency of 10:

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

After this rule takes effect, SELECT statements from the ccltest account that contain the keyword cclmatched are limited to 10 concurrent executions.

RUN

If concurrent matching statements have not reached MAX_CONCURRENCY, the statement runs normally.

WAIT

If concurrency has reached MAX_CONCURRENCY but the queue has not reached WAIT_QUEUE_SIZE, the statement enters the queue. It transitions to RUN when a slot opens, or to WAIT_TIMEOUT if the wait time is exceeded.

To view queued statements, run:

SHOW FULL PROCESSLIST;

Sample output:

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

In this output, select 1 matches the selectrulereal rule and is waiting in the queue.

WAIT_TIMEOUT

A queued statement that exceeds the WAIT_TIMEOUT duration fails with an error.

For example, with WAIT_TIMEOUT set to 10 seconds:

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 both MAX_CONCURRENCY and WAIT_QUEUE_SIZE limits are reached, the statement is rejected immediately.

For example:

ERROR 3009 (HY000): [11a07c4425c00000][**.***.***.**:8527][polardbx]Exceeding the max concurrency 0 of ccl rule selectrulereal

Output fields

Query all rules

SHOW CCL_RULES;

Enable the trigger

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

Example:

SLOW_SQL_CCL GO 'SELECT' 2 5 2;

How the trigger works

1. Scans all sessions across the instance to identify template IDs of slow SQL statements matching the specified type.

2. Creates a trigger named _SYSTEM_SLOW_SQL_CCL_TRIGGER_{SQL_TYPE}_.

3. Passes the template IDs to the trigger, which creates throttling rules for the identified templates.

4. Terminates matching SQL statements that exceed the MAX_CONCURRENCY limit.

View the trigger

SLOW_SQL_CCL SHOW;

Sample output:

Adjust the slow SQL threshold

Three methods are available, listed from highest to lowest priority:

1. Specify SLOW_SQL_TIME directly in the SLOW_SQL_CCL GO statement.

2. Set @slow_sql_time before running SLOW_SQL_CCL GO:

   SET @slow_sql_time=2000;
   SLOW_SQL_CCL GO;

3. Configure SLOW_SQL_TIME in the PolarDB-X console.

Disable the trigger

Disabling the trigger also deletes all throttling rules the trigger created.

SLOW_SQL_CCL BACK;