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
Your RDS instance runs one of the following MySQL versions and RDS editions:
MySQL 8.0 on RDS High-availability Edition or RDS Enterprise Edition
MySQL 5.7 on RDS High-availability Edition or RDS Enterprise Edition
MySQL 5.6 on RDS High-availability Edition
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 instance.
SQL statements: A large number of SQL statements for which no indexes are created are executed. This affects your workloads.
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
If you enable the automatic SQL throttling feature, different types of database services respond differently to SQL statements that contain all SQL keywords.
If your RDS instance runs MySQL 5.6 or MySQL 5.7, the 1317: "query execution was interrupted" error is returned.
If your RDS instance runs MySQL 8.0, the system enables the related SQL statements to remain in the
Concurrency control waitingstate. After the number of the related SQL statements exceeds the value of the ccl_max_waiting_count parameter, theERROR 7534 (HY000): "Concurrency control waiting count exceed max waiting count" error is returned. The ccl_max_waiting_count parameter may be unavailable for RDS instances that run different database engine versions.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. 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.
Procedure
- Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
Use one of the following methods to go to the Autonomous Function Management panel:
In the left-side navigation pane, choose Autonomy Services > Diagnostics. On the page that appears, click the Autonomy Center tab. On the Autonomy Center tab, click Autonomy Service Settings.
In the left-side navigation pane, choose Autonomy Services > Dashboard. On the Performance Trends tab of the page that appears, click Autonomy Service Settings.
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 following parameters to specify the conditions for triggering automatic throttling.
If this parameter uses the OR relationship with the CPU Utilization parameter, specify a value that is greater than or equal to 16 for this parameter.
If this parameter uses the AND relationship with the CPU Utilization parameter, specify a value that is greater than or equal to 2 for this parameter.
If the issue persists after throttling, the system 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 SQL throttling that is automatically triggered on the Session Management tab displayed after you click Diagnostics in the left-navigation pane. For more information, see View the history of automatic SQL throttling.
Optional. Click the Event Subscription Settings tab to configure notifications for automatic SQL throttling events.
When automatic SQL throttling is triggered, Warning events are generated for DAS. To receive the notifications of the events, you can turn on Enable Subscription Service and configure the related parameters. For more information, see Use the event subscription feature.
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. |
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 duration threshold. If 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. |
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 the system starts to monitor the performance of the RDS instance.
View the history of automatic SQL throttling
- Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
Click the Session Management tab.
In the Instance Sessions section, perform the following operations based on your business requirements:
Click SQL Throttling. In the dialog box that appears, perform the following operations:
Click the Running tab to view the throttling rules that are being executed.
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 historical 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 Use the automatic SQL optimization feature.
For more information about manual SQL optimization, see SQL optimization.
If the CPU utilization or number of active sessions of an RDS instance does not reach the threshold and automatic SQL throttling cannot be triggered, you can configure a rule to throttle SQL statements. For more information, see SQL throttling.
Related 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. |