Database Autonomy Service (DAS) provides the automatic SQL throttling feature. If the conditions that you configure are met, automatic SQL throttling is triggered.

Prerequisites

  • The database instance that you want to manage is one of the following types:
    • ApsaraDB RDS for MySQL High-availability Edition or Enterprise Edition that runs MySQL 5.6, MySQL 5.7, or MySQL 8.0
    • PolarDB for MySQL Cluster Edition that runs MySQL 5.6, MySQL 5.7, or MySQL 8.0, and PolarDB for MySQL X-Engine that runs MySQL 8.0
    • ApsaraDB MyBase for MySQL High-availability Edition that runs MySQL 5.6, MySQL 5.7, or MySQL 8.0
  • Enable Autonomy Service is turned on for the database instance. For more information, see Autonomy center.

Background information

For more information about the technical principles, see Automatic SQL throttling.

Applicable scenarios

The automatic SQL throttling feature is suitable for the following scenarios:
  • Traffic: Cache penetration exceptions or suspicious requests cause the surge of concurrent SQL queries of a specific type.
  • Data: A large number of SQL queries are submitted from the same source. For example, a user of an online shopping platform places a large number of orders. In this case, a large number of SQL queries that are related to the user account are submitted. The SQL queries consume a large amount of database resources on your RDS instance.
  • SQL statements: A large number of SQL statements for which no indexes are created are executed. This affects your workloads.

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. Then, the instance details page appears.
  4. In the left-side navigation pane of the instance details page, click Autonomy Center.
  5. In the upper-right corner of the Autonomy Center page, click Autonomy Service Settings.
    Autonomy service settings
  6. On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Throttling and configure the parameters described in the following table to specify the conditions for triggering automatic throttling.
    2
    ParameterDescription
    CPU UtilizationSpecify a value that is greater than or equal to 70.
    Number of Active Sessions
    • Specify an integer that is greater than or equal to 16 when CPU Utilization and Number of Active Sessions are in the OR relationship.
    • Specify an integer that is greater than or equal to 2 when CPU Utilization and Number of Active Sessions are in the AND relationship.
    Available Time RangeSpecify the period of time during which automatic SQL throttling is performed.
    Maximum Throttling DurationSpecify the maximum duration for SQL throttling.

    If you set the Maximum Throttling Duration parameter to 0, automatic SQL throttling is triggered when your RDS instance meets the conditions that you specify. However, automatic SQL throttling immediately becomes invalid.

    DurationIf the period of time during which the values of the CPU Utilization and Number of Active Sessions parameters meet the specified conditions exceeds the value of this parameter, automatic SQL throttling is triggered.
    Note For example, you can specify the following conditions: The CPU utilization exceeds 80%, the number of active sessions is greater than 64, and the abnormal situation lasts for more than 2 minutes. If these conditions are met within a specified throttling window, automatic SQL throttling is triggered, and DAS starts to monitor the performance of the RDS instance.
    • If the issue persists after throttling, DAS automatically rolls back the automatic SQL throttling operation.
    • The duration of automatic SQL throttling does not exceed the specified maximum throttling duration.
  7. In the message that appears, click OK.

    You can view throttled SQL statements of the database instance in the Instance Sessions section. For more information, see View the history of automatic SQL throttling.

    Note After you create an automatic SQL throttling rule for your RDS instance, if the application that is connected to the RDS instance calls a throttled SQL statement, ApsaraDB RDS returns the following information:
    • 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 MySQL 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 ccl_max_waiting_count, the corresponding database system returns the "Concurrency control waiting count exceed max waiting count" error message together with 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 ccl_max_waiting_count is set to the default value 0, all throttled SQL statements are in the Concurrency control waiting state and no error is returned. If you use DAS to implement SQL throttling and set the ccl_max_waiting_count parameter to 0, DAS changes the value of this parameter to the default value defined in DAS. If you set the ccl_max_waiting_count parameter to a value that is greater than 0, DAS uses the value that you specify to implement SQL throttling.

  8. Optional:In the Autonomous Function Management panel, click the Event Subscription Settings tab, turn on Enable Subscription Service and configure notifications of automatic SQL throttling events.
    When automatic SQL throttling is triggered, DAS sends notifications for warning events. To receive the notifications, you can turn on Enable Subscription Service and then configure the following parameters.
    ParameterDescription
    UrgencyThe lowest level of events for which DAS sends notifications. In this step, select Warning
    Select Contacts/Contact GroupsThe contacts and contact groups to which DAS sends notifications. Select one or more contacts or contact groups in the Existing Contacts/Contact Groups section and click the 042701 icon to add the selected contacts or contact groups to the Selected Contacts/Contact Groups section.
    Notification MethodThe method that is used by DAS to send notifications. Valid values:
    • SMS
    • DingTalk Chatbot
    • SMS + DingTalk Chatbot
    • SMS + Email
    Notification LanguageOnly Chinese is supported.
    Minimum Notification IntervalThe minimum interval between two notifications for the same event. If an event continues after DAS sends a notification of the event, DAS resends the notification only after this interval ends.
    Event notification

View the history of automatic SQL throttling

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, click Instance Sessions.
  5. On the Instance Sessions page, you can perform the following operations:
    ss
    • Click SQL Throttling. In the dialog box that appears, you can perform the following operations:
      • Click the Running tab to view throttling rules that are being executed.
      • Click the Complete tab, and then select a time range to view the throttling history within the specified time range.
      SQL throttling
    • If you select Kill Abnormal SQL Statements in Execution when you configure parameters for automatic SQL throttling, you can click End Session History to view ended sessions.

Related API operations

Best practices