Database Autonomy Service (DAS) provides the SQL request behavior identification feature. This feature helps you identify abnormal SQL requests from a large number of requests and locate problems more efficiently. This topic describes the scenarios of the SQL request behavior identification feature and how to enable this feature.
- The instance that is connected to DAS is an ApsaraDB RDS instance that runs MySQL 5.6, 5.7, or 8.0.
- DAS Professional Edition is activated for the instance. For more information, see DAS Professional Edition.
When your workloads increase, more complex metrics are used to monitor databases. As a result, a large number of SQL requests are generated. Some SQL requests have similar characteristics. Therefore, the full SQL analysis feature cannot identify each individual abnormal SQL statement. DAS provides the SQL request behavior identification feature to aggregate and filter SQL requests based on machine learning. This helps you identify abnormal SQL statements in a faster, more efficient, and more accurate manner.
Scenario: If a large number of SQL templates are used for your business requests, you cannot identify abnormal SQL requests shortly after they occur regardless of how the requests are sorted. The red box and the blue box each illustrate a type of abnormal SQL requests.
Solutions: Use the SQL request behavior identification feature to identify performance-related issues. You can use one of the following solutions:
- Solution 1: If your database instance receives a large number of requests, use DAS to classify the SQL templates based on SQL request behaviors. For example, individual abnormal SQL requests can be hardly located from hundreds of pages of SQL templates. In this case, you can use the SQL request behavior identification feature to find similar behavior profiles based on the backend algorithm of DAS. This helps you aggregate a large number of SQL templates and locate problems more efficiently.
- Solution 2: Use the SQL request behavior identification feature to troubleshoot issues related to exceptional metrics based on the periods during which exceptions occurred. The red circles indicate unusually high CPU metrics. When such an exception occurs, the backend algorithm of DAS calculates the correlation between the SQL requests and the CPU metrics based on SQL request behaviors. Then, DAS recommends the SQL request category that is most relevant to the exceptional metrics.
- Log on to the DAS console.
- In the left-side navigation pane, click Instance Monitoring.
- On the Instance Monitoring page, find the instance that you want to manage and click its name. In the left-side navigation pane of the instance details page, click Autonomy Center. Select a time range and click Search. You can view the details about Exceptional Events, Optimization Events, Auto-Scaling Events, and Others in the specified time range.
- You can see the IOPS Usage, CPU Utilization, and Active Sessions metrics. Click Yellow Area 1, and then check the status of time series exception detection in the lower part of the page. Note Each row in Area 2 in the preceding figure represents the following meaning:
- The first row represents the exception degrees of the exceptional time series metric. The color varies based on the degree of exceptions. The red, yellow, and blue colors indicate the exception degrees of events in descending order.
- The second row represents the updates and slow SQL optimization events that correspond to time series exception detection.
- Click the Exception Detection area. In the exception detection list that appears, click the Exception Detection tab and view the exception causes in the Anomaly Analysis drop-down list. In the Exceptional Metric section, select a metric category and view the time distribution of the exceptional
metrics of this category. Note The preceding figure shows that the small red point behind the auto anchor represents the highest exception degree. The red, yellow, and blue colors indicate exception degrees of events in descending order.
- In the Anomaly Analysis section, click View Related SQL Statements in the Related Metrics column next to an exception cause. On the SQL Request Behavior Analysis page, set the Metrics and Associated SQL Metrics parameters and click Analysis. Note The backend analyzes the top 1,000 SQL templates that have consumed the largest amount of time. The retrieval of full SQL data is time-consuming because the analysis feature depends on the full SQL data of DAS Professional Edition. The computing time slightly varies among users and is usually from 1 to 5 minutes.
- View the SQL Request Behavior Analysis chart and the analysis table. Note The first column shows that the value of cpu_usage Correlation is close to 1.0. This indicates that the category to which this SQL template belongs is most correlated with cpu_usage. In other words, this SQL metric is most correlated with the exceptional metrics.
The following table describes the parameters of SQL metrics.
Metric parameter Description Aggregate Images Metrics in each aggregate image are aggregated. cpu_usage Correlation A metric category whose value is closer to 1.00 is more correlated with the exceptional metrics. SQL Templates The number of SQL templates. For more information about the SQL templates, see Instructions. Average Time Consumed The average amount of time that is consumed by an SQL template during the suspicious period. Unit: seconds. Execution Percentage The percentage of the number of SQL template executions in relation to the total number of executions. Scanned Rows The total number of database table rows that are scanned by the rows_examined SQL template. Duration Percentage The percentage of the execution time of SQL templates in relation to the total execution time.
- Click View to obtain the correlation results of each SQL template.
The SQL template in the yellow area is closely correlated with mysql.cpu_usage, mysql.innodb_rows_read, and mysql.active_session. Therefore, this SQL template is the main source of the SQL requests that caused the CPU utilization spikes.
DAS helps aggregate large numbers of SQL requests. You can find that similar SQL templates have similar trends.
- The SQL requests spike for a short period but the spike time is different from the metric spike time. Therefore, this template category is not most correlated with the exceptional metrics.
- The trend of exceptional metrics. In this figure, the peak times of CPU utilization and active sessions coincide with the time when the requests that use SQL templates of a specific category suddenly decreased. This indicates that the requests that use the SQL templates of this category were blocked when the number of active sessions spiked.