Database Autonomy Service (DAS) provides the automatic SQL throttling feature. If the conditions that you configure are met, automatic SQL throttling is triggered.
Prerequisites
- The database instance that you want to manage is of one of the following types:
- An ApsaraDB RDS for MySQL instance of the High-availability Edition or the Enterprise Edition that runs MySQL 5.6, MySQL 5.7, or MySQL 8.0
- A PolarDB for MySQL cluster of the Cluster Edition that runs MySQL 5.6, MySQL 5.7, or MySQL 8.0 or a PolarDB for MySQL cluster of the X-Engine that runs MySQL 8.0
- The autonomy service is enabled for the database instance. For more information, see Autonomy center.
Applicable scenarios
- Traffic: Cache penetration or suspicious requests can cause the number of concurrent SQL queries of a specific type to increase at a sharp rate.
- 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.
- SQL statements: A large number of SQL statements are submitted to query tables that are not indexed. This affects database performance.
Procedure
View the history of automatic SQL throttling
Scenarios

- Anomaly detection
DAS uses machine learning algorithms to train a model offline based on historical performance data of a database instance. Then, DAS uses this model to detect abnormal changes in metrics in real-time. The 24/7 anomaly detection feature is more efficient for database instances than threshold-based anomaly detection methods.
- Root cause analysis
DAS can subscribe to anomaly events in database instances and collect information about sessions that are created when anomalies occur. Then, DAS combines full request analysis and security audit to identify the root causes of the anomalies based on the statistics of performance metrics. In most cases, anomalies occur due to the following causes:
- Blocking SQL statements
DAS analyzes real-time sessions, lock waits, and running transactions to detect SQL statements that cause DDL changes, lock waits, and large transactions. DAS also checks the number of sessions that are created to execute SQL statements and the execution time of SQL statements. If an SQL statement causes a large number of sessions or requires a long execution time, DAS terminates the abnormal sessions instead of performing SQL throttling.
- Resource-intensive SQL statements
SQL statements of this type do not cause high concurrency. However, SQL statements of this type consume a large amount of CPU resources, I/O resources, and network resources. These statements are submitted on an ongoing basis.
- Traffic-intensive SQL statements
SQL statements of this type are executed in a proper manner. However, when a large number of SQL statements are executed at the same time, the SQL statements consume a large amount of database resources. This causes performance bottlenecks and exceptions, such as slow responses to queries of key-value pairs.
- Others
SQL statements of other types can also cause anomalies.
- Blocking SQL statements
- Automatic throttling
When DAS detects resource-intensive and traffic-intensive SQL statements in a database instance, DAS automatically extracts SQL features and throttles abnormal SQL statements based on the automatic throttling settings that you have configured.
- Extraction of characteristics
If DAS detects abnormal SQL statements on which throttling must be implemented, DAS extracts the characteristics of the SQL statements for precise throttling. This prevents the entire service from being compromised due to inaccurate extracted characteristics. In ideal scenarios, the extracted characteristics are unique and only requests that use the identified abnormal SQL statements are throttled. DAS supports the following types of SQL throttling:
- Template-based SQL 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 the same time, anomalies can occur regardless of parameter values. Therefore, DAS extracts only the characteristics of the SQL template. This throttling method 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.
- Statement-based SQL throttling
If some of the SQL statements that use a specific SQL template cause anomalies but others do not, DAS extracts characteristics from the SQL template and the specific parameter values of the SQL statements that cause anomalies. This throttling method is suitable for scenarios in which data skew is detected.
In template-based SQL throttling, DAS extracts template IDs from SQL statements that cause anomalies and throttles SQL requests based on the template IDs. A template ID can be an ID that is automatically generated by database middleware or an SQL 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 characteristics from the SQL statements. SQL templates are generated based on SQL statements 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 DAS throttles SQL requests based on SQL Template 1, the characteristics
select id, name, age from students where name
are extracted. If DAS throttles SQL requests based on these characteristics, SQL requests that use SQL statements with these characteristics are throttled regardless of how the requests are submitted. Even though some SQL requests use SQL statements that are not created based on this template, these requests can also be throttled. In this example, SQL Template 2 has all characteristics of SQL Template 1. In this case, DAS also throttles SQL statements that use 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=?
- Template-based SQL throttling
- Automatic SQL optimization
If DAS detects an abnormal SQL statement that can be optimized during root cause analysis, DAS throttles requests that use this SQL statement as an emergency response and 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 performs root cause analysis again.