PolarDB:SQL Explorer

Features

DAS integrates Search, SQL Explorer, Security Audit, and Traffic Replay and Stress Testing based on full request data collection. These features help you retrieve SQL details, troubleshoot performance issues, identify high-risk sources, and assess scaling needs.

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

• SQL Explorer: Diagnoses SQL health, troubleshoots performance issues, and analyzes service traffic. SQL Explorer.

  • SQL Review: Identifies suspicious SQL statements across database instances and provides optimization suggestions. SQL Review.

  • Traffic Replay and Stress Testing: Validates whether your instance specifications can handle traffic peaks. Traffic Replay and Stress Testing.

  • The Security audit feature identifies risks such as high-risk SQL, SQL injection, and new access sources. Security Audit.

  • The Transaction Analysis feature displays transaction types, counts, and details for a specific thread within a time period, helping you analyze and optimize database performance. Transaction Analysis.

  • Quick Transaction Analysis: Identifies the start and end statements of a transaction to determine whether it was committed or rolled back. Quick Transaction Analysis.

Supported regions

SQL Explorer and Audit requires DAS Enterprise Edition. Supported regions vary by edition. Databases and regions supported by different editions.

Impact

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

Usage notes

To use the Search feature, RAM users must have the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission. Create and manage RAM users.

Billing

Enable SQL Explorer and Audit

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where the cluster is located, 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, follow the on-screen instructions to enable it.

4. Click a tab to view the corresponding feature.

   • Search (Audit): Query and export SQL statements with details 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. View and export SQL details in the Full Request Statistics area and export the information.

       Note

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

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

     • Display by Comparison: Compare SQL Explorer results across time points based on Execution Duration Distribution, Execution Duration, and Executions. You can also view detailed comparisons in the Requests by Comparison area.

     • Source Statistics: View source information for SQL statements within a selected time range.

     • SQL Review: Analyzes cluster workload against a baseline and provides index optimization suggestions, SQL rewrite suggestions, and insights into top SQL, new SQL, failed SQL, execution changes, performance degradation, 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.

     • Traffic Replay and Stress Testing: Replays traffic and runs stress tests before service peaks or schema changes to validate cluster specifications and reduce go-live risk.

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

     • Transaction Analysis: Analyzes transaction details for a selected thread and time range using DAS Enterprise Edition V3 hot storage data, and plots transaction type trend charts.

Modify SQL log storage duration

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where the cluster is located, 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

   • With DAS Enterprise Edition V3, you can set storage durations per sub-feature.

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

Disable SQL Explorer and Audit

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where the cluster is located, 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.

   For DAS Enterprise Edition V3, clear all SQL Explorer and Audit feature check boxes.

   Note

   • If audit log collection is enabled for PolarDB for MySQL in Simple Log Service CloudLens for PolarDB, the system also enables SQL Explorer for the PolarDB for MySQL cluster. You must also disable audit log collection for this PolarDB for MySQL cluster. Enable the data collection feature.

   • Disabling SQL Explorer deletes all audit logs. Export your records first. Export SQL log records.

4. Click OK.

View audit log size and consumption

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

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

3. On the Bill Details tab, click the Detailed Bills tab, and then search by Instance ID. View the cost details where the Billable Item column is sql_explorer.

   

Migrate to the new version

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where the cluster is located, 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 data between Enterprise Editions

Enterprise Edition V2 uses a hybrid hot-cold storage architecture for lower costs. Enterprise Edition V3 builds on this with itemized per-feature billing for further savings.

If your cluster supports Enterprise Edition V3, migrate from V1 or V2 to reduce costs. How do I migrate data between different DAS Enterprise Editions?

FAQ

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

  No. SQL Explorer supports only pay-as-you-go billing. subscription and resource plans are not supported.

• In the Full Request Statistics area of SQL Explorer, what does the logout! statement mean?

  logout! indicates a disconnection. The logout! duration is the idle time from the last interaction to the logout! event. 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 because the interactive_timeout or wait_timeout duration was exceeded.

• In the Source Statistics of SQL Explorer, why does an access source of % appear?

  This can occur when using a stored procedure, as shown below:

  Note

  This example uses a PolarDB for MySQL cluster with a test account (test_user) and database (test_db).

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

  2. Connect to the cluster using the test account. Connect to a cluster from the command line.

  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 cluster using a privileged account. Create a privileged account and Connect to a cluster from the command line.

  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 displayed in the audit log list inconsistent with the one in the SQL statement?

  The log list shows the database name from the session context, while the SQL statement contains the database specified by the user (for example, in cross-database queries). These may differ.

• Does enabling SQL Explorer and Audit affect database performance? If so, what is the impact?

  Yes, but the impact is minimal.

  Resource usage:

  • CPU and memory: Negligible.

  • Storage space: Audit data is stored by DAS and does not consume cluster storage.

  • Network: No impact.

  • Disk performance: No impact. DAS stores audit data externally.

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

  • Troubleshooting procedure:

    1. Find the 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

       Filter by Thread ID first. Add other conditions if the results are too large.

       

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

    Note

    This procedure assumes the UPDATE is the final business logic step. If subsequent modifications or deletions occur, continue investigating related SQL operations.

  • Scenarios:

    • AUTOCOMMIT is disabled and the SQL statement is not committed: AUTOCOMMIT was disabled and no explicit COMMIT was issued, so the data was not changed.

    • Thread ROLLBACK: All operations in a transaction succeed or fail together. Check whether a ROLLBACK occurred after this request.