SQL Explorer

Features

DAS integrates Search, SQL Explorer, Security Audit, and Traffic Replay and Stress Testing based on full request data and security audit. These features help you retrieve SQL details, troubleshoot performance issues, identify high-risk sources, and validate whether cluster specifications need scaling to handle traffic peaks.

• Search feature: Queries and exports SQL statements and related information such as database, status, and execution time. For more information, see Audit.

• SQL Explorer feature: Diagnoses SQL health, troubleshoots performance issues, and analyzes business traffic. For more information, see SQL Explorer.

  • SQL Review feature: Provides global SQL load analysis to help identify suspicious SQL statements across database instances and provides optimization suggestions. For more information, see SQL Review.

  • Traffic Replay and Stress Testing feature: Provides traffic replay and stress testing to help validate whether your instance specifications need scaling to handle traffic peaks. For more information, see Traffic playback and stress testing.

  • Security audit feature: Automatically identifies risks such as high-risk SQL, SQL injection, and new access sources. For more information, see Security Audit.

  • Transaction Analysis feature: Displays transaction types, counts, and details for a specific thread within a specified time period, helping you analyze and optimize database performance at the transaction level. For more information, see Transaction analysis.

  • Quick Transaction Analysis feature: Identifies the start and end statements of the transaction containing the target SQL to determine whether it was committed or rolled back. For more information, see Quick transaction analysis.

Supported regions

SQL Explorer and Audit requires DAS Enterprise Edition. Supported regions vary by edition. For more information, see Supported databases and regions.

Impact

SQL Explorer records all DQL, DML, and DDL operations. This data is output by the database kernel with minimal CPU overhead.

Usage notes

To use the Search feature as a RAM user, you must grant the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission to the RAM user. For more information, see Create and manage RAM users.

Billing

Enable SQL Explorer and Audit

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

2. In the left-side navigation pane, choose Logs and Audit > SQL Explorer.

3. Click Enable SQL Explorer.

   Note

   If DAS Enterprise Edition is not enabled for your Alibaba Cloud account, follow the on-screen instructions to enable it.

4. Click a tab on the right side to view the corresponding information.

   • Search (Audit): Queries and exports SQL statements and related information such as database, status, and execution time.

   • SQL Explorer:

     • Display by Time Range: Select a time range to view SQL Explorer results, including Execution Duration Distribution, Execution Duration, and Executions for all SQL statements within the selected time range. You can also view and export detailed SQL information in the Full Request Statistics area.

       Note

       • You can export up to 1,000 SQL logs at a time. For a larger time range or more SQL logs, use the Search (Audit) feature.

       • After enabling SQL Explorer, wait 30 minutes before viewing Audit logs.

     • Display by Comparison: Select time points to compare SQL Explorer results. You can view comparison results for Execution Duration Distribution, Execution Duration, and Executions. You can also view detailed comparison results in the Requests by Comparison area.

     • Source Statistics: Select a time range to view source information for all SQL statements within that period.

     • SQL Review: Analyzes cluster workload against a baseline interval and performs deep analysis on SQL statements, displaying index optimization suggestions, SQL rewrite suggestions, TOP SQL, new SQL, failed SQL, SQL feature analysis, execution changes, performance degradation SQL, and top traffic tables.

     • Related SQL Identification: Select a metric and click the Analysis button. After 1 to 5 minutes, the system identifies SQL statements with trends most similar to the selected metric and displays their details.

     • Traffic Replay and Stress Testing: Before upcoming short-term traffic peaks or database schema changes (especially index changes), use traffic replay and stress testing to validate whether cluster specifications need scaling and verify actual performance in real business scenarios, reducing the risk of failures after deployment.

     • Security audit: Automatically identifies risks such as high-risk operations, SQL injection, and new access sources.

     • Transaction Analysis: Based on DAS Enterprise Edition V3 hot storage data, analyzes transaction details for a selected thread and time range, performs statistical analysis, and plots trend charts for different transaction types.

Modify SQL log storage duration

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

2. In the left-side navigation pane, choose Logs and Audit > SQL Explorer.

3. In the upper-right corner, click Service Settings.

4. Modify the storage duration and click OK.

   Note

   • If you have enabled DAS Enterprise Edition V3, you can set different storage durations for each sub-feature.

   • SQL Explorer and Audit data is stored by DAS and does not consume your cluster storage space.

Disable SQL Explorer and Audit

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

2. In the left-side navigation pane, choose Logs and Audit > SQL Explorer.

3. Click Service Settings to disable SQL Explorer and Audit.

   If you have enabled DAS Enterprise Edition V3, clear all SQL Explorer and Audit feature check boxes.

   Note

   • If you have enabled audit log collection for PolarDB for MySQL in Simple Log Service CloudLens for PolarDB, the system automatically enables SQL Explorer for the corresponding PolarDB for MySQL. Therefore, you must also disable the audit log collection feature for this PolarDB for MySQL. For more information, see Enable data collection.

   • After SQL Explorer is disabled, SQL audit logs are deleted. We recommend that you export SQL records before disabling SQL Explorer. For more information, see Export SQL log records.

4. Click OK.

View audit log size and consumption details

1. Log on to the Alibaba Cloud Management Console. In the upper-right corner of the page, choose Expenses.

