Database Autonomy Service:Automatic SQL throttling

Scenarios

The automatic SQL throttling feature is applicable to 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 in your database instance.

• SQL statements: A large number of SQL statements that do not use indexes are executed. This affects database performance.

Feature description

After you create an automatic SQL throttling rule for your database instance, if an application that is connected to the database instance executes throttled SQL statements, the following information is returned:

• 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. After the number of SQL statements in the Concurrency control waiting state exceeds the threshold specified by the ccl_max_waiting_count parameter if the version of your database instance supports this parameter, the corresponding database system returns one of the following error codes:

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

For more information about automatic SQL throttling, see Automatic SQL throttling.

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 navigation pane, click Autonomy Center.

5. In the upper-right corner of the page that appears, click 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 that are described in the following table to specify the conditions for triggering automatic SQL throttling.

   

   Parameter	Description
   CPU Utilization Threshold	The threshold for CPU utilization. Set the parameter to a value that is greater than or equal to 70.
   Active Session Threshold	The threshold for the number of active sessions. Specify an integer that is greater than or equal to 16 if the threshold for CPU utilization and the threshold for the number of active sessions are evaluated by using the OR operator. Specify an integer that is greater than or equal to 2 if the threshold for CPU utilization and the threshold for the number of active sessions are evaluated by using the AND operator.
   Available Time Range	The period of time during which automatic SQL throttling is performed.
   Maximum Throttling Duration	The maximum duration for SQL throttling. If you set the Maximum Throttling Duration parameter to 0, automatic SQL throttling is triggered when your database instance meets the conditions that you specify. However, automatic SQL throttling immediately becomes invalid.
   Duration Threshold	The threshold for the period of time during which the CPU utilization and number of active sessions meet the specified conditions. If the specified threshold is exceeded, 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 specific throttling window, automatic SQL throttling is triggered, and DAS starts to monitor the performance of your database instance.

   • If the issue persists after automatic SQL throttling is performed, DAS automatically rolls back the automatic SQL throttling operation.

   • The duration of automatic SQL throttling does not exceed the specified maximum throttling duration.

7. Click OK.

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

8. Optional. Click the Event Subscription Settings tab in the Autonomous Function Management panel to configure the notification rules for 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 configure the parameters. For more information, see Event subscription.

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, 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 Instance Sessions tab, you can perform operations based on your business requirements.

   

   • Click SQL Throttling. In the SQL Throttling dialog box, you can perform the following operations:

     • Click the Running tab to view the throttling rules that are being triggered.

     • Click the Complete tab and select a time range to view the throttling history within the specified time range.

     

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

References

• You can optimize SQL statements to improve database performance, improve query efficiency, and reduce resource consumption.

  • For more information about automatic SQL optimization, see Automatic SQL optimization.

  • For more information about manual SQL optimization, see SQL optimization.

• If automatic SQL throttling cannot be triggered because the CPU utilization or number of active sessions of an instance does not reach the specified threshold but you want to enable SQL throttling, you can manually configure a rule to throttle SQL statements. For more information, see SQL throttling.

Related API operations