Database Autonomy Service:SQL throttling

Throttling messages

After an SQL throttling rule is created, if the application uses an SQL statement that contains all the specified keywords or matches the template throttling rule, the database system returns corresponding messages based on the instance version when throttling takes effect.

Scenarios

Throttling modes

Procedure

1. Log on to the DAS console.

2. In the left-side navigation pane, choose Intelligent O&M Center > Instance Monitoring.

3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

4. In the left-side navigation pane, click Instance Sessions.

5. On the Session Management tab, click SQL Throttling.

6. In the panel that appears, click Create Throttling Rule.

7. In the Create Throttling Rule dialog box, configure the following parameters based on the database engine.

ApsaraDB RDS for MySQL

Parameter	Description
Throttling Mode	The mode that you want to use to throttle SQL statements. Select a throttling mode based on your business requirements.
SQL Type	The type of SQL statements that you want to throttle. Valid values: SELECT, UPDATE, DELETE, and INSERT. Note This parameter is required when Throttling Mode is set to Throttle by Keyword. The INSERT SQL statements can be throttled on one of the following database engines: ApsaraDB RDS for MySQL 8.0 PolarDB for MySQL 5.7 and 8.0 PolarDB-X 2.0 SQL statements that are in the INSERT...SELECT... format cannot be throttled.
Database	The database in which SQL statements are throttled. Note This parameter is required when Throttling Mode is set to Throttle by SQL Template.
Maximum Concurrency	The maximum number of concurrent SQL statements. The minimum value of this parameter is 0. Note Throttling is triggered when the number of SQL statements that are concurrently executed and meet the throttling policy reaches the maximum concurrency value.
Throttling Duration	The duration during which the SQL throttling rule takes effect. Important The throttling feature is intended only for emergency use. We recommend that you specify the throttling duration based on your business requirements and disable throttling when it is no longer needed.
SQL Keyword	The keyword that is used to identify the SQL statements that need to be throttled. Note This parameter is required when Throttling Mode is set to Throttle by Keyword. If you specify multiple keywords, throttling is triggered only if an SQL statement contains all of the specified keywords. Multiple keywords are separated by tildes (~) with no sequential restriction. For example, if the original SQL statement to be throttled is SELECT min(id), max(id) FROM task_event WHERE gmt_modified < '2020-06-21' AND begin_time > '2020-07-09' AND source IN (527) AND id >= 15673 AND id <= 8015673, set this parameter to SELECT~min~id~max~id~FROM~task_event~WHERE~gmt_modified~AND~begin_time~AND~source~IN~AND~id~AND~id. Do not set this parameter to only SELECT, INSERT, UPDATE, or DELETE. Keywords are case-sensitive except in some earlier versions of instances.
SQL Sample	The sample of the SQL statement that you want to throttle. Note This parameter is required when Throttling Mode is set to Throttle by SQL Template. For complex SQL statements, DAS optimizes the SQL sample while keeping the SQL template structure unchanged. Therefore, the throttling effect is not affected after optimization. For example, if you enter an SQL sample select name from das where name = 'das' and age = 21 limit 20, DAS converts it to an SQL template select name from das where name = ? and age = ? limit ?, and then rewrittes the template to select name from das where name = 'param0' and age = 'param1' limit 20.

ApsaraDB RDS for PostgreSQL

Parameter	Description
Throttling Mode	The mode that you want to use to throttle SQL statements. Only Throttle by SQL Template is supported.
Database	The database in which SQL statements are throttled.
Search Path	The search path to perform SQL throttling. Note Separate multiple paths with commas (,). Default value: information_schema,public.
Maximum Concurrency	The maximum number of concurrent SQL statements. The minimum value of this parameter is 0. Note Throttling is triggered when the number of SQL statements that are concurrently executed and meet the throttling policy reaches the maximum concurrency value.
Maximum Waiting Queries	The maximum number of waiting SQL statements.
Throttling Duration	The duration during which the SQL throttling rule takes effect. Important The throttling feature is intended only for emergency use. We recommend that you specify the throttling duration based on your business requirements and disable throttling when it is no longer needed.
SQL Sample	The sample of the SQL statement that you want to throttle. Note This parameter is required when Throttling Mode is set to Throttle by SQL Template. For complex SQL statements, DAS optimizes the SQL sample while keeping the SQL template structure unchanged. Therefore, the throttling effect is not affected after optimization. For example, if you enter an SQL sample select name from das where name = 'das' and age = 21 limit 20, DAS converts it to an SQL template select name from das where name = ? and age = ? limit ?, and then rewrittes the template to select name from das where name = 'param0' and age = 'param1' limit 20.

