Docs

Database Workshop

Observation and diagnosis

SQL insights and auditing management

Database Workshop (DBW) provides SQL insights and auditing, which helps you improve database performance and stability by capturing, reviewing, and analyzing all SQL statements. This topic describes the precautions, advantages, and operation steps for enabling, disabling, and viewing SQL insights and auditing.

Background

SQL insights and audit includes database audit and full SQL insights. SQL insights and auditing is based on database audit. Therefore, when full SQL insight is enabled, database audit is enabled by default.

The SQL insights and auditing function supports automatically recording and analyzing SQL statements generated by the database kernel, including information such as the execution account, IP address, and implementation details. SQL statement types include SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, RENAME, TRUNCATE, and OTHER (all other SQL types).

Currently, database audit has been launched in all regions, while the full SQL insight feature is only available in China regions China (Beijing), China (Shanghai), China (Guangzhou), and overseas region Asia Pacific (Johor). For billing information about SQL insights and auditing, see Billing items and prices.

Advantages

Low CPU consumption: SQL statements are generated by the database kernel, resulting in very low CPU resource consumption on the system.

Provide a comprehensive perspective: SQL insights and auditing can capture and analyze all SQL statements running in the database instance, providing you with a comprehensive perspective that can be used to identify performance bottlenecks in the database instance.

Problem locating and diagnosis: By analyzing real-time monitoring and historical trends, SQL insights and auditing can help you locate and diagnose problems with database instances, such as slow queries and concurrent deadlocks.

Optimize the database structure: The SQL insight and auditing feature provides information such as which tables are frequently used and which SQL statements consume the most resources, thereby helping you improve the structure of your database.

Improve the availability and stability of the database: SQL insight and auditing can help you better understand the overall operating status of the database instance, thereby improving the availability and stability of the database.

Precautions

Feature overview and dependencies

SQL insights and auditing generate operation information based on database kernel analysis. Data accuracy may be affected by the following factors:

Inherent limitations of kernel analysis itself;

When the data volume for a query is too high, it may lead to inaccurate statistics.

This feature is only supported for MySQL instances with a kernel version ≥ 20240415.

You can use the command SELECT @@byte_rds_version; to check the current kernel version. Manual upgrade is required for older versions.

Enable and disable mechanism

Enable conditions: Automatically enabled only when instance load is low; enabling may be delayed under high load.

Correlation:

When SQL insights and auditing is enabled, V2 auditing is automatically enabled.

When this feature is disabled, V2 auditing is also disabled.

Impact of disabling:

All stored SQL audit data will be completely cleared.

It is recommended to manually download important data before closing to avoid data loss.

Instance deletion: Automatically disables this feature and clears related data.

Storage resources

Storage location: SQL audit data is stored by DBW and does not occupy the storage space of the database instance itself.

Hierarchical storage policy

Audit logs beyond the standard retention period are automatically moved to infrequent access storage:

Infrequent access storage is much cheaper than standard storage. It is designed for long-term archiving and historical logs that are rarely accessed.

While there may be minor Retrieval fees when accessing the data, the total cost remains far lower than maintaining it in standard storage over the long term.

Ensures data accessibility while effectively lowering long-term storage expenses.

SQL statement length limit

The maximum SQL length recorded depends on the minor version of the instance kernel:

Kernel version requirements	Maximum recording length	Description
≥ MySQL 5.7.32_20250615 or MySQL 8.0.32_20250615	8KB (8192 bytes)	If the relevant parameter ≤ 8K, it is limited by the parameter value. If the parameter > 8K, the upper limit remains 8K. The actual recordable length is slightly less than the set value (due to the system adding a prefix).
Below the above versions	4096 bytes	If the relevant parameter ≤ 4096, it is limited by the parameter value. If the parameter > 4096, the upper limit remains 4096. The actual length is also slightly less than the set value.

tip

You can view the minor version on the Instance information page. For more information, see the Minor version release notes.

Suggestions for use

Upgrade to a supported version to enable longer SQL recording (up to 8K).

Under high load conditions, there may be a delay in enabling the feature. This is normal behavior.

