Among all the factors that affect system stability, slow SQL is relatively fatal. It may cause CPU, LOAD exceptions, and system resource exhaustion. There are many reasons for slow SQL in an online production environment:

  • Hardware problems. For example, the network speed is slow, the memory is insufficient, the I/O throughput is small, and the disk space is full.
  • No index or index failure.
  • Too much data.
  • After DevOps, no professional DBA reviewed the SQL code, and the performance of SQL was not well considered at the beginning of the project. Severe slow SQL can bring down the entire database, creating the risk of interrupting online business. AHAS flow control degradation provides SQL level identification and protection, which can help developers and o&M students quickly identify slow SQL and take effective protection measures to ensure the stability of the system.

AHAS product address

SQL automatic protection document address

Application scenarios

AHAS provides rich SUPPORT for SQL scenarios that can be identified and defended without changing the code, including:

  • Control SQL statements based on the call frequency and execution time, such as direct rejection or queuing.
  • Automatic detection of SQL execution time, frequency, and control.
  • Database read/write linkage control. For example, the speed of read operations is determined by the speed of write operations.
  • SQL performs concurrency control. We’ll cover the different uses in detail in the slow SQL Protection section.

SQL recognition

AHAS flow control degradation By automatically detecting COMMON DAO classes and JDBC driver classes to automatically identify SQL statements in applications, you can use Java Agent or Java SDK to monitor and intercept SQL statements. Java Agent currently supports MySQL JDBC and Oracle JDBC drivers, and SDK currently supports SQL identification under the MyBatis framework. For details about versions supported by third-party components and frameworks, see the supported list.

If you use Agent access, you only need to download and install the latest Agent:

wget -O ./install_agent.sh https://ahasoss-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/agent/prod/latest/install_agent.sh && source ./install_agent.sh {YourAppName} defaultCopy the code

SDK access requires the introduction of aHAS-Sentinel-Client in application dependencies:

<dependency>
   <groupId>com.alibaba.csp</groupId>
   <artifactId>ahas-sentinel-client</artifactId>
   <version>x.y.z</version>
</dependency>Copy the code

On the monitoring page of the AHAS console, you can view the QPS and RT information about SQL resource invocation. On the monitoring page, you can view the RT value of the SQL to determine whether the SQL execution is slow. In this way, you can set traffic limiting and degradation rules in advance for multi-means defense. Common defense rules for SQL are explained in slow SQL defense.

Slow SQL protection

AHAS flow control degradation provides a variety of protection methods. For the case of slow SQL on the line, the system can be protected from two aspects: traffic limiting and degradation. Rule configuration takes effect immediately after it is enabled. It can not only set up protection in advance, but also handle problems in an emergency when online, avoiding the system being dragged down by a large number of slow SQL calls.

Current limiting strategy

Based on the SQL statements automatically recognized by the console, users can configure the corresponding flow limiting rules to control SQL execution. AHAS supports restrictions on QPS and the number of concurrent threads. SQL control generally adopts the number of concurrent threads mode. When slow SQL calls occur, the number of SQL executed at the same time can be limited to prevent resource exhaustion caused by excessive slow SQL statement execution. Flow control mode AHAS provides different flow control modes. Users can choose to use them based on their own business scenarios. In slow SQL defense, direct and association modes are commonly used: 1. The most common flow control mode, in which the threshold is configured to the current SQL resource and excess requests beyond the set value are rejected.


2. Association mode. The threshold is configured to associate SQL resources. If the number of associated resource requests exceeds the threshold, the resource invocation will be blocked. You can associate SQL reads with write resources as needed.


Demotion strategy

In addition to the traffic limiting rules, AHAS also provides a demotion strategy based on RT and exception ratio. For slow SQL defense, you can set the threshold and time window based on the RT value of SQL execution. If the RT value exceeds the specified value, SQL execution in the time window will be degraded and a packaged exception will be thrown.


After a resource is degraded by flow control, a subclass of BlockException exception is thrown. For example, FlowException is thrown after flow limiting, and DegradeException is thrown after flow degradation. Developers can customize service processing of exceptions, such as retry.

Powerful monitoring

AHAS provides powerful monitoring functions to help users quickly find problems and deal with them in time. On the application Summary page, you can view the total QPS, rejected QPS, and RT of the cluster.

In addition, you can view information about TOP cpus and loads in a cluster or single machine, and playback historical data:


In addition, you can also intuitively see the TOP situation of different SQL statements, including TOP QPS requests, TOP REJECTED QPS, RT TOP, and the corresponding TOP data of single machine:


On the monitoring details page, you can view the call and execution of each SQL statement. If the RT request is too long, you can perform one-key flow limiting or degrade:


The above is the slow SQL-based defense provided by AHAS flow control degradation. Monitoring -> Detecting exceptions -> Configuring rules -> Effective defense ensures the high availability service capability of applications.

The original link

This article is the original content of the cloud habitat community, shall not be reproduced without permission.