SQL Explorer

Features

Database Autonomy Service (DAS) integrates features such as search, SQL Explorer, Security Audit, and Traffic Replay and Stress Testing based on full request data collection and security auditing. DAS helps you retrieve the details of SQL statements, troubleshoot performance issues, identify high-risk sources, and determine when to scale out your cluster to effectively handle traffic peaks.

• Search: Queries and exports SQL statements and their corresponding information, such as the database, status, and execution duration. For more information, see Audit.

• SQL Explorer: Diagnoses the health of SQL statements, troubleshoots performance issues, and analyzes service traffic. For more information, see SQL Explorer.

  • SQL Review: Provides global SQL workload analysis to help you quickly identify suspicious SQL statements in database instances, analyze them, and receive optimization suggestions. For more information, see SQL Review.

  • Traffic Replay and Stress Testing: Provides traffic replay and stress testing features to help you verify whether your instance specifications need to be scaled out to handle traffic peaks. For more information, see Traffic Replay and Stress Testing.

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

  • Transaction Analysis: View the transaction type, number of transactions, and transaction details for a specific thread or within a specified time range. This feature helps you understand, analyze, and optimize database performance at the transaction level. For more information, see Transaction Analysis.

  • Quick Transaction Analysis: Identify the start and end statements of the transaction that contains the SQL statement to be analyzed to determine whether the transaction was committed or rolled back. For more information, see Quick Transaction Analysis.

Supported regions

The SQL Explorer and Audit feature is available only after you enable DAS Enterprise Edition. The supported regions vary based on the edition. For more information, see Databases and regions supported by different editions.

Impact

Enabling SQL Explorer records all DQL, DML, and DDL operations. This information is output by the database kernel with a minimal impact on CPU consumption.

Usage notes

To use the Search feature, a RAM user must be granted the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission. For more information about how to grant permissions to a RAM user, see 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 click the cluster ID.

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. On the page that appears, click a tab to view the related information.

   • Search (Audit): Queries and exports SQL statements and their corresponding information, such as the database, status, and execution duration.

   • SQL Explorer:

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

       Note

       • You can export a maximum of 1,000 SQL logs. If you need to obtain logs for a longer time range or a larger number of logs, use the Search (Audit) feature.

       • SQL Explorer logs are available 30 minutes after you enable SQL Explorer.

     • Display by Comparison: Select the time points for which you want to compare SQL Explorer results. You can view the comparison results for all SQL statements based on Execution Duration Distribution, Execution Duration, and Executions. You can also view detailed comparison results in the Requests by Comparison section.

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

     • SQL Review: Analyzes the workload of database clusters and the SQL statements that run on them within a selected time range and a baseline time range. This in-depth analysis provides index optimization suggestions, SQL rewrite suggestions, top SQL, new SQL, failed SQL, SQL feature analysis, SQL with execution changes, SQL with performance degradation, and tables with top traffic.

     • Related SQL Identification: Select the metric that you want to view and click the Analysis button. After 1 to 5 minutes, you can locate the SQL statement whose trend most closely matches the metric's trend within the selected time range and view its detailed information.

     • Traffic Replay and Stress Testing: When facing an upcoming business peak or a database schema change, especially an index change, use the traffic replay and stress testing feature to confirm whether the database cluster needs to be scaled out and verify the actual performance in a real-world business scenario. This reduces the risk of failures after your service goes live.

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

     • Transaction Analysis: Analyzes transaction details for a selected thread or within a specified time range based on hot storage data from DAS Enterprise Edition V3. The feature then performs statistical analysis and plots a trend chart showing the number of different types of transactions.

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 click the cluster ID.

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 modify the data storage duration for different sub-features.

   • The storage space for SQL Explorer and Audit data is provided by DAS and does not occupy the storage space of your database cluster.

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 click the cluster ID.

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 the check boxes of all features of SQL Explorer and Audit.

   Note

   • If you enable the audit log collection feature for PolarDB for MySQL in CloudLens for PolarDB of Simple Log Service, the SQL Explorer feature is automatically enabled for the corresponding PolarDB for MySQL cluster. Therefore, you must also disable the audit log collection feature for that PolarDB for MySQL cluster. For more information, see Enable the data collection feature.

   • Disabling the SQL Explorer feature deletes all SQL audit logs. We recommend exporting the SQL records beforehand. For more information about how to export SQL records, see Export SQL log records.

