The SQL Explorer feature in PolarDB for PostgreSQL provides value-added services for your databases, such as health diagnostics and performance troubleshooting.
Function overview
DAS combines several powerful tools into a single interface:
Search (Audit): Query and export logs of all SQL statements, such as database, status, and execution time. For more information, see Audit.
NoteEnabling or disabling the audit log in the SQL Explorer and Audit console changes the kernel parameter
log_statement.Enabling the audit log will set
log_statement = all.Disabling the audit log sets
log_statement = ddl.
The SQL Explorer feature is used to diagnose the health of SQL statements, troubleshoot performance issues, and analyze service traffic. For more information, see SQL Explorer.
SQL Review: Provides global SQL payload analysis. It helps you quickly locate suspicious SQL in your database instances, analyze the suspicious SQL, and obtain corresponding optimization recommendations. For more information, see SQL Review.
Traffic Replay And Stress Testing: Provides traffic replay and stress testing capabilities that help you determine whether your instance type needs to be scaled out to effectively manage peak service traffic. For more information, see Traffic Replay and Stress Testing.
Security Audit: Automatically detect threats such as important SQL, SQL injection, and new access sources. For more information, see Security Audit (Old Version).
Transaction Analysis: Lets you view the transaction type, number of transactions, and transaction details for a specific 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: Helps you identify the start and end statements of the transaction that contains a specific SQL statement, which lets you determine whether the transaction was committed or rolled back. For more information, see Quick Transaction Analysis.
Supported regions
You can use the SQL Explorer and Audit feature only after you enable DAS Enterprise Edition. Different Enterprise Editions support different regions. For more information, see Databases and regions supported by each edition.
Impacts
The SQL Explorer feature records the information about all executed 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 RAM user, make sure that the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permission is granted to the RAM user. For more information, see Create and manage RAM users.
You can also use a custom policy to grant a RAM user permissions to use the search feature, including the export feature. For more information, see Use a custom policy to grant a RAM user permissions to use the search and export features in SQL Explorer and Audit.
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 feature
Log on to the PolarDB console.
In the upper-left corner of the console, select the region where your cluster is deployed.
Click the ID of the cluster.
In the navigation pane on the left, choose .
Enable the SQL Explorer feature.
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.
Click the tab corresponding to a feature as needed. 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.
NoteYou 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 Audit feature.
Display By Comparison: Select the 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.
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.1 s and less than or equal to 1 s. The chart shows the percentage of SQL queries whose execution durations fall within this range.>1 s: indicates that the execution duration is greater than 1 s. The chart shows the percentage of SQL queries whose execution durations fall within this range.
NoteThe 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.
NoteYou 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 cluster uses a larger number of 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.
NoteThe UTF-8 character set is used to encode SQL samples.
Modify the retention period of SQL logs
Log on to the PolarDB console.
In the upper-left corner of the console, select the region where your cluster is deployed.
Click the ID of the cluster.
In the navigation pane on the left, choose .
In the upper-right corner of the SQL Explorer page, click Service Settings.
Modify the storage duration and click OK.
If you enable DAS Enterprise V3, you can change the storage duration of data generated by different subfeatures.
NoteThe storage space that is occupied by the SQL Explorer data is provided by DAS and does not consume the storage space of your database instance.
Export SQL log entries
Log on to the PolarDB console.
In the upper-left corner of the console, select the region where your cluster is deployed.
Click the ID of the cluster.
In the navigation pane on the left, choose .
Click Audit on the right side of the Export tab.
In the dialog box that appears, configure the Exported Fields and Export Time Range parameters and then click OK.
In the Are You Sure That You Want To Export SQL Details? dialog box, configure the Task Name and CSV Separator parameters and then click Confirm To Submit A Task.
NoteAfter the task is submitted, you cannot cancel the task.
If the task fails, you are not charged for the task.
The task-related data is retained for seven days.
Only 10,000,000 task-related data records are exported. If the number of data records in the export details exceeds 10,000,000, you can narrow the time range of the export.
It takes about 5 minutes to process and archive the latest data. If you want to export the latest data, try again later.
After the export is complete, click Task List in the upper-right corner of the SQL Explorer page. Then, click Download in the Actions column corresponding to the task that you want to manage to download the exported file.
Disable the SQL Explorer feature
After you disable the SQL Explorer 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.
Log on to the PolarDB console.
In the upper-left corner of the console, select the region where your cluster is deployed.
Click the ID of the cluster.
In the navigation pane on the left, choose .
Click Service Settings, disable the SQL Explorer and Audit feature.
If you have activated DAS Enterprise V3, clear all features of the SQL Explorer and Audit module. Click Submit.
NoteThe storage space that is occupied by the data generated by the SQL Explorer and Audit module is released one hour after the SQL Explorer and Audit module is disabled.
Select the prompt that appears and then click Submit And Unsubscribe.
View the size and consumption details of audit logs
Log on to the Alibaba Cloud Management Console. In the upper-right corner of the page, click Expenses.
In the left navigation bar of the Expenses And Costs page, select .View the billing details where the Billing Item column is sql_explorer.
On the Bill Details tab, click the Billing Details tab and search by Instance ID.View the billing details for the entry where the Billing Item column is sql_explorer.
