Database Autonomy Service (DAS) provides the automatic SQL throttling feature. If the conditions that you configure are met, automatic SQL throttling is triggered. This topic describes how to enable the automatic SQL throttling feature.
- The database instance for which you want to enable automatic SQL throttling runs on one of the following database engines:
- MySQL 5.6, MySQL 5.7, or MySQL 8.0 in ApsaraDB RDS for MySQL High-availability Edition or Enterprise Edition
- MySQL 5.6, MySQL 5.7, or MySQL 8.0 in Cluster Edition of the ApsaraDB PolarDB MySQL-compatible edition or MySQL 8.0 in Archive Database of the ApsaraDB PolarDB MySQL-compatible edition
- The Enable Autonomy Service switch is turned on for the database instance for which you want to enable the automatic SQL throttling feature. For more information, see Autonomy center.
- SQL Explorer is enabled for the database instance for which you want to enable the automatic SQL throttling feature. For more information, see SQL Explorer.
Note After slow SQL statements are optimized, the overall performance may deteriorate. To make automatic SQL optimization precise, we recommend that you enable SQL Explorer.
Scenario and video tutorial
- Traffic: Cache penetration or suspicious calls may cause a sharp rise in the number of concurrent SQL statements of a specific type.
- Data: A large number of SQL statements are submitted from an account of a user who has placed a large order. These SQL statements consume a large number of database resources.
- SQL statement: A large number of SQL statements are submitted to query tables that are not indexed. This affects database performance.
- Log on to the DAS console.
- In the left-side navigation pane, click Instance Monitoring.
- On the Instance Monitoring page, find the instance for which you want to enable the automatic SQL throttling feature and click the instance ID. The instance details page appears.
- In the left-side navigation pane, click Autonomy Center.
- In the upper-right corner of the Autonomy Center page, click Settings.
- In the Settings dialog box, turn on the Enable Autonomy Service switch.
- Turn on the Automatic Throttling switch and configure the following parameters to specify the trigger conditions.
Parameter Description CPU Utilization The CPU utilization must be greater than 70 percent. Active Sessions The value must be an integer that is greater than 16. Duration The duration is measured in minutes. The duration must be greater than 2 minutes. The maximum throttling duration cannot be a negative value.Note For example, you specify the following trigger conditions: The CPU utilization is higher than 80 percent. The number of active sessions is greater than 64. The abnormal situation lasts for more than 2 minutes. If the preceding conditions are met within a specified throttling window, automatic SQL throttling is triggered and the system starts to monitor the instance performance.
- If the issue persists after throttling, the system automatically rolls back the throttling operation.
- After automatic SQL throttling is triggered, the throttling duration does not exceed the specified maximum throttling duration.
- If you select Kill Abnormal SQL Statements in Execution, the system terminates the abnormal SQL statements to be throttled. This helps improve instance performance in a short period of time.
- Click OK.
Note After automatic SQL throttling is enabled, if an SQL statement that is submitted from an application meets all trigger conditions, the system returns the error code 1317 and the error message
query execution was interrupted.
- Exception detection
DAS uses machine learning algorithms to train a model offline by using historical performance data of database instances. Then, DAS uses this model to detect exceptions in real time. Compared with threshold-based detection, the 24/7 exception detection feature provides more timely notifications for exceptions.
- Root cause analysis
DAS subscribes to exceptions in database instances and collects information about sessions when exceptions occur. Then, DAS uses the full request analysis feature of SQL audit and the statistics of performance_schema to identify the root causes of the exceptions. In most cases, the exceptions occur due to the following causes:
- SQL statements that block subsequent operations
DAS analyzes sessions, locks, and running transactions in real time to detect SQL statements that cause DDL changes, lock waits, and large transactions. DAS also checks the number of sessions that are affected by the SQL statements and the execution time of the SQL statements. If an SQL statement affects a large number of sessions or requires a long execution time, DAS terminates the sessions instead of performing SQL throttling.
- Resource-intensive SQL statements
The number of concurrent SQL statements of this type may not be large. However, SQL statements of this type consume a large number of CPU resources, I/O resources, or network resources. These statements are also continuously submitted.
- Traffic-intensive SQL statements
SQL statements of this type are being properly executed. However, when a large number of these SQL statements are executed at the same time, the SQL statements consume a large number of database resources. As a result, performance bottlenecks and exceptions can occur. This includes a slow system response to the queries of key-value pairs.
- Other SQL statements
SQL statements of other types can also cause exceptions in databases.
- SQL statements that block subsequent operations
- Automatic throttling
When DAS detects resource-intensive SQL statements and traffic-intensive SQL statements, DAS automatically extracts the features of these statements. After you authorize DAS to throttle SQL statements, DAS automatically throttles the abnormal SQL statements.
- Feature extraction
SQL throttling can be implemented based on SQL templates or SQL statements. Each time DAS detects abnormal SQL statements that need to be throttled, DAS extracts the features of the SQL statements for precise throttling. This prevents your business from being degraded due to imprecise feature extraction. In ideal scenarios, the extracted features are unique and throttling is implemented only on the identified abnormal SQL statements.
- SQL template-based throttling
An SQL template is an SQL statement in which the parameters do not have specific values. If a large number of SQL statements that use a specific SQL template are executed at a time, exceptions can occur regardless of parameter values. Therefore, you need only to extract the features of the SQL template. This type of throttling applies to multiple scenarios. For example, throttling is implemented when traffic spikes occur or when a large number of SQL statements are submitted to query tables that are not indexed.
- SQL statement-based throttling
If some of the SQL statements that use a specific SQL template cause exceptions, you must extract features from the SQL template and the SQL statements that contain specific parameter values. This type of throttling is suitable for scenarios in which data skew is detected.
For SQL template-based throttling, DAS extracts the template ID from SQL statements and throttles the statements that have the template ID. The template ID can be an SQL template ID that is automatically generated by database middleware or a hint that is added by developers to the SQL template. This facilitates precise throttling and ensures that SQL statements that use other templates are not throttled. However, template-based throttling cannot be used to throttle SQL statements that do not have a template ID. This includes SQL statements that are submitted in a CLI. These statements can still be executed.
If SQL statements do not contain a template ID, you must extract features from the SQL statements. SQL templates are generated based on computing in the analysis process. In the following example, SQL template 1 is generated based on SQL statement 1 and SQL template 2 is generated based on SQL statement 2. If SQL throttling is implemented based on SQL template 1, the features of SQL template 1 are
select id, name, age from students where name. If these features are used for throttling, SQL statements that have these features are throttled regardless of how the statements are submitted. However, some SQL statements may be throttled in an unexpected manner. In this example, SQL template 2 has all features of SQL template 1. In this case, DAS also throttles SQL statement 2 that uses SQL template 2.
/* SQL statement 1 */ select id,name,age from students where name='Bob'; /* SQL template 1 */ select id,name,age from students where name = ?; /* SQL statement 2 */ select id,name,age from students where name='Bob' and sid='Unique ID'; /* SQL template 2 */ select id,name,age from students where name=? and sid=?
- SQL template-based throttling
- Automatic optimization
If DAS detects an abnormal SQL statement that can be optimized during root cause analysis, DAS implements throttling as an emergency response and also optimizes the SQL statement. To optimize query performance, DAS automatically creates indexes for the tables to query.
- Monitoring and rollback
After you enable automatic SQL throttling, DAS monitors the performance of a database. If the workloads of the database are not decreased or if the amount of traffic is not reduced as expected, DAS rolls back the throttling operation. Then, DAS restarts to locate the root cause.