All Products
Search

This Product

    PolarDB:️SQL Explorer and Audit

    Document Center

    PolarDB:️SQL Explorer and Audit

    Last Updated:Jul 18, 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.

    Background information

    The SQL Explorer and Audit feature records the information of all Data Query Language (DQL), DML, and DDL statements that are executed. DAS obtains the information from database kernels, which consumes only a small amount of CPU resources. This topic describes how to use the SQL Explorer and Audit feature on a PolarDB cluster.

    Prerequisites

    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.

    Supported regions

    You must enable DAS Enterprise Edition for your database instance before you can use the SQL Explorer and Audit feature. DAS provides different versions of DAS Enterprise Edition. The available version varies based on regions. For more information, see Supported databases and regions.

    Features

    • Search (Audit): This feature is used to query and export the execution history records of SQL statements, including database status and execution time. For more information, see Search (Audit).

    • SQL Explorer: This feature is used to perform health checks on SQL statements that are executed within a specific period of time, troubleshoot performance issues, and analyze business traffic.

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

    • Traffic playback and stress testing: This feature is used to check whether your cluster needs to be scaled to handle traffic spikes. For more information, see Traffic playback and stress testing.

    Billing rules

    You must enable DAS Enterprise Edition for your database instance before you can use the SQL Explorer and Audit feature. For more information about the billing rules of DAS Enterprise Edition of different versions, see Billing overview.

    Note

    • If the SQL Explorer feature is enabled for your PolarDB cluster before the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature, the fee for the SQL Explorer feature is billed by PolarDB for MySQL. For more information, see Billing rules of SQL Explorer (optional).

    • If you use DAS Enterprise Edition V3, you are charged by DAS.

    Enable the SQL Explorer and Audit feature

    1. Log on to the PolarDB console.

    2. In the left-side navigation pane, click Clusters.

    3. In the upper-left corner, select the region where the cluster to which you want to connect is deployed.

    4. Find the cluster and click its ID.

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

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

      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.

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

      • Display by Time Range: 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.

      • Display by Comparison: 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 of the comparison results in the Requests by Comparison section.

      • Source Statistics: 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. For more information, see SQL Review.

      • Related SQL Identification: 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 Test: For more information, see Traffic playback and stress testing.

      • Security Audit: For more information, see Security audit.

    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 cluster over time. The larger the blue area of the chart is, the healthier the cluster is when the SQL statements are executed on the cluster. The larger the orange and red areas of the chart are, the less healthy the cluster is when the SQL statements are executed on the cluster.

    • 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 cluster 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 initiates 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.

    2. In the left-side navigation pane, click Clusters.

    3. In the upper-left corner, select the region where the cluster to which you want to connect is deployed.

    4. Find the cluster and click its ID.

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

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

    7. Change the storage duration and click OK.

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

      Note

      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. Before you disable the SQL Explorer and Audit feature, we recommend that you export the SQL audit logs as a file and download the file to your computer. 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.

    2. In the left-side navigation pane, click Clusters.

    3. In the upper-left corner, select the region where the cluster to which you want to connect is deployed.

    4. Find the cluster and click its ID.

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

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

    7. In the message that appears, 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, choose Expenses > User Center.

    3. In the left-side navigation pane, choose Spending Summary > Spending Summary.

    4. On the Bills page, click the 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.

      费用账单

    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.

    2. In the left-side navigation pane, click Clusters.

    3. In the upper-left corner, select the region where the cluster to which you want to connect is deployed.

    4. Find the cluster and click its ID.

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

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