4. Click OK.

View audit log size and consumption

1. Choose Expenses in the upper-right corner of the Alibaba Cloud Console page.

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

3. On the bill details page, search by Instance ID. View the billing details where the Billable Item column is sql_explorer.

   

Migrate to 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 click the cluster ID.

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 that appears, click Upgrade.

Migrate data between enterprise editions

Enterprise Edition V2 introduces a different underlying storage architecture from V1, using a hybrid of hot and cold storage to reduce costs. Building on this architecture, Enterprise Edition V3 offers more flexible, itemized billing based on feature usage, further reducing costs.

If your database cluster supports Enterprise Edition V3, you can migrate data from DAS Enterprise Edition V1 or V2 to Enterprise Edition V3 to benefit from lower costs. For more information, see How do I migrate data between different DAS Enterprise Editions?

FAQ

• Can the fees for SQL Explorer be deducted from a resource plan?

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

• In the full request statistics section of SQL Explorer, what is the logout! statement?

  logout! indicates a connection disconnection. The duration of a logout! event is the time elapsed from the last interaction until the logout! event occurs. This duration represents the connection's idle time. The value 1158 in the Status column indicates a network disconnection. The possible reasons are:

  • The client connection timed out.

  • The server was disconnected unexpectedly.

  • 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 you use a stored procedure, as shown in the following example:

  Note

  This example uses a PolarDB for MySQL cluster, a test account named test_user, and a test database named test_db.

  1. In the PolarDB console, create a standard account and the database that the account is authorized to access. For more information, see Create a standard account.

  2. Use the test account to connect to the database cluster from the command line. For more information, see 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. Use a privileged account to connect to the database cluster. For more information, see 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 in the audit log list different from the one in the SQL statement?

  The database name in the log list is obtained from the session, while the database name in the SQL statement is specified by the user. The name depends on user input or query design, such as in scenarios involving cross-database queries or dynamic SQL. Therefore, the two database names may be inconsistent.

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

  Yes, but the performance impact is negligible.

  The specific resource usage is as follows:

  • CPU and memory: The consumption is extremely low and can be ignored.

  • Storage space: This feature is mainly used to store audit information. However, DAS provides the storage for the SQL Explorer and Audit feature, so it does not use your cluster's storage space.

  • Network: It does not affect network performance.

  • Disk performance: Disk performance is unaffected because audit data is stored within DAS, not on your cluster's disks.

• I ran an UPDATE statement, and the audit log shows one affected row, but the data did not change. What could be the cause?

  • Troubleshooting procedure:

    1. Find the SQL statement in the audit log and get the thread ID. Then, click Enable Advanced Search and search by the thread ID. Check whether AUTOCOMMIT is disabled for the current thread. If it is, check for an explicit COMMIT.

       Note

       We recommend that you filter your search by only the thread ID. If too many logs are returned, combine other conditions as needed to reduce the number of logs for easier analysis.

       

    2. If you cannot locate the issue using the preceding method, consider restoring the database and table and parsing the logs to confirm whether there is a record of a successful update.

    Note

    This procedure assumes the UPDATE statement is the final step. If subsequent modifications occur, further investigation into related SQL operations is required to determine the cause of the data change.

  • Scenarios:

    • AUTOCOMMIT is disabled and the transaction is not committed: During troubleshooting, you find that AUTOCOMMIT was disabled when the request was run, and no explicit COMMIT was performed afterward. This resulted in the data not being changed, as shown in the screenshot.

    • thread ROLLBACK: Operations within the same transaction session either all succeed or all fail. If a rollback occurs, all operations are rolled back. Follow the troubleshooting procedure to check if a ROLLBACK operation was performed after this request was run.