SQL Explorer

Features

Building on full request data and security audit, Database Autonomy Service (DAS) integrates features such as Search, SQL Explorer, Security Audit, and Traffic Replay and Stress Testing. These features help you get the details of SQL statements, troubleshoot various performance issues, identify high-risk sources, and check whether your cluster specifications need to be scaled up, so that you can handle business traffic peaks.

• Search feature: queries and exports SQL statements and their related information, such as the 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. It helps you quickly locate suspicious SQL statements in your database instances, analyze them, and get optimization suggestions. For more information, see SQL Review.

  • Traffic Replay and Stress Testing feature: replays traffic and runs stress tests. It helps you check whether your instance specifications need to be scaled up, so that you can handle business 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: with transaction analysis, you can get the transaction types, transaction counts, and transaction details of a specified thread within a specified time period. This helps you understand, analyze, and optimize database performance at the transaction level. For more information, see Transaction analysis.

  • Quick Transaction Analysis feature: gets the first and last statements of the transaction that contains the SQL statement you want to analyze, so you can see whether the transaction was committed or rolled back. For more information, see Quick transaction analysis.

Supported regions

SQL Explorer and Audit is available only after you enable DAS Enterprise Edition. The regions supported by each Enterprise Edition are different. For more information, see Supported databases and regions.

Impact

Enabling SQL Explorer records information about all DQL, DML, and DDL operations. The database kernel outputs this information, so the CPU consumption of the system is extremely low.

Usage notes

To use the Search feature as a RAM user, you must grant the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission to the RAM user. 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 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 current Alibaba Cloud account, follow the on-screen instructions to enable DAS Enterprise Edition.

4. On the right-side page, click a feature tab to view its details.

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

   • SQL Explorer:

     • Display by Time Range: select the time range for which you want to view SQL Explorer results. You can view the Execution Duration Distribution, Execution Duration, and Executions of all SQL statements within the selected time range. You can also view the details of all SQL statements within the selected time range in the Full Request Statistics area and export them to your local machine.

       Note

       • You can export up to 1,000 SQL logs at a time. To obtain SQL logs of a larger time range or in a greater quantity, use the Search (Audit) feature.

       • After you enable SQL Explorer, wait 30 minutes before you can view the Audit logs.

     • Display by Comparison: select the points in time at which you want to compare SQL Explorer results. You can view the comparison results of the Execution Duration Distribution, Execution Duration, and Executions of all SQL statements. You can also view the detailed comparison results in the Requests by Comparison area.

     • Source Statistics: select the time range for which you want to collect statistics on SQL sources. You can view the source information of all SQL statements within the selected time range.

     • SQL Review: performs workload analysis on the database cluster in the selected interval and the baseline interval, and performs in-depth analysis on the SQL statements that run in the database cluster. It shows the index optimization suggestions, SQL rewrite suggestions, top SQL statements, new SQL statements, failed SQL statements, SQL feature analysis, SQL statements with execution changes, SQL statements with degraded performance, and top traffic tables of the database cluster.

     • Related SQL Identification: select the metrics that you want to view, and click Analysis. After 1 to 5 minutes, the system locates the SQL statements whose trends are most similar to the trend of the selected metrics within the selected time range, along with their details.

     • Traffic Replay and Stress Testing: before an upcoming short-term business peak or a database schema change, especially an index change, you can use traffic replay and stress testing to check whether the database cluster specifications need to be scaled up. This verifies the actual results in real business scenarios and reduces the risk of failures after the change goes live.

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

     • Transaction Analysis: based on the hot storage data of DAS Enterprise Edition V3, analyzes the transaction details within the selected thread and the selected time range, then performs statistical analysis and plots trend charts of the number of transactions of each type.

Modify the storage duration of SQL logs

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 modify the data storage duration of each sub-feature.

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

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 and disable SQL Explorer and Audit.

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

   Note

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

   • After the SQL Explorer feature is disabled, the SQL audit logs are deleted. Export the SQL records before you disable the SQL Explorer feature. For more information about how to export SQL records, see Export SQL log records.

4. Click OK.

View the 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 in which the Billable Item column is sql_explorer.

3. On the Bill Details page, set the search condition to Instance ID and search.View the cost details in which 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 that appears, click Upgrade.

Migrate SQL Explorer and Audit data between Enterprise Editions

Compared with Enterprise Edition V1, Enterprise Edition V2 changes the underlying storage architecture and uses hot-cold hybrid storage to reduce costs and improve efficiency. This lowers the cost of use. Building on hot-cold hybrid storage, Enterprise Edition V3 splits billing items by the features that you use. Billing is more flexible and the cost of use is lower.

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

FAQ

• Can I use a resource plan to offset the fees of SQL Explorer?

  No. The SQL Explorer feature supports only Pay-as-you-go billing. It does not support Subscription, and the fees cannot be offset by a resource plan.

• What is the logout！ statement in the Full Request Statistics area of SQL Explorer?

  logout！ indicates that the connection is disconnected. The duration of logout！ is the difference between the time of the last interaction and the time when logout！ occurs. This difference represents the idle duration of the connection. In the Status column, the value 1158 indicates that the network connection is disconnected. The possible causes are:

  • The client connection timed out.

  • The server disconnected abnormally.

  • The server reset the connection because the interactive_timeout or wait_timeout duration was exceeded.

• 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 shows how this situation arises:

  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 a 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 Use the command line to connect to the cluster.

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

     -- 切换到测试数据库
     USE test_db;

     -- 创建存储过程
     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 Use the command line to connect to the cluster.

  5. Call the stored procedure.

     -- 切换到测试数据库
     USE test_db;

     -- 调用存储过程
     CALL das();

     -- 调用结果
     +-----------+-----------+---------+---------+---------+------+-----------+-------------------------------------------------------------------------+
     | 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 different from the database name in the SQL statement?

  The database name displayed in the log list is obtained from the session. The database name in the SQL statement is specified by the user and depends on the user input or the query design, such as cross-database queries and dynamic SQL. The two names can be different.

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

  Yes. However, the impact is extremely small and can hardly be perceived.

  The resource usage is as follows:

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

  • Storage space: mainly used to store audit information. However, the SQL Explorer and Audit feature provided by DAS Enterprise Edition uses the storage space provided by DAS and does not occupy the storage space of the database cluster.

  • Network: no impact on network performance.

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

• An UPDATE statement was run in a cluster. The audit log shows that the number of affected rows for this SQL statement is 1. However, the data in the table was not updated and remains in the state before the modification. How do I troubleshoot and resolve this issue?

  • Troubleshooting procedure:

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

       Note

       (Recommended) Filter the search only by the Thread ID. If too many logs are returned, combine other search conditions as needed to reduce the log volume and make it easier to analyze and locate the issue.

    2. If the preceding method does not locate the issue, you can use database and table restoration and log parsing to confirm whether a record of a successful modification exists.

    Note

    In this troubleshooting procedure, the UPDATE statement is treated as the last step of the business logic. After this modification is complete, the data remains unchanged, so you do not need to further check whether a modification or deletion occurred afterwards. If a modification occurred later, you must continue to check whether related SQL operations changed the data.

  • Problem scenarios:

    • AUTOCOMMIT disabled and the SQL statement not committed: the troubleshooting procedure shows that this request disabled AUTOCOMMIT during execution and did not issue an explicit COMMIT afterwards, so the data was not changed.

    • Thread ROLLBACK: the operations that run in the same transaction session either all succeed or all fail. If a rollback occurs, all operations are rolled back. Use the troubleshooting procedure to check whether a ROLLBACK operation occurred after this request.