Before shutting down or deleting an instance, make sure to export critical audit data.

Download filesDownload file

The full SQL file downloaded by the system is compressed in the .gz (Gzip) format. For macOS users, please extract the downloaded file using the steps below:

Decompress using the built-in gzip or gunzip command-line tools:Open the Terminal and run the gzip or gunzip commands provided by the system to extract the files. The command format is gzip -d your_filename.gz or gunzip your_filename.gz.

To decompress the file using the tar tool: Download and install the tar tool to decompress the file. The command format is tar -xz --force-local -f file_name.gz.

Billing

Billing rules:

During the promotion period: Free storage for 1 day.

After the promotional period: Billed based on actual usage. For more information, see the Billing rules.

Prerequisites

An instance for RDS for MySQL has been created. For detailed steps, refer to Creating an instance.

If the target instance has security control enabled, you need to apply for operation and maintenance read-only or read/write permissions for the current account. For more information, refer to Enabling instance security control (RDS for MySQL), and Applying for operation permissions (RDS for MySQL).

Enable SQL insights and auditing

warning

After enabling SQL insights and auditing, if the current MySQL instance load is high, SQL insights and auditing will not be enabled immediately; it will only be enabled when the instance load is low.

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the upper-left corner of the Operation observability page, click the drop-down icon and select the target instance.

tip

You can switch to the target instance by searching for the instance ID.

On the target instance page, click SQL insights and auditing.

In the SQL insights and auditing tab, set the following parameter information:

Parameter	Description
Features to be enabled	Since the SQL details in the full SQL insight record the V2 version of database audit information, when you enable full SQL insight, the V2 version of database audit is enabled by default. You can also only enable database audit to enable the SQL details feature of full SQL insight. For more information about the V2 version of database audit, refer to Database audit management. tip For the billing standards for database audit and full SQL insight, see Billing items and prices.
Standard storage duration	Select the retention period for Audit V2 version logs as needed. Currently supported options are 1 day (default), 7 days, 30 days, 180 days, 1 year, or 3 years. A custom retention period is also supported, with a maximum of 1095 days. tip The storage space for SQL data is provided by DBW and does not occupy the storage space of the database instance.
Infrequent-access storage	Enable infrequent-access storage for instances on demand to optimize storage costs. The low-frequency storage duration feature allows infrequently accessed but undeletable data to be stored securely at a lower cost, achieving a balance between cost and performance. Disabled: If disabled, DBW will clear SQL audit logs that exceed the standard storage duration. It is recommended to download and save SQL audit logs locally for future queries. Enabled: If enabled, DBW will transfer SQL audit logs exceeding the standard storage duration to low-frequency storage. Data in low-frequency storage will have reduced retrieval efficiency.
Infrequent-access retention period	Set the infrequent-access retention period as needed. Currently, you can select 30 days (default), 60 days, 90 days, 180 days, 1 year, or 3 years. You can also customize the infrequent-access retention period, up to 3648 days.
SQL storage duration	The default storage duration for SQL data is the standard storage duration plus the infrequent-access storage duration. The maximum storage period supported by the system is 3650 days. This upper limit is fixed and cannot be modified.
Storage SQL type	Select SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, RENAME, TRUNCATE, and OTHER (all other SQL types) as needed. You can also Select all SQL types with one click.
Tag	Tags are used to mark resources to facilitate searching and resource aggregation. You can add tags to the SQL insight and auditing feature of an instance in the following two ways: Manual addition: Click the pen icon, and in the Edit tag dialog box, set the tag key and tag value. Configuration must follow the rules below: Naming restrictions: Tag keys cannot start with volc: (case-insensitive, such as Volc:, VOLC:, etc. are not allowed). Character support: Supports uppercase and lowercase letters, numbers, spaces, and specific English symbols (_, ., :, /, =, +, -, @). Length limit: Must be between 1 and 128 characters. Encoding format: Supports Unicode in UTF-8 encoding. Copy instance tag: Copies the current instance tags directly to the SQL audit fee tags. If the instance tags change later, the SQL audit fee tags are not automatically synchronized. You need to manually copy them again. Click Copy instance tag. In the Copy Instance Tag dialog, click Copy instance tag to copy the instance tags to the SQL audit tags. If a tag key from the instance conflicts with an existing SQL audit fee tag, the conflicting item is skipped during copying, and only the remaining tags are copied. tip Each time, you can bind up to 20 tags, and each instance can bind up to 50 tags. To centrally manage tags, refer to Tag management. The tag keys of the same resource can not be repeated, and one tag key can only have one value.
Agreement	Read and check I have read and agree to Database Workshop (DBW) terms of service.