PolarDB for MySQL

Parameter	Description
Throttling Mode	The mode that you want to use to throttle SQL statements. Select a throttling mode based on your business requirements.
SQL Type	The type of SQL statements that you want to throttle. Valid values: SELECT, UPDATE, DELETE, and INSERT. Note The INSERT SQL statements can be throttled on one of the following database engines: ApsaraDB RDS for MySQL 8.0 PolarDB for MySQL 5.7 and 8.0 PolarDB-X 2.0 SQL statements that are in the INSERT...SELECT... format cannot be throttled.
Maximum Concurrency	The maximum number of concurrent SQL statements. The minimum value of this parameter is 0. Note Throttling is triggered when the number of SQL statements that are concurrently executed and meet the throttling policy reaches the maximum concurrency value.
Database	The database in which SQL statements are throttled. Note This parameter is required when Throttling Mode is set to Throttle by SQL Template.
Throttling Duration	The duration during which the SQL throttling rule takes effect. Important The throttling feature is intended only for emergency use. We recommend that you specify the throttling duration based on your business requirements and disable throttling when it is no longer needed.
SQL Keyword	The keyword that is used to identify the SQL statements that need to be throttled. Note This parameter is required when Throttling Mode is set to Throttle by Keyword. If you specify multiple keywords, throttling is triggered only if an SQL statement contains all of the specified keywords. Multiple keywords are separated by tildes (~) with no sequential restriction. For example, if the original SQL statement to be throttled is SELECT min(id), max(id) FROM task_event WHERE gmt_modified < '2020-06-21' AND begin_time > '2020-07-09' AND source IN (527) AND id >= 15673 AND id <= 8015673, set this parameter to SELECT~min~id~max~id~FROM~task_event~WHERE~gmt_modified~AND~begin_time~AND~source~IN~AND~id~AND~id. Do not set this parameter to only SELECT, INSERT, UPDATE, or DELETE. Keywords are case-sensitive except in some earlier versions of instances.
SQL Sample	The sample of the SQL statement that you want to throttle. Note This parameter is required when Throttling Mode is set to Throttle by SQL Template. For complex SQL statements, DAS optimizes the SQL sample while keeping the SQL template structure unchanged. Therefore, the throttling effect is not affected after optimization. For example, if you enter an SQL sample select name from das where name = 'das' and age = 21 limit 20, DAS converts it to an SQL template select name from das where name = ? and age = ? limit ?, and then rewrittes the template to select name from das where name = 'param0' and age = 'param1' limit 20.

PolarDB-X 2.0

