Database Autonomy Service (DAS) provides the abnormal SQL request identification feature. You can use this feature to detect abnormal SQL requests from a large number of SQL requests. This helps you identify issues in a more efficient manner. This topic describes the scenarios in which abnormal SQL request identification is used and how to configure this feature.
Prerequisites
The database instance that you want to manage is of one of the following types:
ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0
ApsaraDB MyBase for MySQL 5.6, 5.7, and 8.0
DAS Enterprise Edition is enabled for the database instance.
Background information
When your workloads increase, more SQL requests are generated. As a result, the system needs to monitor more database metrics. Some SQL requests have similar characteristics. In this case, the full SQL request analysis feature cannot detect each abnormal SQL request. DAS provides the abnormal SQL request identification feature to aggregate and classify SQL requests based on machine learning methods. This helps you identify abnormal SQL requests in a faster, more efficient, and more accurate manner.
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 identify abnormal SQL templates in a quick manner by sorting all the templates. 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 Instance Monitoring page, find the database instance that you want to manage and click the instance ID to go to the instance details page. In the left-side navigation pane of the instance details page, click Autonomy Center. Specify a time range and click Search. Then, you can select Exceptional Events, Optimization Events, Auto-Scaling Events, or Others to view the metric values in the specified time range.
The following figure shows the IOPS Usage, CPU Utilization, and Active Sessions metrics. Click the yellow line that is marked 1 and check the results of time series exception detection in the lower part of the page.
NoteEach row in the area that is marked 2 in the preceding figure shows the following information:
The first row shows the severity of the time series metric exception. The color varies based on the severity of the exception. Red, yellow, and blue indicate the severity in descending order. Red indicates the highest severity and blue indicates the lowest severity.
The second row shows the status of time series exception detection and slow query optimization events that are based on the results of time series exception detection.
Click Details. On the Anomaly Detection tab, view the causes in the Anomaly Analysis section. In the Exceptional Metric section, select a metric category and view how the time is distributed for the abnormal metrics of this category.
NoteIn the preceding figure, the red anchor points represent the most severe exceptions. Red, yellow, and blue indicate the severity in descending order. Red indicates the highest severity and blue indicates the lowest severity.
In the Anomaly Analysis section, find an exception cause and click View Related SQL Statements in the Related Metrics column. In the SQL Request Behavior Analysis section, select a metric and an associated SQL metric and click Analysis.
NoteThe backend system of 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 the SQL Request Behavior Analysis chart and the analysis table.
NoteThe first row shows that the value of the cpu_usage Correlation parameter is close to 1.00. This indicates that the category to which this SQL template belongs is most correlated with CPU utilization and that this SQL metric is most correlated with the abnormal metric.
The following table describes the parameters of SQL metrics.
Parameter
Description
Aggregate Images
Metrics in each aggregate image are aggregated.
cpu_usage Correlation
An SQL template category whose value of the cpu_usage Correlation parameter is closer to 1.00 is more correlated with the abnormal metric.
SQL Templates
The number of SQL templates of the current category. For more information about SQL templates, see Full request feature.
Average Time Consumed
The amount of time that is consumed to execute the SQL statements during the period of the exception. These SQL statements are generated based on the SQL templates of the current category. Unit: seconds.
Execution Percentage
The percentage of the number of times for which the SQL statements are executed to the total number of SQL executions. These SQL statements are generated based on the SQL templates of the current category.
Scanned Rows
A value in the Scanned Rows column indicates the total number of database table rows that are scanned by SQL requests. These SQL requests use the SQL templates of the current category.
Duration Percentage
The percentage of the execution duration of the SQL statements that use the SQL templates of the current category to the total SQL execution duration.
Click View to obtain the correlation results of each SQL template.
In the following figure, the SQL template represented by a yellow line is closely correlated with the mysql.cpu_usage, mysql.innodb_rows_read, and mysql.active_session metrics. The SQL requests that are generated based on this SQL template are the main causes for the spike in CPU utilization.
Additional information
The abnormal SQL request identification feature provided by DAS aggregates and classifies a large number of SQL templates. Similar SQL templates have similar occurrence trends.
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.
