Automatic SQL throttling

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.

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.

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=?

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.

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.