Automatic SQL throttling - Autonomy Center| Alibaba Cloud Documentation Center

Database Autonomy Service (DAS) provides the automatic SQL throttling feature. When you enable this feature, you must customize a trigger condition. When the condition is met, DAS automatically throttles SQL statements as configured. This topic describes how to use the automatic SQL throttling feature. An ApsaraDB RDS for MySQL database is used as an example.

Prerequisites

An ApsaraDB RDS for MySQL database or a PolarDB for MySQL database is connected to DAS.
The autonomy service is enabled 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

The automatic SQL throttling feature is applicable to the following scenarios:

An exception, such as cache penetration or an improper SQL statement execution, causes a large number of concurrent SQL statements of a specific type.
A large number of SQL statements are submitted from the account of a customer who places a large order. These SQL statements are occupying most resources in the database.
A large number of SQL statements are submitted to query tables that are not indexed, affecting the performance of the database.

Use case

How it works

Exception 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 conventional threshold-based detection, the 24/7 exception detection feature of DAS can detect exceptions in a more timely manner.
Root cause analysis
DAS subscribes to exceptions in the database instance and collects information about current sessions after an exception occurs. Then, DAS uses the full request analysis feature and performance insight features to identify the root cause of the exception. DAS classifies problematic SQL statements into the following types:
- Blocking SQL statements
  DAS detects SQL statements that cause data definition language (DDL) changes, lock waits, and large transactions by analyzing real-time sessions, locks, and running transactions. DAS also checks the number of sessions that are affected by the SQL statements and the execution time of the SQL statements. If the number of sessions that are affected is excessively large or the execution time is excessively long, DAS terminates related sessions, instead of throttling the SQL statements.
- Resource-intensive SQL statements
  The number of concurrent SQL statements of this type may not be necessarily high. However, SQL statements of this type consume large amounts 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 occupy most resources of a database, causing exceptions such as longer response for even simple key-value queries.
- Other SQL statements
  Except for the preceding SQL statements, other SQL statements may also cause exceptions in a database.
Automatic throttling
When DAS detects resource-intensive and traffic-intensive SQL statements, DAS automatically extracts features of these SQL statements. Under your authorization, DAS automatically throttles all the SQL statements with these features.
Feature extraction
Every time DAS detects problematic SQL statements that need to be throttled, DAS extracts the features of the SQL statements. To ensure precise throttling and prevent misoperations, DAS must correctly extract the features. Ideally, the features must identify only problematic SQL statements. This helps ensure precise SQL throttling. 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 SQL statements that use a specific SQL template cause exceptions whenever a large number of these SQL statements are executed, regardless of the parameter values in the SQL statements, you only need to extract the features of the SQL template. Then, you can throttle SQL statements with the extracted features. This type of throttling is applicable to various 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 not all SQL statements that use a specific SQL template cause exceptions, you must extract both the SQL template and the SQL text that contains specific parameter values as the features. Then, you can throttle SQL statements with the extracted features. This type of throttling is applicable to scenarios in which data skew is detected.
In SQL template-based throttling, if SQL statements contain the template ID, DAS preferentially extracts the template ID and throttles all the SQL statements with the 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 in the SQL template. This allows precise throttling and ensures that SQL statements that use other templates are not affected. However, the same SQL statements that do not contain the template ID, such as those submitted in the command line, are missed during the throttling.

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 two SQL templates that are respectively generated based on two SQL statements. Assume that you use SQL template 1 for SQL throttling. The features of SQL template 1 is select id, name, age from students where name. Throttling based on these features ensure that all SQL statements with these features are throttled, regardless of the mode in which the SQL statements are submitted. The drawback is that other SQL statements may be mistakenly throttled. As shown in the following code snippet, SQL template 2 has all the features of SQL template 1. Therefore, DAS will also throttle SQL statement 2 based on SQL template 1, which may not 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=?
```
Automatic optimization
After DAS detects a problematic 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 the database. If the workload of the database is not eased or the traffic is not reduced as much as expected, DAS rolls back the throttling operation and analyzes the root cause again.

Procedure

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the Instance Monitoring page, find the target instance and click the instance ID to go to the instance details page.
In the left-side navigation pane, click Autonomy Center.
On the Autonomy Center page, click Switch Settings in the upper-right corner.
In the Switch Settings dialog box, turn on Enable Autonomy Service.
Turn on Automatic Throttling and set a trigger condition.

Note For example, you can specify that automatic SQL throttling is triggered if, for five consecutive minutes, the CPU usage is greater than 80% and more than 64 concurrent sessions are active. After automatic SQL throttling is triggered, DAS tracks the performance of the relevant database. If the exception persists, DAS rolls back the throttling operation and analyzes the root cause again. You must set a maximum throttling period, so that DAS will stop throttling after this period ends.
Click OK.

Note After automatic SQL throttling is enabled, if the SQL statement submitted by a client meets the trigger condition, the client receives error 1317. The error message is query execution was interrupted.