How to throttle slow SQL statements-PolarDB(PolarDB)-阿里云帮助中心

This topic describes how to throttle slow SQL statements.

When a large number of slow SQL statements are found in database sessions or slow logs, consuming excessive database resources, and one or more monitoring metrics such as active sessions, CPU utilization, IOPS, and memory usage are at high levels. After analysis, these slow SQL statements are not part of core business logic but are poorly optimized SQL statements. To ensure the stable operation of core services, you need to throttle them.

For the related throttling syntax, see SQL throttling.

Operational steps for SQL throttling

Identify slow SQL statements on the Instance sessions page or by using the following statement:

select *
  from information_schema.processlist
 where COMMAND!= 'SLEEP'
   and TIME>= 1000
 order by TIME DESC;

Analyze slow SQL statements. For more information, see How to analyze and optimize slow SQL statements.
Create throttling rules by using SQL commands or by using the SQL throttling feature on the Instance sessions page.
Evaluate the effectiveness of throttling rules from the following aspects:
- Recovery of monitoring metrics;
- Feedback from the business side;
- show ccl_rules to view the statistics of each throttling rule;
- Review session and SQL logs.
Create indexes, modify SQL statements, or add resources.
Use DROP CCL_RULE or CLEAR CCL_RULES to disable throttling rules.

The following cases illustrate how to throttle slow SQL statements. You can refer to the throttling rules in the cases and modify them as needed.

Case 1: Slow SQL statements belong to the same SQL template

A DBA receives an alert that a database resource metric is at a high level. After reviewing the database slow logs and sessions, the following slow SQL statements are found:

+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                         | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where `o_id` > 12 | 65c92c88        |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 10 | 65c92c88        |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 23 | 65c92c88        |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 25 | 65c92c88        |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where `o_id` > 27 | 65c92c88        |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+

These slow SQL statements belong to the same SQL template (template ID: 65c92c88):

select * from bmsql_oorder where `o_id` > ?

The table bmsql_oorder contains a large amount of data, and there is no index on the column o_id. This is clearly an unoptimized SQL statement that consumes database resources and affects the normal execution of other important SQL statements. This is an ideal scenario for using template ID-based SQL throttling.

Create throttling rules

If the SQL statements of this template should not be executed at the current time but should be executed during off-peak hours, create a throttling rule to block their execution:

CREATE CCL_RULE `KILL_CCL`       -- The throttling rule is named KILL_CCL
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY TEMPLATE '65c92c88'  -- Matches the template ID
  WITH MAX_CONCURRENCY = 0;      -- Sets the per-node concurrency to 0, blocking all matching SQL statements

When the client executes such SQL statements again, the following error message is returned:

ERROR 3009 (HY000): [13172dbaf2801000][10.93.159.222:3029][analy_db]Exceeding the max concurrency 0 per node of ccl rule KILL_CCL

If you want to allow a small number of these SQL statements to execute without consuming all database resources, set a concurrency limit when creating the throttling rule:

CREATE CCL_RULE `KILL_CCL_2`       -- The throttling rule is named KILL_CCL_2
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY TEMPLATE '65c92c88'  -- Matches the template ID
  WITH MAX_CONCURRENCY = 2;      -- Allows up to two concurrent executions of such SQL statements per node

You can also use the SQL throttling feature on the Instance sessions page. Perform the following operations:

On the SQL Throttling creation page, set the Throttling Duration to 5 minutes, fill in the remaining parameters based on the values in the SQL command described above, and then click Create.

If you want the SQL statements of this template to execute slowly rather than fail, you can configure a wait queue and wait timeout (default: 600 seconds):
```
CREATE CCL_RULE `QUEUE_CCL_2`       -- The throttling rule is named QUEUE_CCL_2
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY TEMPLATE '65c92c88'  -- Matches the template ID
  WITH MAX_CONCURRENCY = 2, WAIT_QUEUE_SIZE=20, WAIT_TIMEOUT=500; -- Per-node concurrency is 2, per-node wait queue size is 20, wait timeout is 500 seconds
```
After creating the rule, you can run the show ccl_rules command to query the actual effectiveness of each throttling rule, such as the number of SQL statements currently matching and executing under a given rule, the number of SQL statements that resulted in errors due to throttling, and the total number of successful matches. If you want to release throttled SQL statements — for example, after adding an index that improves the execution efficiency of a throttled SQL statement — you can use the drop ccl_rule command to disable a specific throttling rule, or use clear ccl_rules to disable all throttling rules.

The SQL statements above can also be throttled by keyword. Extract the keywords from the SQL statement to get the following keyword list:
- select
- from
- bmsql_oorder
- where
- `o_id`
Create a throttling rule:
```
CREATE CCL_RULE `KILL_CCL`       -- The throttling rule is named KILL_CCL
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY KEYWORD('select','from','bmsql_oorder','where','`o_id`')  -- Matches keywords
  WITH MAX_CONCURRENCY = 0;      -- Sets the per-node concurrency to 0, blocking all matching SQL statements
```
When a template ID is available (from SQL logs, the EXPLAIN command, or sessions), using template ID-based throttling is recommended for greater precision.