Click Enable database auditing & SQL insights.

warning

After enabling full SQL insights, if the MySQL instance is under high load, full SQL insights will not be enabled immediately, and will only be enabled when the instance load is low.

Set SQL insights and auditing

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the upper-right corner of the page, locate the drop-down list and select the target database type - RDS for MySQL.

In the upper-right corner of the page, locate the drop-down list and select the target instance.

On the Operations observability page, click SQL insights and auditing.

On the SQL insights and auditing tab, click Service settings.

In the Service settings dialog box, you can perform the following operations:

Parameter	Description
Disable SQL insight and auditing	When you disable SQL insights and auditing, you simultaneously disable database audit and full SQL insights. tip After you disable SQL insights and auditing, all stored data will be cleared. If necessary, download all data locally before it is cleared, otherwise it may result in data loss.
Full SQL insight	SQL insights and auditing includes two features: database audit and full SQL insight. In SQL insights and auditing, you can enable or disable the Full SQL insight feature as needed. Click Enable to enable the Full SQL insight feature. Click Close to disable the full SQL insight feature. Currently, only full SQL insight can be disabled separately. The database audit feature can not be disabled individually. When disabling full SQL insight, all stored data will be cleared. We recommend downloading the data locally before disabling it to avoid potential data loss. tip After SQL insight is disabled, new data will not be collected and stored. Existing stored data will not be cleared immediately. DBW still cleans up SQL insight data according to the standard storage duration previously set. Enabling or disabling full SQL insight will change the cost. For more information, refer to Billing items and prices (RDS for MySQL).
Standard storage duration	Select the retention period for Audit V2 version logs as needed. Currently supported options are 1 day (default), 7 days, 30 days, 180 days, 1 year, or 3 years. A custom retention period is also supported, with a maximum of 1095 days. tip The storage space for SQL data is provided by DBW and does not occupy the storage space of the database instance.
Infrequent-access storage	Enable infrequent-access storage for instances on demand to optimize storage costs. The low-frequency storage duration feature allows infrequently accessed but undeletable data to be stored securely at a lower cost, achieving a balance between cost and performance. Disabled: If disabled, DBW will clear SQL audit logs that exceed the standard storage duration. It is recommended to download and save SQL audit logs locally for future queries. Enabled: If enabled, DBW will transfer SQL audit logs exceeding the standard storage duration to low-frequency storage. Data in low-frequency storage will have reduced retrieval efficiency.
Infrequent-access retention period	Set the infrequent-access retention period as needed. Currently, you can select 30 days (default), 60 days, 90 days, 180 days, 1 year, or 3 years. You can also customize the infrequent-access retention period, up to 3648 days.
SQL storage duration	The default storage duration for SQL data is the standard storage duration plus the infrequent-access storage duration. The maximum storage period supported by the system is 3650 days. This upper limit is fixed and cannot be modified.
Storage SQL type	Select SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, RENAME, TRUNCATE, and OTHER (all other SQL types) as needed. You can also Select all SQL types with one click.
Tag	Tags are used to mark resources to facilitate searching and resource aggregation. You can add tags to the SQL insight and auditing feature of an instance in the following two ways: Manual addition: Click the pen icon, and in the Edit tag dialog box, set the tag key and tag value. Configuration must follow the rules below: Naming restrictions: Tag keys cannot start with volc: (case-insensitive, such as Volc:, VOLC:, etc. are not allowed). Character support: Supports uppercase and lowercase letters, numbers, spaces, and specific English symbols (_, ., :, /, =, +, -, @). Length limit: Must be between 1 and 128 characters. Encoding format: Supports Unicode in UTF-8 encoding. Copy instance tag: Copies the current instance tags directly to the SQL audit fee tags. If the instance tags change later, the SQL audit fee tags are not automatically synchronized. You need to manually copy them again. Click Copy instance tag. In the Copy Instance Tag dialog, click Copy instance tag to copy the instance tags to the SQL audit tags. If a tag key from the instance conflicts with an existing SQL audit fee tag, the conflicting item is skipped during copying, and only the remaining tags are copied. tip Each time, you can bind up to 20 tags, and each instance can bind up to 50 tags. To centrally manage tags, refer to Tag management. The tag keys of the same resource can not be repeated, and one tag key can only have one value.

