The audit log analysis (formerly SQL insight) feature can carry out in-depth SQL analysis for a database instance. It can statistically analyze, sample, and aggregate all SQL statements and their execution information (source, number of executions, execution duration, result set, scan set, etc.) based on the audit logs generated by the database within a specified time period.
The audit log analysis feature analyzes the SQL performance based on the execution plan result, comprehensive resource consumption, sizes of scan set and result set, and index usage rationality of the aggregated SQL statements. Then, it provides optimization suggestions for poorly performing SQL statements by taking into account the index conditions and database/table design. This document describes how to perform full SQL analysis and view analysis details.
Currently, audit log analysis is supported only for TencentDB for MySQL (excluding the basic single-node instances).
The instance needs to have the database audit feature enabled.
Log in to the DBbrain console and select Performance Optimization on the left sidebar. On the displayed page, select a database at the top and select the Audit Log Analysis tab. The QPS, slow queries, and CPU utilization of the selected database are displayed on this tab. After specifying the query period at the top of the diagnosis chart, you can drag the gray scroll bar below to stretch or compress the diagnosis chart to view fine-grained details during the period.
Creating a Task
- Select your desired time period and click Create Analysis Task at the top of the chart.
- Select the start time and interval (in minutes) for the task, and click Confirm.
- After the task is created, you can view the analysis result and delete the task in the task list. Click View SQL Analysis in the Operation column to access the SQL analysis page.
- On the SQL analysis page, you can display the view by SQL Type, Host, User, or SQL Code and specify a time period to expand the view and view data at specific points in time. The aggregated details and execution information (including the number of executions, the total, maximum, and minimum delay, and the total, maximum, and minimum number of affected rows) of SQL statements in the specified time period are displayed in the table below.
- If you select a time period and stretch it in the chart, the SQL data in the table will change accordingly, and only the SQL analysis result of the selected time period will be displayed. Click Reset in the upper right corner to reset the stretched chart.
- You can select SQL Type or click a legend to filter, and the SQL data that will change accordingly in the table. For example, if you want to view only SELECT requests, you can click other legends to gray them out.
- In the chart, click a point of the curve to view the monitoring data in the point of time, including the data of SELECT, INSERT, UPDATE, DELETE, REPLACE, and other requests.
- Click the SQL template on the target row, and the SQL statement details will be displayed on the right.
- On the analysis page, you can view and copy specific SQL statements and optimize them based on the provided optimization suggestion or description.
In the pop-up Analysis window, you can click Optimization Comparison in the upper right corner to view the SQL execution plan, index recommendations, the table structure, and the cost comparison before and after the SQL optimization which is illustrated in a visual chart.
The costs of optimized SQL statements are estimated based on the analysis of the statistics of database tables related to the statements, the OPTIMIZER_SWITCH configuration, and the index selectivity. A chart is used to visually show the decrease in costs of the optimized SQL statements. You can also compare the execution plans before and after the SQL optimization to further verify the optimization results.
- On the statistics page, you can view the statistical analysis and execution duration track of the specified SQL statement type by Host, User, or SQL Code.