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.

Prerequisites

  • A database instance that runs one of the following database engines and editions is used:
    • MySQL 5.6, MySQL 5.7, and MySQL 8.0 in ApsaraDB RDS High-availability Edition or Enterprise Edition
    • MySQL 5.6, MySQL 5.7, and MySQL 8.0 in PolarDB Cluster Edition and MySQL 8.0 in PolarDB Archive Database Edition
  • The Enable Autonomy Service switch is turned on for the database instance. For more information, see Autonomy center.
  • SQL Explorer is enabled for the database instance. For more information, see SQL Explorer.
    Note After slow SQL optimization is complete, global performance may deteriorate. To improve the precision of the automatic SQL optimization feature, we recommend that you enable SQL Explorer.

Applicable scenarios and case videos

The automatic SQL throttling feature is applicable to the following three types of scenarios:
  • Traffic: Cache penetration or abnormal calls may result in a sharp rise in the number of concurrent SQL statements of a specific type.
  • Data: A large number of SQL statements are submitted from a user who has placed a large order. These SQL statements consume a large number of database resources.
  • SQL statements: A large number of SQL statements are submitted to query tables that are not indexed. This affects the normal services of databases.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. Find the instance for which you want to enable automatic SQL throttling, and click the instance ID. The instance details page appears.
  4. In the left-side navigation pane, click Autonomy Center.
  5. On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
    Autonomy Service Settings
  6. In the Set dialog box, turn on the Enable Autonomy Service switch.
  7. Turn on the Automatic Throttling switch and configure the following parameters to specify trigger conditions.
    2
    Parameter Value range
    CPU Utilization At least greater than 70%.
    Active Sessions An integer that is at least greater than 16.
    Duration At least more than 2 minutes. The maximum throttling duration cannot be a negative number.
    Note Assume that you specify the following trigger conditions: The CPU utilization is greater than 80%. The number of concurrent active sessions is greater than 64. The duration for which the abnormal situation can last before automatic throttling is triggered is greater than 2 minutes. If the preceding conditions are met within a specified throttling window, automatic SQL throttling is triggered. The system also starts to check whether the conditions are met again.
    • If the issue persists, the system 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 At the same time, KILL abnormal SQL statements in execution, the system also kills the abnormal SQL statements to be throttled. This helps recover instance performance in a short period of time.
  8. Click OK.
    Note After automatic SQL throttling is enabled, if an SQL statement submitted from an application meets all trigger conditions, the application receives error 1317. The error message is query execution was interrupted.

Scenarios

1
  • Anomaly detection

    DAS uses machine learning algorithms to train a model offline by using historical performance data of database instances, 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 each exception to database instances and collects the information about active sessions if an exception occurs. Then, DAS uses the full request analysis feature in the SQL audit and the statistics in performance_schema to identify the root cause of the exception.
    • 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 execution time of SQL statements is excessively long, DAS closes the sessions instead of implementing throttling on 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 continuously submitted.

    • Traffic-intensive SQL statements

      These SQL statements are normal. However, when a large number of such SQL statements are executed at the same time, they consume a large number of database resources. This causes performance bottlenecks and exceptions, such as slow responses to simple key-value queries.

    • Other SQL statements

      Except for the preceding SQL statements, other SQL statements may also cause exceptions in databases.

  • Automatic throttling

    When DAS detects resource-intensive and traffic-intensive SQL statements, DAS automatically extracts the features of these SQL statements. After you authorize DAS, DAS automatically implements throttling on all the SQL statements that have these features.

  • Feature extraction
    SQL throttling can be implemented based on SQL templates or SQL statements. Every time DAS detects abnormal SQL statements that need throttling, DAS extracts the features of the SQL statements for precise throttling. This prevents your business from being compromised due to inaccurate 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 where 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, 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. For example, when a traffic spike occurs or a large number of SQL statements are submitted to query tables that are not indexed, you can implement this type of throttling.

    • 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 applicable to scenarios in which data skew is detected.

    In SQL template-based throttling, DAS extracts the problematic template ID from SQL statements and implements throttling on the statements that have the template ID. The template ID may be an SQL ID that is automatically generated by the 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 affected. However, identical SQL statements that do not contain the template ID, such as those submitted in the command line, are not affected by the throttling operation. These statements can still be executed.

    Therefore, if SQL statements do not contain the template ID, you must extract features from 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. 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. If these features are used for throttling, throttling is implemented on the SQL statements that have these features regardless of the submission methods. The drawback is that throttling may be implemented on some SQL statements in an unexpected way. For example, SQL template 2 has all the features of SQL template 1. Therefore, DAS also implements throttling on 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=?
  • Automatic optimization

    After 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. DAS automatically creates indexes for the relevant tables for 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 decreased or if the traffic is not reduced as much as expected, DAS rolls back the throttling operation. Then, DAS identifies the root cause again.