If the number of concurrent SQL statements significantly increases due to unexpected traffic surges, some SQL statements consume a large number of database resources, or a large number of SQL statements without indexes are executed, you must limit the concurrency of SQL statements to ensure business stability. Database Autonomy Service (DAS) provides the automatic SQL throttling feature. The feature automatically extracts SQL statement characteristics and throttles problematic SQL statements by keyword.
Prerequisites
The database instance that you want to manage is of one of the types described in the following table.
Database instance | Region |
| The automatic SQL throttling feature depends on the anomaly detection feature. You can enable automatic SQL throttling only for database instances in regions that support the anomaly detection feature. The following regions are supported: 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) |
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.
Limits
The throttling mode of automatic SQL throttling is Throttled by Keywords:
SQL statements of the following types can be throttled: SELECT, UPDATE, DELETE, and INSERT.
SQL statements of the INSERT...SELECT... type cannot be throttled.
Only ApsaraDB RDS for MySQL instances that run MySQL 8.0 and PolarDB for MySQL clusters that run MySQL 5.7 or 8.0 can throttle SQL statements of the INSERT type.
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 waitingstate. 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.0ERROR 3277 (HY000)for a PolarDB for MySQL cluster that runs MySQL 5.7ERROR 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 waitingstate 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, which is 10. 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
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
On the instance details page, click Autonomy Center in the left-side navigation pane.
On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
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 >
The threshold for CPU utilization. Set the parameter to a value that is greater than or equal to 70.
Number of Active Sessions >
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 >
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.
NoteFor 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.
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.
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.
In the Alert Configuration section, configure an alert template and subscribe to alert notifications. This helps you understand the status of an automatic SQL throttling task at the earliest opportunity.
The system recommends an alert template and adds alert rules for the required autonomy events in the alert template. You can configure the alert template as prompted.
NoteIf you have configured an alert template for your database instance, you must add alert rules for the required autonomy event to the alert template as prompted.
For more information about how to configure an alert template and alert rules for your database instance, see Configure alert templates and Configure alert rules.
In the Select Contact Group section, select an alert contact group.
Click Add Contact to add an alert contact.
Click Create Contact Group to create an alert contact group.
Find the alert contact that you want to manage and click Edit or Remove in the Actions column to modify or delete information about the alert contact.
For more information, see Manage alert contacts.
Click Submit Configuration. In the dialog box that appears, confirm the configuration.
View the history of automatic SQL throttling
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Instance Sessions.
In the Instance Sessions section of the Session Management 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
Operation | Description |
Asynchronously specifies the parameters of automatic SQL throttling for multiple database instances at a time. | |
Queries the automatic SQL throttling rules of database instances. | |
Disables the automatic SQL throttling feature for multiple database instances at a time. |