Click OK.

View full SQL information

tip

The SQL insight and auditing feature supports not only viewing the trend of all SQL and SQL templates, but also SQL details.

When an SQL statement is executed successfully, it only indicates that no exceptions occurred during SQL statement execution and that the execution was completed successfully. For example, in the event of a transaction rollback, if a single SQL in the transaction is executed successfully without any exceptions, the SQL statement is considered to have been executed successfully.

When you query SQL insight and auditing information, you can set any query start time within the SQL storage duration of the database instance to view up to 24 hours of data. If you want to query SQL records in a larger time range, we recommend that you export the records and then query them. The export function asynchronously exports logs and is suitable for queries within a large time range.

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the upper-left corner of the Operation observability page, click the drop-down icon and select the target instance.

tip

You can switch to the target instance by searching for the instance ID.

On the target instance page, click SQL insights and auditing.

On the SQL insights and auditing tab, view full SQL insight information, SQL details, and write analysis, as follows:

Full SQL insight

SQL audit

Write analysis

On the SQL insight and auditing tab page, choose the Full SQL insight sub tab to view the trend of SQL and the SQL template list in the instance. You can also select an instance node to view the full SQL insight information of the specified node as needed. The details are as follows:

View full SQL insight information by interval

On the SQL insight and auditing tab page, choose the Full SQL insight sub tab, select the time range of the full SQL insight to be viewed as needed. You can set any query start time within the SQL storage duration range of the database instance to view data for up to 24 hours. Because SQL insight records all database behaviors, a large number of SQL statements are recorded. If you select a time range that is too large for online queries, the query results will not be returned for a long time, or the query will time out.

View the SQL development trend and SQL template list of the instance in the selected time period, as follows:

Full SQL trends: In the specified time period, you can view the performance analysis and statistics of SQL execution time. In the Full SQL trends area, you can view the following information:

Time-consuming execution phase: Supports selecting and viewing the development trends of metric items at each point in time, including CPU Time, ThreadScheduleTime, ThreadWaitTime, RowLockWaitTime, MDLWaitTime, LatchWaitTime, SocketSendTime, CCLWaitTime, and TRXCommitTime.

Execution time distribution: Displays the execution time distribution of all SQLs within the selected time period, dividing the execution time into 7 intervals, calculated once per minute, as follows:

(0,1]ms: Indicates the percentage of SQL execution times that satisfy 0ms < execution time ≤ 1ms.

(1,2]ms: Indicates the percentage of SQL execution times that satisfy 1ms < execution time ≤ 2ms.

(2,3]ms: Indicates the percentage of SQL execution times that satisfy 2ms < execution time ≤ 3ms.

(3,10]ms: Indicates the percentage of SQL execution times that satisfy 3ms < execution time ≤ 10ms.

(10,100]ms: Represents the percentage of SQL execution times that satisfy 10ms < execution time ≤ 100ms.

(0.1,1]s: Represents the percentage of SQL execution times that satisfy 0.1s < execution time ≤ 1s.

>1 s: Represents the percentage of SQL execution times that satisfy 1s < execution time.

Execution times: A statistical calculation of the number of SQL executions within the execution time period. You can view the trend of total executions at each point in time.

SQL template list: On the SQL template list pane, you can filter by dimensions including Database name, Table name, SQL type, Average rows scanned, and Execution times.

