All Products
Search

This Product

    PolarDB:️SQL Explorer and Audit

    Document Center

    PolarDB:️SQL Explorer and Audit

    Last Updated:Dec 20, 2024

    The SQL Explorer feature in 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). This feature is developed based on the full request feature and the SQL Audit feature. In addition, this feature is integrated with the following features: Search, SQL Explorer, Security Audit, and Traffic Playback and Stress Test. This feature helps you obtain the information of the SQL statements that are executed. You can use the information to troubleshoot various performance issues, identify the sources of high risks, and check whether you need to upgrade your PolarDB cluster to handle business traffic peaks.

    Region

    You must enable DAS Enterprise Edition 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 Supported databases and regions.

    Features

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

    • SQL Explorer: diagnoses the SQL statements, troubleshoots performance issues, and analyzes business traffic. For more information, see SQL Explorer.

      • SQL Review: provides 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 Test: checks whether your instance needs to be scaled to handle traffic spikes. For more information, see Traffic playback and stress testing.

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

      • Transaction Analysis: obtains 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: checks the start and end SQL statements of the transaction where the SQL statement to be analyzed is located. This helps you obtain the commit or rollback status of the SQL statement. For more information, see Quick transaction analysis.

    Impact

    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).

        • 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: You can use this feature to perform workload analysis on database clusters within the specified time range and the baseline time range, and perform 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 instance needs to be scaled out or scaled up and verify the performance of the database instance 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.

    2. In the upper-right corner of the page, select Expenses.

    3. In the left-side navigation pane of the Expenses and Costs page, choose Bills > Bill Details.

    4. 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.

      image

    5. View the billing details in the data entries in which the value in the Billing Item column is sql_explorer.

    Upgrade to new SQL Explorer and Audit

    Note

    You can upgrade original SQL Explorer and Audit to new SQL Explorer and Audit 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 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 database cluster supports Enterprise Edition V3, you can migrate data from DAS Enterprise Edition V1 or V2 to Enterprise Edition V3 for more discounts. For more information, see FAQ.

    FAQ

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

    A: The logout! statement 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.

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

    A: 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 this example, the database instance is an ApsaraDB RDS for MySQL instance, the test account is test_user, and the test database is testdb.

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

    2. Use the test_user account to connect to the database instance by using the CLI. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

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

      -- Switch to the testdb database.
USE testdb;

-- 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 instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

    5. Call the stored procedure that you created. 

      -- Switch to the testdb database.
USE testdb;

-- Call the stored procedure.
CALL das();

+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| ID     | USER      | HOST   | DB     | COMMAND | TIME | STATE     | INFO                                                                    |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
| 487818 | test_user | %:2065 | testdb | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+

    Q: Why is the database name displayed in the Logs section inconsistent with that in SQL statements?

    A: The database name displayed in the Log 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.