Database Autonomy Service:Automatic SQL throttling

Use cases

Automatic SQL throttling is ideal for the following scenarios:

• Workload spikes: A sudden increase in the concurrency of specific SQL queries, often caused by events like cache penetration or exceptional application behavior.

• Resource contention: Queries related to specific accounts, such as those with large amounts of order data, consume excessive database resources.

• Inefficient queries: A high volume of queries are executed without a proper index, degrading overall business performance.

How it works

After you create an automatic throttling rule, when an application executes an SQL statement that matches a throttling rule, the database responds as follows:

• ApsaraDB RDS for MySQL 5.6, ApsaraDB RDS for MySQL 5.7 instances, and PolarDB for MySQL 5.6 clusters return a 1317 error (query execution was interrupted).

• For ApsaraDB RDS for MySQL 8.0 instances and PolarDB for MySQL 5.7 or PolarDB for MySQL 8.0 clusters, the throttled SQL statements enter a Concurrency control waiting state. If the number of waiting statements exceeds the value of the ccl_max_waiting_count parameter (if supported by the instance version), the system returns a "Concurrency control waiting count exceed max waiting count" error and one of the following error codes:

  • ApsaraDB RDS for MySQL 8.0: ERROR 7534 (HY000).

  • PolarDB for MySQL 5.7: ERROR 3277 (HY000).

  • PolarDB for MySQL 8.0: ERROR 7533 (HY000).

  If ccl_max_waiting_count is set to its default value of 0, all throttled SQL statements remain in the Concurrency control waiting state, and no error is returned. When you configure throttling through DAS, if this parameter is 0, DAS automatically sets it to the default value of 10. If you have set the parameter to a different value greater than 0, DAS uses your custom setting.

For a detailed technical explanation of automatic SQL throttling, see Automatic SQL throttling.

Procedure

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

4. In the navigation pane on the left, click Autonomy Center.

5. On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.

6. In the Autonomous Function Management panel, click the Autonomous Function Settings tab. Turn on the main switch for the autonomy service, navigate to the Optimization and Throttling tab, select Automatic Throttling, and then configure the following trigger conditions.

   Parameter	Description
   CPU Utilization >	Specifies the CPU utilization threshold. The value must be 70% or higher.
   Number of Active Sessions >	Specifies the threshold for the number of active sessions. If you use the OR operator, the value must be 16 or greater. If you use the AND operator, the value must be 2 or greater.
   Available Time Range	The time period during which automatic SQL throttling can be performed.
   Maximum Throttling Duration	The maximum duration for a single SQL throttling task. If Maximum Throttling Duration is set to 0, throttling is triggered when the conditions are met but immediately becomes inactive.
   Duration >	The duration for which CPU utilization and the number of active sessions must exceed their thresholds before automatic SQL throttling is triggered.

   Note

   For example, you can configure the following trigger condition within the available time range: If CPU utilization exceeds 80% AND the number of active sessions exceeds 64 for more than 2 minutes, DAS triggers automatic throttling.

   • If the issue is not resolved after throttling, the operation is automatically rolled back.

   • The throttling duration will not exceed the value specified for Maximum Throttling Duration.

7. Click OK.

   You can view the automatic SQL throttling history on the Instance Sessions page. For more information, see View automatic SQL throttling history.

8. (Optional) Click the Event Subscription Settings tab to configure notifications for automatic SQL throttling events.

   When DAS triggers automatic SQL throttling, it generates a Warning level event. To receive notifications, turn on the Enable Subscription Service switch and configure the notification parameters. For more information, see Enable event subscription.

9. Configure an Alert Template to subscribe to notifications. This ensures you are notified about automatic SQL throttling events on your database instance.

   The system recommends a template and adds the corresponding alert rules for autonomy events. You can follow the prompts to complete the configuration.

   Note

   • If you have already configured an alert template for the instance, follow the prompts to add the required alert rules to your existing template.

   • To configure alert templates and rules manually, see Configure alert templates and Configure alert rules.

10. Select an Alert Contact Group to receive notifications.

    • Click Add Contact to add a new alert contact.

    • Click Add Contact Group to add a new alert contact group.

    • To modify or delete an existing contact, find the contact and click Modify or Remove in the Actions column.

    For more information, see Manage alert contacts.

11. Click Submit Configuration and confirm the settings in the dialog box that appears.

View throttling history

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

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

5. On the Instance Sessions page, perform the following steps:

   • Click SQL Throttling.

     • On the Running tab, view the throttling rules that are currently active.

     • On the Complete tab, select a time range to view the history of completed throttling tasks.

     The history includes the following columns: Throttling Mode, Max Concurrency, Start Time, End Time, Throttling Rule, and Actions. You can click Enable in the Actions column to reactivate a completed rule.

   • If you selected Kill Abnormal SQL Statements in Execution in the settings, you can click End Session History to view a record of terminated sessions.

References

• You can optimize SQL queries to improve database performance and reduce resource consumption.

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

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

• If the CPU utilization or number of active sessions does not reach the specified threshold, DAS does not trigger automatic throttling. In this case, you can manually configure a rule to throttle SQL statements. For more information, see SQL throttling.

API reference