After filtering, the list displays a wide range of performance metrics including average SQL execution time, SQL time (ms), SQL template, SQL type, database name, table name, total SQL execution time, execution times, total CPU time, average CPU time, average innoDB lock wait time, average MDL lock wait time, average Latch wait time, average transaction commit time, average SQL throttling wait time, average socket send time, average thread scheduling wait time, average thread wait time, average rows affected, average rows scanned, average rows returned, average bytes sent, average logical page reads, average IO read bytes, average number of pages read by I/O, average number of disk temporary tables.

Click an SQL template to view its execution times, execution time, rows affected, rows returned, SQL template, SQL text, and SQL optimization in the SQL details control panel. You can also click the favorite icon to easily view the target SQL template later.

tip

Total SQL execution time = Average SQL execution time * Execution times.

Total CPU time = Average CPU time * Execution times.

In the SQL optimization suggestions dialog box, view SQL optimization suggestions, including the executed SQL statement, Execution plan, and Optimization suggestions including Index optimization and SQL rewrite.

Favorite SQL template: On the SQL template list pane, click the favorite icon in the Actions column of the target SQL template to favorite the SQL template.

View the favorited SQL template: On the SQL template list pane, click My favorite to view the SQL templates that you have favorited in all SQL template lists. You can also click the Actions column of the target SQL template to cancel the SQL template you favorited.

Compare and view full SQL insight information

On the Full SQL insight tab, select Time comparison, select the time range for which you want to compare the full SQL insight as needed, and view information such as Time-consuming execution phase, Execution time distribution, Execution times, and SQL template lists within the comparison time range.

tip

ThreadScheduleTime: Kernel-level runtime (RT) time.

You can also click View execution plan in the Actions column of the target SQL text, and view more information in the Execution plan dialog box.

Type	Description
SQL details	On the SQL details sub-tab, you can view SQL statement details and select the time range for which you want to view the full SQL details. You can set any query start time within the SQL storage duration of the database instance to view up to 24 hours of data. Because SQL insight records all database behaviors, a large number of SQL statements are recorded. If you select a time range that is too large for online queries, the query results will not be returned for a long time, or the query will time out. The details of the SQL statement include Execution time, SQL time (ms), Database name, SQL statement, SQL type, Session ID, User name, Client IP, table name, Start Time, CPUTime, RowLockWaitTime, MDLWaitTime, LatchWaitTime, TRXCommitTime, CCLWaitTime, SocketSendTime, ThreadScheduleTime, ThreadWaitTime, Number of rows affected, Rows scanned, Number of rows returned, Send bytes , Logical page reads, IO read bytes, Number of pages read by IO, Number of disk temporary tables, Log level, Log type. You can also click Advanced filtering to filter SQL statements that meet the conditions. tip You can also click View execution plan in the Actions column of the target SQL statement, and view more information in the Execution plan dialog box. In the keywords of the advanced filtering of SQL details, fuzzy search is supported to query the target SQL information. The fuzzy search is case-insensitive, and the keyword format is SQL keyword + , for example, select .
Search analysis	DBW provides log search and analysis capabilities, supporting filtering and screening of log data through search analysis statements, and performing statistical analysis on the filtering results. On the Search and analysis sub-tab, you can perform the following operations: View historical search and analysis results. On the query pane, click the History query icon on the left side of the search box to select and view historical search information. Search and analyze logs. The search time range defaults to 5 minutes. You can customize the search time range. Enter a search and analysis statement, or use the selection mode to add custom filters and analysis statements, then click Search. View the search and analysis results. The search and analysis results can be displayed as raw logs and statistical charts, and support customizing more display conditions to meet your result display needs. For more information on log search and analysis, refer to Searching and analyzing SQL logs. tip The search and analysis statement consists of a search statement and a SQL analysis statement, which are separated by a pipe (\|), indicating that data statistics and analysis are performed on the results that meet the search criteria. By default, the query analysis results are displayed in a table. You can also specify other statistical charts and add corresponding chart settings. The query results area also displays basic information about the current log analysis, including the total number of logs and the query result accuracy.

Through write analysis, you can comprehensively monitor and analyze database write operations.

