Database Autonomy Service:Automatic SQL throttling

Prerequisites

Before you begin, ensure that you have:

• A supported database instance type:

  • ApsaraDB RDS for MySQL High-availability Edition or Enterprise Edition (MySQL 5.5 is not supported)

  • PolarDB for MySQL Cluster Edition (single-node clusters are not supported)

  • ApsaraDB MyBase for MySQL High-availability Edition

• A database instance in a region that supports the anomaly detection feature. Supported regions: China (Hangzhou), China (Shanghai), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Nanjing - Local Region), China (Fuzhou - Local Region), China (Chengdu), China (Zhengzhou - Local Region), China (Hong Kong), Japan (Tokyo), South Korea (Seoul), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), Thailand (Bangkok), UAE (Dubai), SAU (Riyadh - Partner Region), Germany (Frankfurt), US (Silicon Valley), US (Virginia), and UK (London)

Use cases

Limitations

Automatic SQL throttling uses Throttled by Keywords mode only.

• Throttleable statement types: SELECT, UPDATE, DELETE, and INSERT

• INSERT...SELECT... statements cannot be throttled

• INSERT throttling is only supported on ApsaraDB RDS for MySQL running MySQL 8.0 and PolarDB for MySQL clusters running MySQL 5.7 or 8.0

How it works

When a throttling rule is active and an application executes a matched SQL statement, the database returns an error or holds the statement in a waiting state depending on the MySQL version.

MySQL 5.6 and 5.7 (ApsaraDB RDS for MySQL) and MySQL 5.6 (PolarDB for MySQL)

The database returns error code 1317 with the message query execution was interrupted.

MySQL 8.0 (ApsaraDB RDS for MySQL) and MySQL 5.7 or 8.0 (PolarDB for MySQL)

Matched statements enter Concurrency control waiting state. Once the number of waiting statements exceeds the ccl_max_waiting_count threshold, the database returns one of the following errors:

About `ccl_max_waiting_count`: The default value is 0, which keeps all throttled statements in the waiting state without returning an error. When DAS applies a throttling rule and detects that ccl_max_waiting_count is 0, DAS automatically sets it to 10. If you set it to any value greater than 0, DAS uses that value.

For a conceptual overview of automatic SQL throttling, see Automatic SQL throttling.

Enable automatic SQL throttling

1. Log on to the DAS console.

2. In the left-side navigation pane, click Instance Monitoring.

3. Find the database instance you want to manage and click its instance ID to open the instance details page.

4. In the left-side navigation pane, click Autonomy Center.

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

1. On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service. Then go to the Optimization and Throttling tab, select Automatic Throttling, and configure the following parameters.

For example, set CPU utilization to over 80%, active sessions to over 64, and duration to over 2 minutes. When these conditions are met within the configured time window, DAS starts throttling and monitors the instance. If the issue persists after throttling, DAS automatically rolls back the throttling operation. The throttling duration never exceeds the maximum throttling duration you configured.

1. Click OK. After throttling is triggered, view active throttling rules and history on the Instance Sessions page. For details, see View throttling history.

2. (Optional) Click the Event Subscription Settings tab to configure notifications for automatic SQL throttling events. When automatic SQL throttling triggers, DAS sends Warning event notifications. Turn on Enable Subscription Service and configure the parameters. For more information, see Event subscription.

3. In the Alert Configuration section, configure an alert template and subscribe to alert notifications to track throttling task status in real time. DAS recommends an alert template and adds alert rules for the required autonomy events automatically. Configure the template as prompted.

   If an alert template is already configured for your instance, add alert rules for the required autonomy events to the existing template as prompted. For more information, see Configure alert templates and Configure alert rules.

4. In the Select Contact Group section, select an alert contact group. For more information, see Manage alert contacts.

   • Click Add Contact to add an alert contact.

   • Click Create Contact Group to create a new alert contact group.

   • To edit or remove an existing contact, click Edit or Remove in the Actions column.

5. Click Submit Configuration and confirm the settings in the dialog box.

View throttling history

1. Log on to the DAS console.

2. In the left-side navigation pane, click Instance Monitoring.

3. Find the database instance you want to manage and click its instance ID to open the instance details page.

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

5. In the Instance Sessions section of the Session Management tab, perform the relevant action.

• Click SQL Throttling to open the SQL Throttling dialog box:

  • Running tab: View throttling rules currently in effect.

  • Complete tab: Select a time range to review historical throttling events.

What's next

• Optimize SQL statements to reduce resource consumption and improve query efficiency:

  • Automatic SQL optimization

  • SQL optimization

• Manual SQL throttling: If CPU utilization or active sessions do not reach the thresholds needed to trigger automatic throttling, configure a rule manually. See SQL throttling.

API reference