As business workloads increase, the number of SQL requests increases. As a result, database monitoring metrics increase. Some SQL requests have similar characteristics. Therefore, the identification of abnormal SQL requests is difficult if you use the full SQL request analysis feature. In this case, Database Autonomy Service (DAS) provides the abnormal SQL request identification feature that can aggregate and filter SQL requests by using machine learning. This helps you identify abnormal SQL requests in a fast, efficient, and accurate manner.
Prerequisites
The database instance that you want to manage is of one of the following types:
ApsaraDB RDS for MySQL 5.6, 5.7, or 8.0
ApsaraDB MyBase for MySQL 5.6, 5.7, or 8.0
The database instance is connected to DAS. For more information, see Autonomy center.
DAS Enterprise Edition is enabled for the database instance. For more information, see the Enable DAS Economy Edition and DAS Enterprise Edition section of the "Enable and manage DAS Economy Edition and DAS Enterprise Edition" topic.
Scenarios
Scenario: A large number of SQL templates are used for your business requests. You cannot identify abnormal SQL requests in a short period of time after they are initiated regardless of how you sort the SQL templates. In the following figure, the queries per second (QPS) charts framed in red and in blue illustrate two types of abnormal SQL requests.
Solutions: Use the abnormal SQL request identification feature to identify performance issues. You can use one of the following solutions:
Solution 1: If your database instance receives a large number of requests, you can use DAS to classify SQL templates based on SQL behavior. For example, if hundreds of pages of SQL templates are used, you cannot quickly identify abnormal SQL templates by sorting all the templates. In this case, you can use the abnormal SQL request identification feature. This feature uses the backend algorithm of DAS to identify similar SQL behavior profiles. Then, the feature aggregates and classifies a large number of SQL templates to help you identify issues in a more efficient manner.
Solution 2: Use the abnormal SQL request identification feature to troubleshoot issues related to metric exceptions based on the periods during which the exceptions occurred. The following figure shows the trend of an abnormal metric. In this figure, the time during which the CPU utilization and the number of active sessions reach the peak values coincides with the time when the occurrences of the SQL templates of the current category abruptly decrease. This indicates that the SQL requests that use the SQL templates of this category are blocked when the number of active sessions spikes.
Procedure
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
On the instance details page, click Autonomy Center in the left-side navigation pane.
Specify a time range and click Search to view the events in the specified time range.
Click Details of an event to view the details of the event. In this example, time series exception detection is used.
In the Analysis of Abnormal Metrics section of the Anomaly Snapshots tab, view the exception causes and related metric changes.
In the Performance Metrics section, select different types of abnormal metrics to view the distribution of the selected metrics within the specified time range.
In the SQL Request Behavior Analysis section, select a monitoring metric and an associated metric to perform analysis.
DAS analyzes the top 1,000 SQL templates. SQL requests that are generated based on these templates have consumed the largest amount of time. The full SQL request analysis feature of DAS Enterprise Edition retrieves and analyzes the full SQL data of a database instance. The process is time-consuming. The duration varies based on the data size. In most cases, the system takes approximately 1 to 5 minutes to analyze the data.
View analysis results.
View the SQL metric that is most correlated with the abnormal metric. You can also view the corresponding SQL template and statistics.
NoteA value closer to 1.00 indicates a higher correlation with the abnormal metric.
Additional information
You can view that similar SQL templates have similar occurrence trends by using the abnormal SQL request identification feature.
If the SQL requests that use the SQL templates of a category spike for a short period of time, but the spike time is different from the metric spike time, the SQL templates of this category are not most correlated with the abnormal metric.
The following figure shows the trends of abnormal metrics. The point in time at which the CPU utilization and the number of active sessions reach the peak values coincides with the point in time at which the SQL requests that use the SQL templates of the category suddenly decrease. This indicates that the SQL requests that use the SQL templates of this category are blocked when the number of active sessions spikes.
