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 can help 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:
  • MySQL 5.6, MySQL 5.7, or MySQL 8.0 in ApsaraDB RDS for MySQL
  • MySQL 5.6, MySQL 5.7, or MySQL 8.0 in PolarDB for MySQL
  • 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.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. 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 in the Instance Sessions section.
    ss
  6. In the SQL Throttling dialog box, configure the following parameters.
    aa
    Parameter Description
    Throttling Mode Select the SQL throttling mode. For more information, see Throttling modes.
    SQL Type Select the type of the SQL requests to throttle. Valid values: SELECT, UPDATE, and DELETE.
    Maximum Concurrency The maximum number of concurrent SQL requests. Throttling is triggered when the number of SQL requests that contain specific keywords reaches the maximum value. The minimum value of this parameter is 1.
    Throttling Duration The duration during which the SQL throttling configuration takes effect. SQL throttling is an emergency measure. We recommend that you specify the throttling duration based on your business requirements and disable throttling when throttling is no longer needed.
    SQL Keywords The keywords that are used to identify the SQL requests that need to be throttled. If you specify multiple keywords, throttling is triggered only if an SQL request contains all keywords. Separate multiple keywords with tildes (~). The value of the SQL Keywords parameter cannot be set 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 statement 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.
    The database account of the throttled SQL statement The database account that sends SQL requests that are to be throttled.
    Note After the SQL throttling rule is created, different types of database services respond in a different manner to SQL requests that contain all the specified SQL keywords.
    • For an ApsaraDB RDS for MySQL instance that runs MySQL 5.6 or MySQL 5.7 or a PolarDB for MySQL cluster that runs MySQL 5.6, the corresponding database system returns 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. After the number of SQL statements that are in the Concurrency control waiting state exceeds the threshold specified by the ccl_max_waiting_count parameter if the version of your instance supports the ccl_max_waiting_count parameter, the corresponding database system returns the following error code:
      • 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 value of the ccl_max_waiting_count parameter is the default value 0, all throttled SQL statements are in the Concurrency control waiting state and no error 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.

  7. Click Create.
  8. After the SQL throttling rule is created, you can view the status of the rule in the list of throttling rules.
    aa
  9. If you want to disable or modify a throttling rule before the specified throttling duration expires, click Disable or Edit in the Actions column.

Throttling modes

DAS provides three modes to throttle SQL requests.

Parameter Description Supported databases
Throttled by Keywords Throttles the SQL requests that contain the specified keywords.
  • RDS MySQL
  • PolarDB MySQL
  • PolarDB-X 2.0
Throttle by SQL Template ID Throttles the SQL requests that apply the SQL templates of the specified IDs. The SQL template IDs can be obtained from SQL logs, sessions, and the results 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
Throttle by Execution Duration Throttles SQL requests by execution duration. If the execution duration of an SQL request of the specified type exceeds the specified threshold value, the system fetches the ID of the SQL template used by the SQL request and adds the ID to the throttling rule. Then, SQL requests 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

Related API operations