2. In the left-side Expenses and Costs navigation pane, choose Bill > Bill Details. View the cost details where the Billable Item column is sql_explorer.

3. On the Bill Details page, search by Instance ID. View the cost details where the Billable Item column is sql_explorer.

Migrate to the new version

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

2. In the left-side navigation pane, click Logs and Audit>SQL Explorer.

3. In the Upgrade SQL Explorer To 'SQL Explorer and Audit' dialog box, click Upgrade.

Migrate SQL Explorer and Audit data between Enterprise Editions

Enterprise Edition V2 upgrades the underlying storage architecture with hybrid hot-cold storage for cost efficiency. Enterprise Edition V3 builds on this with per-feature billing for more flexible and lower-cost usage.

If your database cluster supports Enterprise Edition V3, you can migrate data from DAS Enterprise Edition V1 or V2 to V3 for more favorable pricing. For more information, see How do I migrate data between versions of DAS Enterprise Edition?

FAQ

• Can SQL Explorer fees be offset by a resource plan?

  No. SQL Explorer supports only Pay-as-you-go billing. Subscription and resource plan offset is not supported.

• What does the logout! statement mean in the Full Request Statistics area of SQL Explorer?

  logout! indicates a disconnection. The logout! duration is the time difference from the last interaction to when the logout! event occurs, which can be understood as the idle duration of the connection. A value of 1158 in the Status column indicates a network disconnection. Possible causes include:

  • The client connection timed out.

  • The server disconnected abnormally.

  • The server connection was reset (exceeding the interactive_timeout or wait_timeout duration).

• Why does an access source of % appear in the Source Statistics of SQL Explorer?

  This can occur when you use stored procedures. The following example demonstrates this scenario:

  Note

  In the following example, the database cluster is PolarDB for MySQL, the test account is test_user, and the test database is test_db.

  1. In the PolarDB console, create a standard account and an authorized database. For more information, see Create a standard account.

  2. Connect to the database cluster using the test account via the command line. For more information, see Use the command line to connect to the cluster.

  3. Switch to the test database and create the following stored procedure.

     -- Switch to the test database
     USE test_db;

     -- Create a stored procedure
     DELIMITER $$
     DROP PROCEDURE IF EXISTS `das` $$
     CREATE DEFINER=`test_user`@`%` PROCEDURE `das`()
     BEGIN
         SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();
     END $$
     DELIMITER ;

  4. Connect to the database cluster using a privileged account. For more information, see Create a privileged account and Use the command line to connect to the cluster.

  5. Call the stored procedure.

     -- Switch to the test database
     USE test_db;

     -- Call the stored procedure
     CALL das();

     -- Call result
     +-----------+-----------+---------+---------+---------+------+-----------+-------------------------------------------------------------------------+
     | ID        | USER      | HOST    | DB      | COMMAND | TIME | STATE     | INFO                                                                    |
     +-----------+-----------+---------+---------+---------+------+-----------+-------------------------------------------------------------------------+
     | 269660316 | test_user | %:46182 | test_db | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
     +-----------+-----------+---------+---------+---------+------+-----------+-------------------------------------------------------------------------+

• Why is the database name in the audit log list inconsistent with the one in the SQL statement?

  The database name displayed in the log list is obtained from the session context, while the database name in the SQL statement is specified by the user, depending on user input or query design, such as cross-database queries and dynamic SQL. These may differ.

• Does enabling SQL Explorer and Audit affect database performance? How significant is the impact?

  Yes, but the impact is minimal and almost imperceptible.

  Resource usage details:

  • CPU and memory: Extremely low consumption, almost negligible.

  • Storage space: Used primarily for storing audit information, but SQL Explorer and Audit provided by DAS Enterprise Edition uses DAS-side storage and does not consume cluster storage space.

  • Network: No impact on network performance.

  • Disk performance: No impact on disk performance because audit data is stored on the DAS side, not on the database cluster disks.

• An UPDATE statement was executed in a cluster. The audit log shows that one row was affected. However, the data in the table was not updated and remains in the pre-modification state. How can this issue be diagnosed and resolved?

  • Troubleshooting procedure:

    1. Find this SQL statement in the audit log and obtain its Thread ID. Then click Enable Advanced Search and search by the Thread ID. Check whether AUTOCOMMIT was disabled for the current thread and, if so, whether an explicit COMMIT was issued.

       Note

       We recommend filtering by Thread ID only. If too many logs are returned, combine other conditions to reduce the log volume for easier analysis and problem identification.

    2. If the preceding method fails to identify the issue, consider restoring the database and parsing logs to confirm whether a successful modification record exists.

    Note

    This procedure assumes the UPDATE statement is the final step of the business logic. After this modification, the data remains unchanged, so no further checks are needed for subsequent modifications or deletions. If subsequent modifications occur, continue investigating related SQL operations that may have changed the data.

  • Problem scenarios:

    • AUTOCOMMIT disabled and SQL not committed: The troubleshooting procedure reveals that AUTOCOMMIT was disabled during execution and no explicit COMMIT was issued afterward, so the data was not changed.

    • ThreadROLLBACK: All operations in the same transaction session either all succeed or all fail. If a rollback occurs, all operations are rolled back. Check the troubleshooting procedure to determine whether a ROLLBACK occurred after this request.