Parameter	Description
Throttling Mode	The mode that you want to use to throttle SQL statements. Select a throttling mode based on your business requirements.
SQL Type	The type of SQL statements that you want to throttle. Valid values: SELECT, UPDATE, DELETE, and INSERT. Note The INSERT SQL statements can be throttled on one of the following database engines: ApsaraDB RDS for MySQL 8.0 PolarDB for MySQL 5.7 and 8.0 PolarDB-X 2.0 SQL statements that are in the INSERT...SELECT... format cannot be throttled.
Maximum Concurrency	The maximum number of concurrent SQL statements. The minimum value of this parameter is 0. Note Throttling is triggered when the number of SQL statements that are concurrently executed and meet the throttling policy reaches the maximum concurrency value.
Throttling Duration	The duration during which the SQL throttling rule takes effect. Important The throttling feature is intended only for emergency use. We recommend that you specify the throttling duration based on your business requirements and disable throttling when it is no longer needed.
SQL Keyword	The keyword that is used to identify the SQL statements that need to be throttled. Note This parameter is required when Throttling Mode is set to Throttle by Keyword. If you specify multiple keywords, throttling is triggered only if an SQL statement contains all of the specified keywords. Multiple keywords are separated by tildes (~) with no sequential restriction. For example, if the original SQL statement to be throttled is SELECT min(id), max(id) FROM task_event WHERE gmt_modified < '2020-06-21' AND begin_time > '2020-07-09' AND source IN (527) AND id >= 15673 AND id <= 8015673, set this parameter to SELECT~min~id~max~id~FROM~task_event~WHERE~gmt_modified~AND~begin_time~AND~source~IN~AND~id~AND~id. Do not set this parameter to only SELECT, INSERT, UPDATE, or DELETE. Keywords are case-sensitive except in some earlier versions of instances.
SQL Template ID	The ID of an SQL template used by an SQL statement that you want to throttle. Separate multiple IDs with commas (,). Note This parameter is required when the database engine is PolarDB-X Enterprise Edition and Throttling Mode is set to Throttle by SQL Template.
Minimum SQL Execution Duration	The threshold for the SQL execution time. When the execution time of an SQL statement exceeds this threshold, DAS adds the ID of the SQL template to which this SQL statement belongs to the throttling rule. Note This parameter is required when the instance is a PolarDB-X Enterprise Edition instance and Throttling Mode is set to Throttle by Execution Duration.
Maximum Number of Throttled SQL IDs	The maximum number of SQL template IDs that can be throttled by execution duration. Once this threshold is reached, DAS stops creating throttling rules for other SQL statements whose execution duration threshold is reached. Note This parameter is required when the instance is a PolarDB-X Enterprise Edition instance and Throttling Mode is set to Throttle by Execution Duration.
Database Account with Throttled Queries	The database account to which the throttled SQL belongs. Note This parameter is required when Throttling Mode is set to Throttle by Keyword or Throttle by SQL Template.

8. Click Submit.

   After the SQL throttling rule is created, you can view the information about the rule on the Running tab. The following table describes the parameters.

   Note

   The parameters may vary slightly based on the database engine and throttling mode.

   Parameter	Description
   Throttling Mode	The mode that you want to use to throttle SQL statements.
   Throttling Rule	Throttle by Keyword: the configured throttling keyword. Throttle by SQL Template: the template ID corresponding to the throttled sample. Throttle by Execution Duration: the configured execution duration threshold and the number of SQL statements throttled by template.
   Maximum Concurrency	The maximum number of concurrent SQL statements.
   Maximum Waiting Queries	The maximum number of waiting SQL statements configured in the throttling rule. Note This parameter is displayed when the database instance is ApsaraDB RDS for PostgreSQL and Throttling Mode is set to Throttle by SQL Template.
   Throttling Duration (min)	The duration during which the SQL throttling rule takes effect.
   Start Time	The time when the throttling rule takes effect.
   Remaining Time (s)	The remaining time before the throttling rule expires.
   Sample SQL	The sample of the SQL statement that you want to throttle. Note This parameter is displayed when Throttling Mode is set to Throttle by SQL Template.
   Throttling Template ID	The ID of an SQL template used by the sample of the SQL statement that you want to throttle. Note This parameter is displayed when Throttling Mode is set to Throttle by SQL Template.
   Status	The running status of the enabled throttling rule.
   Database	The database in which SQL statements are throttled. Note This parameter is displayed when Throttling Mode is set to Throttle by SQL Template.
   Search Path	The search path to perform SQL throttling. Note This parameter is displayed when the database instance is ApsaraDB RDS for PostgreSQL and Throttling Mode is set to Throttle by SQL Template.
   Matched SQL Query	The number of SQL statements matched by the throttling rule. Note This parameter is displayed when Throttling Mode is set to Throttle by SQL Template.
   Waiting SQL Query	The number of waiting SQL statements in the throttling rule. Note This parameter is displayed when Throttling Mode is set to Throttle by SQL Template.
   Actions	The operations to disable or modify the throttling rule.

References

• Automatic SQL throttling

• Concurrency control feature for PolarDB for MySQL clusters

• Concurrency control feature for ApsaraDB RDS for MySQL instances

Related API operations