On the Write analysis subtab, you can view write analysis for all SQL statements within a specified time frame. Select the time range for viewing full SQL write analysis. You can set any start time within the SQL retention period of the database instance to view data up to 24 hours. Because SQL insight records all database behaviors, a large number of SQL statements are recorded. If you select a time range that is too large for online queries, the query results will not be returned for a long time, or the query will time out.

Write operations in the specified database and table include Table name, Database name, Execution times, SQL average time, Total DML execution time, DML execution times, Average DML execution time, DML average rows scanned, Average number of rows affected for DML, DML average InnoDB row lock wait time, Average MDL Lock Wait Time for DML, Maximum DML execution time, Total DDL execution time, DDL execution times, Average DDL execution time, DDL average rows scanned, Average number of rows affected for DDL, DDL average InnoDB row lock wait time, Average MDL Lock Wait Time for DDL, and Maximum DDL execution time. At the same time, you can also click the table name of the target table to view more information about the table, as shown below:

DML: On the DML sub-tab of the target table control panel, you can view the DML details executed in the table within the specified time, including the Execution time(s), Total time, and Rows affected of the DML.

DDL: On the DDL sub-tab of the target table control panel, you can view the DDL details executed in the table within the specified time, including the Execution time(s), Total time, and Rows affected of the DDL.

SQL template lists: In the SQL template area of the target table control panel, you can view the average SQL execution time, SQL time(ms), SQL template, SQL type, database name, table name, total SQL execution time, execution times, total CPU time, average CPU time, average innoDB lock wait time, average MDL lock wait time, average Latch wait time, average transaction submit time, average SQL throttling wait time, average Socket send time, average thread scheduling wait time, average thread wait time, average rows affected, average rows scanned, average number of rows returned, average bytes sent, average logical page reads, average IO read bytes, average number of pages read by IO, and average number of disk temporary tables of each SQL template in the table within the specified time.

Click SQL template. In the SQL details control panel, view the Execution time(s), Execution time, Rows affected, Rows returned, SQL template, and SQL text of the table-level SQL template.

Download full SQL

tip

To view or download the exported log file, click the download icon, and then select Export history.

The full SQL file downloaded by the system is compressed in the .gz (Gzip) format. For macOS users, please extract the downloaded file using the steps below:

Decompress using the built-in gzip or gunzip command-line tools:Open the Terminal and run the gzip or gunzip commands provided by the system to extract the files. The command format is gzip -d your_filename.gz or gunzip your_filename.gz.

To decompress the file using the tar tool: Download and install the tar tool to decompress the file. The command format is tar -xz --force-local -f file_name.gz.

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the upper-left corner of the Operation observability page, click the drop-down icon and select the target instance.

tip

You can switch to the target instance by searching for the instance ID.

On the SQL insights and auditing tab of the target instance, download SQL data as needed:

Download all TOP SQL

Download all full SQL details

Download full SQL write analysis in a table

In the SQL template area on the Full SQL insight sub-tab, click the download icon.

In the Export all Top SQL dialog box, select the database instance Field to be exported as needed.

Click OK.

FAQs

Q: What is the relationship between database audit, SQL insights and auditing, and full SQL insight?

A: The relationship between the three is as follows:

Function	Description
Database audit	The SQL detail in the database audit function is not the full SQL.
Full SQL insight	Full SQL insight can collect all SQLs and perform analysis based on all SQLs. With a greater focus on performance, see DBW's full SQL insights into analytics practices for more value.
SQL insights and auditing	SQL insights and audit includes database audit and full SQL insights. SQL insights and auditing is based on database audit. Therefore, when full SQL insight is enabled, database audit is enabled by default.

Q: In SQL insights and auditing, I have only enabled database audit. If I want to enable Full SQL insight, how do I do it?

A: You can enable full SQL insights through service settings. For more information, see Set SQL insights and auditing.

Last updated: 2026.08.04 11:16:29

Parameter	Description
File format	Select the format for downloaded logs. Currently, CSV and JSON formats are supported.
Export range	Select a log range to export as needed. The system supports Custom, Past 2 hours, Past 12 hours, and Past 24 hours.
Custom time period	Select the time range for log export. This parameter can be set only when you select Custom for the export range.