You can also use the SQL throttling feature on the Instance sessions page. The operations are as follows:

On the SQL Throttling page, on the Create tab, configure the following parameters:
- Throttling Mode: Select By Keyword
- SQL Type: Select SELECT
- Max Concurrency: Set to 1
- Throttling Duration: Set to 5 minutes
- SQL Keywords: Connect keywords with ~ and enter them, such as select~from~bmsql_oorder~where~o_id
After the configuration is complete, click Create.

Case 2: All slow SQL statements are the same SQL statement

A DBA receives an alert that a database resource metric is at a high level. After reviewing the database slow logs and sessions, the following slow SQL statements are found:

+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                              | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547     | analy_db           | Query   |   43 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+

The table bmsql_oorder contains many records matching the condition o_carrier_id = 2, leading to slow SQL statements. Using template ID-based throttling would affect SQL statements where o_carrier_id is not 2. Using keyword-based throttling would affect similar normal SQL statements such as the following:

select * from bmsql_oorder where o_carrier_id = 2 limit 1;
select * from bmsql_oorder where o_carrier_id = 2 and o_c_id = 1;

To throttle a specific SQL statement, combine the template ID and keyword approach by creating the following throttling rule:

CREATE CCL_RULE `KILL_CCL`       -- The throttling rule is named KILL_CCL
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY TEMPLATE '438b00e4'  -- Matches template ID 438b00e4
  FILTER BY KEYWORD('o_carrier_id','2')  -- Matches parameter keywords
  WITH MAX_CONCURRENCY = 0;      -- Sets the per-node concurrency to 0, blocking all matching SQL statements

If the PolarDB-X version is 5.4.11 or later and the SQL statement is not executed in prepare mode, you can also use the following advanced syntax for throttling:

CREATE CCL_RULE `KILL_CCL`       -- The throttling rule is named KILL_CCL
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY QUERY 'select * from bmsql_oorder where o_carrier_id = 2'  -- Matches the SQL statement
  WITH MAX_CONCURRENCY = 0;      -- Sets the per-node concurrency to 0, blocking all matching SQL statements

Case 3: The slow SQL set contains multiple SQL templates

A DBA receives an alert that a database resource metric is at a high level. After reviewing the database slow logs and sessions, the following slow SQL statements are found:

+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID     | USER          | HOST                | DB                 | COMMAND | TIME | STATE | INFO                                              | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830     | analy_db           | Query   |   40 |       | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4        |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517     | analy_db           | Query   |   43 |       | select * from bmsql_order_line where ol_o_id = 2  | 57a572f9        |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527     | analy_db           | Query   |   43 |       | select * from bmsql_new_order where no_w_id = 2   | de6eefdb        |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+

This scenario is more complex. If a clearly efficient SQL statement also becomes a slow SQL statement, this may be caused by network jitter or abnormal service node behavior reducing execution efficiency and generating a large number of slow SQL statements, or it may be that truly inefficient SQL statements have exhausted all resources, causing originally normal SQL statements to become slow as well. The root cause needs to be determined through SQL analysis, which is beyond the scope of this topic. Assuming the slow SQL statements to be throttled have been identified, you can create a throttling rule for each template ID. However, as the number of throttling rules increases, matching efficiency may decrease slightly. When the CN kernel version of PolarDB-X is 5.4.11 or later, we recommend that you use multi-template throttling:

CREATE CCL_RULE `KILL_CCL`       -- The throttling rule is named KILL_CCL
    ON `analy_db`.`*`              -- Matches SQL statements executed on all tables in analy_db
  TO 'userxxxxxxxxx'@'%'         -- Matches SQL statements from the user userxxxxxxxxx
    FOR SELECT                     -- Matches SELECT SQL statements
  FILTER BY TEMPLATE('438b00e4','57a572f9','de6eefdb')  -- If any one of the template IDs is matched, the match is successful
  WITH MAX_CONCURRENCY = 0;      -- Sets the per-node concurrency to 0, blocking all matching SQL statements

If all slow SQL statements in the session are confirmed to be inefficient SQL statements that need to be throttled, and the PolarDB-X version is 5.4.11 or later, you can enable SQL throttling. You can also use the SQL throttling feature on the Instance sessions page. Perform the following operations:

On the SQL Throttling page, on the Create tab, create a throttling rule with the following configuration:

Throttling Mode: Select By Execution Duration
SQL Type: Select SELECT
Max Concurrency: Set to 3
SQL Execution Time >: Set to 5 seconds
Max Throttled SQL IDs: Set to 3
Throttling Duration: Set to 5 minutes (the page prompt: Throttling is an emergency measure. After the issue is resolved, disable the throttling rule promptly)

After completing the configuration, click Create.

Summary

SQL throttling is an emergency measure that can quickly restore database performance when inefficient SQL statements cause efficiency degradation. After throttling such SQL statements, focus on how to optimize them, and disable the throttling rules at an appropriate time.