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 Edition).
The instance needs to have the database audit feature enabled. Otherwise, the following error will be displayed when you create an SQL insight task. You can click Quick Activation to enable and configure database SQL audit.
Log in to the DBbrain Console, select Diagnosis and Optimization on the left sidebar, and select Audit Log Analysis at the top. For insight view, you can select and display QPS or the number of slow queries.
Creating a task
- Select your desired time period and click Create Audit Task on the top-right corner of the view.
- Select the task start time and duration 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 table, the SQL data will change accordingly, and only the SQL analysis result of the selected time period will be displayed.
- You can select “SQL Type” or click a legend to filter 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.
- 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.
- 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.