Database Autonomy Service (DAS) provides the automatic SQL throttling feature. You can specify conditions to trigger automatic SQL throttling. If the specified conditions are met, automatic SQL throttling is triggered. This topic describes how to enable the automatic SQL throttling feature. An ApsaraDB RDS for MySQL database is used in this topic.
Prerequisites
- An ApsaraDB RDS for MySQL 5.6, ApsaraDB RDS for MySQL 5.7, ApsaraDB RDS for MySQL 8.0, PolarDB for MySQL 5.6, or PolarDB for MySQL 8.0 database is connected to DAS.
- The Enable Autonomy Service switch is turned on for the database instance. For more information, see Autonomy center.
Note If you require higher precision of the automatic SQL throttling feature, activate DAS Professional Edition.
Background information
- Traffic: Cache penetration or abnormal calls may result in a sharp rise in concurrent SQL statements.
- Data: A large number of SQL statements are submitted from a user who has placed a large order. These SQL statements consume a large amount of resources in databases.
- SQL statements: A large number of SQL statements are submitted to query tables that are not indexed. This affects the performance of databases.
For more information, see 自动SQL限流.
Use cases
Workflow of automatic SQL throttling
- Anomaly detection
DAS uses machine learning algorithms to train a model by using historical performance data of a database instance, and then uses this model to detect exceptions in real time. Compared with threshold-based detection, the 24/7 anomaly detection feature provides real-time notifications when system events trigger alerts.
- Root cause analysis
DAS subscribes to exceptions in database instances and collects information about current sessions if an exception occurs. Then, DAS uses the full request analysis and performance insight features to identify the root cause of the exception. DAS classifies abnormal SQL statements into the following four types:
- Blocking SQL statements
DAS analyzes sessions, locks, and running transactions in real time to detect SQL statements that cause Data Definition Language (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 a large number of sessions are affected or the SQL statements are executed for an extended period of time, DAS closes the sessions instead of throttling the SQL statements.
- 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, I/O, or network resources, and are frequently submitted.
- Traffic-intensive SQL statements
These SQL statements are normal statements. However, when a large number of such SQL statements are executed at the same time, they consume a large number of resources in databases. This causes performance bottlenecks and exceptions such as long response for simple key-value queries.
- Other SQL statements
Except for the preceding SQL statements, other SQL statements may also cause exceptions in databases.
- Blocking SQL statements
- Automatic throttling
When DAS detects resource-intensive and traffic-intensive SQL statements, DAS automatically extracts features of these SQL statements. After you authorize DAS, DAS automatically throttles all the SQL statements that have these features.
- Feature extraction
Every time DAS detects abnormal SQL statements that need throttling, DAS extracts the features of the SQL statements for precise throttling. In ideal scenarios, the features must identify only abnormal SQL statements. DAS supports two types of SQL throttling:
- SQL template-based throttling
An SQL template is an SQL text where the parameters do not contain specific values. If a large number of SQL statements that use a specific SQL template are executed at the same time, exceptions may occur, regardless of parameter values. Therefore, you need only to extract the features of the SQL template. This type of throttling is applicable to a variety of scenarios, such as when a traffic spike occurs or a large number of SQL statements are submitted to query tables that are not indexed.
- SQL text-based throttling
If some SQL statements that use a specific SQL template cause exceptions, you must extract the SQL template and SQL text that contains specific parameter values as features. This type of throttling is applicable to scenarios in which data skew is detected.
In SQL template-based throttling, DAS extracts the problematic template ID from SQL statements and throttles statements that have that template ID. The template ID may be an ID that is automatically generated by database middleware or an SQL hint that is added by developers to the SQL template. This allows precise throttling and ensures that SQL statements that use other templates are not affected. However, identical SQL statements that do not contain the template ID, such as those submitted in the command line, are not throttled.
Therefore, if SQL statements do not contain the template ID, you must extract SQL text information as features. SQL templates can be generated in the process of root cause analysis. The following code snippet shows that SQL template 1 is generated based on SQL statement 1 and SQL template 2 is generated based on SQL statement 2. Assume that you use SQL template 1 for SQL throttling. The features of SQL template 1 are
select id, name, age from students where name
. Throttling based on these features ensures that SQL statements are throttled regardless of their submission method. The drawback is that some SQL statements may be mistakenly throttled. For example, the following code snippet shows that SQL template 2 has all the features of SQL template 1. Therefore, DAS also throttles SQL statement 2 based on SQL template 1. This may fail to meet your expectation./* 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
After DAS detects an abnormal SQL statement that can be optimized, DAS also automatically optimizes the SQL statement. DAS creates indexes for relevant tables to perform optimization.
- Tracking and rollback
After DAS starts automatic SQL throttling, DAS keeps tracking the performance of a database. If the workload of the database is not eased or if the traffic is not reduced as much as expected, DAS rolls back throttling and detects the root cause again.