All Products
Search

This Product

    PolarDB:️SQL Explorer and Audit

    Document Center

    PolarDB:️SQL Explorer and Audit

    Last Updated:Feb 07, 2025

    The SQL Explorer feature of PolarDB for MySQL is upgraded to the SQL Explorer and Audit feature. The SQL Explorer and Audit feature is provided by Database Autonomy Service (DAS). The search (audit) feature collects full SQL request statistics and allows you to query and export SQL statements along with related information, such as the databases on which the SQL statements are executed and the IP addresses of users or clients. The SQL Explorer feature can diagnose the health status of SQL statements, troubleshoot performance issues, and analyze business traffic. This improves the efficiency of fault diagnosis, database optimization, and risk detection.

    Overview

    DAS integrates features including search, SQL Explorer, security audit, and traffic replay and load testing based on full request statistics collection and security auditing. These features enable you to efficiently obtain specific information about SQL statements, troubleshoot various performance issues, pinpoint high-risk threats, and evaluate if your cluster capacity requires expansion to accommodate peak business traffic.

    • Search: This feature is used to query and export SQL statements and related information, such as the databases on which the SQL statements are executed, the status of SQL requests, and the execution duration of each SQL statement. For more information, see Search (Audit).

    • SQL Explorer: This feature is used to diagnose the health status of SQL statements, troubleshoot performance issues, and analyze business traffic. For more information, see SQL Explorer.

      • SQL Review: This feature supports global workload analysis on SQL statements. It helps you quickly locate suspicious SQL statements, analyzes suspicious SQL statements, and provides the corresponding optimization suggestions. For more information, see SQL Review.

      • Traffic playback and stress testing: These features are used to check whether you need to scale up your database instances to handle workloads during peak hours. For more information, see Traffic playback and stress testing.

      • Security audit: This feature is used to automatically identify risks, such as high-risk SQL statements, SQL injection attacks, and new access sources. For more information, see Security audit.

      • Transaction analysis: This feature is used to obtain the transaction type, number of transactions, and transaction details of a thread within the specified period. This allows you to understand, analyze, and optimize database performance at the transaction level. For more information, see Transaction analysis.

      • Quick transaction analysis: This feature is used to check the start and end SQL statements of the transaction where the SQL statement to be analyzed is located. This way, you can obtain the commit or rollback status of the SQL statement. For more information, see Quick transaction analysis.

    Supported regions

    You must enable DAS Enterprise Edition for your database instance before you can use the SQL Explorer and Audit feature. Different versions of DAS Enterprise Edition support different databases and regions. For more information, see the Supported databases and regions section of the "DAS editions and supported features" topic.

    Impacts

    The SQL Explorer feature records the information about all executed data query language (DQL), DML, and DDL statements. DAS obtains the information from database kernels, which consumes only a small amount of CPU resources.

    Usage notes

    If you use the search feature as a Resource Access Management (RAM) user, make sure that the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission is granted to the RAM user. For more information, see Create and grant permissions to a RAM user.

    Note

    You can also create a custom policy to grant the RAM user the permissions to use the search feature, including the log export feature. For more information, see Use custom policies to grant a RAM user the permissions to use the search and export features in the SQL Explorer and Audit module.

    Billing

    Enterprise Edition V0

    The SQL Explorer feature can be billed based on the pay-as-you-go billing method and does not support the subscription billing method. The related fees are categorized under PolarDB.

    Prices

    • Regions in the Chinese mainland: USD 0.0013 per GB-hour

    • China (Hong Kong) and regions outside China: USD 0.0019 per GB-hour

    Enterprise Edition V0 or later

    (Optional) Billing rules for SQL Explorer For more information, see DAS billing.

    Enable the SQL Explorer and Audit feature

    1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.

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

    3. On the page that appears, click the SQL Explorer tab. Then, click Enable SQL Explorer.

      Note

      If you do not enable DAS Enterprise Edition for your Alibaba Cloud account, follow the instructions on the page that appears to enable DAS Enterprise Edition.

    4. On the page that appears, click the tab corresponding to a feature based on your business requirements. Then, you can view the information of the feature.

      • Search (audit): You can query and export SQL statements and the related information, such as the database, status, and execution time.

      • SQL Explorer:

        • Display by Time Range: You can select the time range of the executed SQL statements whose SQL Explorer results you want to query. You can view the Execution Duration Distribution, Execution Duration, and Executions values of all SQL statements over the time range. You can view the details of all SQL statements over the time range and export the details in the Full Request Statistics section.

          Note

          • You can export up to 1,000 SQL logs. If you want to obtain a larger number of SQL logs within a larger time range, you can use the search (audit) feature. For more information, see Search (Audit).

          • You can view audit logs approximately half an hour after you enable SQL Explorer.

        • Display by Comparison: You can select the date and time range of the executed SQL statements whose SQL Explorer results you want to compare. You can view the Execution Duration Distribution, Execution Duration, and Executions comparison results of all SQL statements over the time range. You can view the details about the comparison results in the Requests by Comparison section.

        • Source Statistics: You can select the time range of the executed SQL statements whose access sources you want to collect. Then, you can view all request sources over the time range.

        • SQL Review: The SQL Review feature performs workload analysis on database clusters within the specified time range and the baseline time range, and performs in-depth analysis on running SQL queries in database clusters. This feature displays index optimization suggestions, SQL rewrite suggestions, top resource-consuming SQL statements, new SQL statements, failed SQL statements, SQL feature analysis, SQL statements with high execution variation, SQL statements with deteriorated performance, and top tables that generate the most traffic for database clusters.

        • Related SQL Identification: You can select the metrics that you want to view and click Analysis. It takes 1 to 5 minutes to identify the SQL statements that best fit the performance of the specified metrics.

        • Traffic Playback and Stress Testing: You can use this feature to check whether your database cluster needs to be scaled out or scaled up and verify the performance of the database cluster after scale-out or scale-up in real business scenarios to handle workloads during peak hours or schema changes, especially index changes. This helps reduce the possibility of failures.

        • Security Audit: You can use this feature to automatically identify risks, such as high-risk SQL statements, SQL injection attacks, and new request sources.

        • Transaction Analysis: The transaction analysis feature is based on the data of DAS Enterprise Edition V3 stored in hot storage mode. You can use this feature to analyze the transaction details of a specific thread within the specified period, collect statistics, and generate a chart that shows the trends in the quantity of transactions of different types.

    Parameters

    • Execution Duration Distribution: On the Execution Duration Distribution tab, you can view the distribution of execution durations of SQL queries based on the time range that you specify. The statistical data is collected every minute. The execution durations are divided into seven ranges:

      • [0,1] ms: indicates that the execution duration ranges from 0 ms to 1 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • (1,2] ms: indicates that the execution duration is greater than 1 ms and less than or equal to 2 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • (2,3] ms: indicates that the execution duration is greater than 2 ms and less than or equal to 3 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • (3,10] ms: indicates that the execution duration is greater than 3 ms and less than or equal to 10 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • (10,100] ms: indicates that the execution duration is greater than 10 ms and less than or equal to 100 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • (0.1,1]s: indicates that the execution duration is greater than 0.1s and less than or equal to 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      • > 1s: indicates that the execution duration is greater than 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.

      Note

      The section on the Execution Duration Distribution tab shows the execution time of SQL statements on the instance over time. The larger the blue area of the chart is, the healthier the instance is when the SQL statements are executed on the instance. The larger the orange and red areas of the chart are, the less healthy the instance is when the SQL statements are executed on the instance.

    • Execution Duration: On the Execution Duration tab, you can specify a time range to view the execution durations of SQL queries.

    • Full Request Statistics: You can view the details of SQL statements based on the time range that you specify. The details include the SQL text, execution duration percentage, average execution duration, and execution trend for each SQL statement.

      Note

      You can calculate the execution duration percentage for the SQL statements that use a specific SQL template based on the following formula: Execution duration percentage = (Execution duration of the SQL statements that use the SQL template × Number of executions of the SQL statements)/(Total execution duration of all SQL statements × Total number of executions) × 100%. Higher execution duration percentages indicate that the database instance uses a larger number of MySQL resources to execute the corresponding SQL statements.

    • SQL ID: You can click an SQL ID to view the performance trend and sample data of the SQL statements that use the corresponding SQL template.

    • SQL Sample: On the SQL Sample tab, you can view the client that initiated each sample SQL request.

      Note

      The UTF-8 character set is used to encode SQL samples.

    Change the retention period of SQL logs

    Warning

    If you reduce the storage duration of data generated by the SQL Explorer and Audit feature, DAS immediately clears the SQL audit logs that are stored for a period longer than the specified storage duration. We recommend that you export and save SQL audit logs to your computer before you reduce the storage duration.

    1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.

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

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

    4. Change the storage duration and click OK.

      Note

      • If you enable DAS Enterprise V3, you can change the storage duration of data generated by different subfeatures.

      • The storage that is occupied by the data generated by the SQL Explorer and Audit feature is provided by DAS and does not consume the storage of your database cluster.

    Disable the SQL Explorer and Audit feature

    Warning

    After you disable the SQL Explorer and Audit feature, all SQL audit logs are deleted. We recommend that you export the SQL audit logs as a file and download the file to your computer before you disable the SQL Explorer and Audit feature. If you enable the SQL Explorer and Audit feature again, SQL audit logs are recorded from the point in time at which the SQL Explorer and Audit feature is enabled.

    1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.

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

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

      If you enable DAS Enterprise V3, select all subfeatures of the SQL Explorer and Audit feature.

      Note

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

      • After you disable the SQL Explorer and Audit feature, all SQL log entries are deleted. We recommend that you export SQL log entries before you disable this feature. For more information about how to export SQL records, see Procedure.

    4. Click OK.

    View the size and consumption details of audit logs

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

    2. In the left-side navigation pane of the Expenses and Costs page, choose Bills > Bill Details. View the billing details in the data entries in which the value in the Billing Item column is sql_explorer.

    3. On the Bill Details page, click the Billing Details tab. In the search bar, select Instance ID from the drop-down list and enter the ID of the cluster for which you want to query the details. View the billing details in the data entries in which the value in the Billing Item column is sql_explorer.

      image

    Upgrade to new SQL Explorer and Audit

    Note

    You can upgrade SQL Explorer and Audit to the new version if your PolarDB for MySQL cluster is deployed in the China (Hangzhou), China (Shanghai), China (Beijing), or China (Shenzhen) region.

    1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.

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

    3. In the Upgrade from SQL Explorer to SQL Explorer and Audit message, click Upgrade.

    Migrate data between different versions of DAS Enterprise Edition

    Compared with Enterprise Edition V1, Enterprise Edition V2 adopts a new underlying storage architecture that implements hot and cold hybrid storage to reduce storage costs and improve storage efficiency. Enterprise Edition V3 is developed based on the hot and cold hybrid storage architecture and further designs fine-grained billable items for flexible billing methods and lower storage costs.

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

    FAQ

    Can I use resource plans to offset the fees for using SQL Explorer?

    No. The SQL Explorer feature can be billed based on the pay-as-you-go billing method and does not support the subscription billing method or resource plans.

    What does the logout! statement in the Full Request Statistics section on the SQL Explorer tab indicate?

    logout! indicates a disconnection. The execution duration of the logout! statement is the difference between the last interaction time and the time when the disconnection occurs. During the time difference, the connection remains idle. The 1158 code displayed in the Status column indicates network disconnection that may be caused by the following reasons:

    • The client connection times out.

    • The server is disconnected.

    • The connection to the server is reset if the duration of the connection exceeds the value specified by the interactive_timeout or wait_timeout parameter.

    Why does a percent sign (%) appear in the Access Source column on the Source Statistics tab of the SQL Explorer tab?

    When you use a stored procedure, a percent sign (%) may be displayed in the Access Source column on the Source Statistics tab of the SQL Explorer tab. You can perform the following operations to reproduce this situation.

    Note

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

    1. In the PolarDB console, create a database and a standard account and grant permissions on the database to the standard account. For more information, see Create a standard account.

    2. Use the test_user account to connect to the cluster by means of the CLI. For more information, see Use the CLI to connect to a cluster.

    3. Switch to the testdb database and execute the following statements to create a stored procedure: 

      -- Switch to the testdb 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 cluster. For more information, see Create a privileged account and Use the CLI to connect to a cluster.

    5. Call the stored procedure that you created. 

      -- Switch to the testdb database.
USE test_db; 
      -- Call the stored procedure.
CALL das(); 
      -- 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 Logs section inconsistent with that in SQL statements?

    The database name displayed in the Logs section is obtained from sessions, while the database name in SQL statements is specified by a user and depends on the input or query design of the user, such as cross-database query and dynamic SQL. This causes inconsistent database names that are displayed in SQL statements and in the Logs section.

    Does the SQL Explorer and Audit feature have an impact on database performance? How significant is the impact?

    Yes. The SQL Explorer and Audit feature has an impact on database performance, but the impact is minor and hardly noticeable.

    The following resources are used:

    • CPU and memory: The CPU and memory consumption is extremely low and almost negligible.

    • Storage space: The SQL Explorer and Audit feature provided by DAS Enterprise Edition has no impact on the storage space of the cluster because the audit data is stored in DAS.

    • Network: The network performance is not affected.

    • Disk performance: SQL Explorer and Audit has no impact on disk performance because the audit data is stored in DAS, not on the disk of the cluster.