All Products
Search
Document Center

Database Autonomy Service:SQL throttling

Last Updated:Jun 25, 2024

Database Autonomy Service (DAS) provides the SQL throttling feature. The number of database access requests and the number of concurrent SQL requests can be controlled by using the SQL throttling feature. This feature helps ensure the availability of your database service. This topic describes how to use SQL throttling for an ApsaraDB RDS for MySQL instance.

Prerequisites

The database instance for which you want to enable SQL throttling runs on one of the following database engines:

  • ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0

  • PolarDB for MySQL 5.6, 5.7, and 8.0

  • PolarDB-X 2.0

Scenarios

Scenario

Description

Sharp rise in the number of concurrent SQL statements of a specific type

Cache penetration or abnormal calls can cause an unexpected rise in the number of concurrent SQL statements.

Existence of SQL statements that cause data skew

If a large amount of data is queried during a promotional event, the overall system slows down.

Lack of index tables

If a large number of SQL statements are executed on tables that are not indexed, the overall system slows down.

Throttling modes

The following table describes the supported throttling modes.

Parameter

Description

Supported database

Throttled by Keywords

Throttles the SQL statements that contain the specified keywords.

  • ApsaraDB RDS for MySQL

  • PolarDB for MySQL

  • PolarDB-X 2.0

Throttled by SQL Template ID

Throttles the SQL statements that use the SQL templates with the specified IDs. The SQL template IDs can be obtained from SQL logs, sessions, and results that are returned after EXPLAIN statements are executed.

Note

The ID of an SQL template is a hexadecimal string that contains eight characters in length.

PolarDB-X 2.0

Throttled by Execution Duration

Throttles SQL statements by execution duration. If the execution duration of an SQL statement of the specified type exceeds the specified threshold value, the system fetches the ID of the SQL template used by the SQL statement and adds the ID to the throttling rule. Then, SQL statements that use the SQL template are executed with the specified concurrency.

Note

You can configure the maximum number of SQL template IDs. After the number of SQL template IDs added to the throttling rule reaches the upper limit, the system stops fetching SQL template IDs.

PolarDB-X 2.0

Procedure

  1. Log on to the DAS console.

  2. In the left-side navigation pane, click 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 pane, click Instance Sessions.

  5. On the Session Management tab, click SQL Throttling in the Instance Sessions section.

    ss

  6. In the SQL Throttling dialog box, configure the parameters described in the following table.

    Parameter

    Description

    Throttling Mode

    The mode that you want to use to throttle SQL statements. Select a throttling mode based on your business requirements. For more information, see the Throttling modes section of this topic.

    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 1.

    Throttling is triggered when the number of SQL statements that are concurrently executed and meet the throttling policy reaches the maximum value.

    Throttling Duration

    The duration during which the SQL throttling rule takes effect. 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 Keywords

    The keywords that are used to identify the SQL statements that need to be throttled.

    Note

    This parameter takes effect only when the Throttling Mode parameter is set to Throttled by Keywords.

    • 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.

    • The SQL Keywords parameter cannot be set only to SELECT, UPDATE, or DELETE. The keywords are case-sensitive for instances except for the instances of some earlier versions.

    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.

    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 takes effect only when the Throttling Mode parameter is set to Throttled by SQL Template ID.

    Executed At

    The threshold value of the execution duration. If the execution duration of an SQL statement exceeds the specified threshold value, the system fetches the ID of the SQL template used by the SQL statement and adds the ID to the throttling rule.

    Note

    This parameter takes effect only when the Throttling Mode parameter is set to Throttled by Execution Duration.

    Maximum Number of Throttled Queries

    The maximum number of IDs of SQL templates used by SQL statements throttled by execution duration. After the number of SQL template IDs added to the throttling rule reaches the upper limit, the system stops fetching IDs of SQL templates used by SQL statements whose execution duration exceeds the specified threshold value.

    Note

    This parameter takes effect only when the Throttling Mode parameter is set to Throttled by Execution Duration.

    Database Account with Throttled Queries

    The database account to which the throttled SQL statement belongs.

    Note

    This parameter takes effect only when the database instance for which you want to enable SQL throttling is a PolarDB-X 2.0 instance and the Throttling Mode parameter is set to Throttled by Keywords or Throttled by SQL Template ID.

    Note

    After the SQL throttling rule is created, different types of database services behave in different manners when they respond to requests sent for SQL statements that contain all specified keywords.

    • For an ApsaraDB RDS for MySQL instance that runs MySQL 5.6 or 5.7 or a PolarDB for MySQL cluster that runs MySQL 5.6, the corresponding database system returns the error code 1317 and the "query execution was interrupted" error message.

    • For an ApsaraDB RDS for MySQL instance that runs MySQL 8.0 or a PolarDB for MySQL cluster that runs MySQL 5.7 or 8.0, the SQL statements enter the Concurrency control waiting state. If the version of your database instance supports the ccl_max_waiting_count parameter, the corresponding database system returns one of the following error codes and the "Concurrency control waiting count exceed max waiting count" error message when the number of SQL statements in the Concurrency control waiting state exceeds the threshold specified by the ccl_max_waiting_count parameter:

      • ERROR 7534 (HY000) for an ApsaraDB RDS for MySQL instance that runs MySQL 8.0

      • ERROR 3277 (HY000) for a PolarDB for MySQL cluster that runs MySQL 5.7

      • ERROR 7533 (HY000) for a PolarDB for MySQL cluster that runs MySQL 8.0

      If the ccl_max_waiting_count parameter is set to the default value 0, all throttled SQL statements are in the Concurrency control waiting state and no error code is returned. When DAS performs SQL throttling, DAS sets the ccl_max_waiting_count parameter to the default value that is defined in DAS if the value of the ccl_max_waiting_count parameter is 0 or directly uses the value of the ccl_max_waiting_count parameter if the value is greater than 0. The default value is 10.

  7. Click Create.

    After the SQL throttling rule is created, you can view the information about the rule on the Running tab.

    To disable or modify a throttling rule, click Disable or Edit in the Actions column.

References

Configure automatic SQL throttling

Related API operations

API operation

Description

EnableSqlConcurrencyControl

Enables SQL throttling.

DisableSqlConcurrencyControl

Disables a throttling rule.

DisableAllSqlConcurrencyControlRules

Disables all throttling rules that are in effect.

GetRunningSqlConcurrencyControlRules

Queries the throttling rules that are in effect.

GetSqlConcurrencyControlRulesHistory

Queries the throttling rules that are in effect or have been triggered.

GetSqlConcurrencyControlKeywordsFromSqlText

Generates throttling keywords for SQL statements.