PolarDB:PolarDB-X slow query logs

Concepts

Before you begin, it is important to understand the following concepts:

• Slow Query Definition: A query is defined as "slow" if its execution time exceeds 1 second.

• Compute Node (CN) Slow Query (Logical SQL):

  • The original SQL query sent by your application to PolarDB-X. It represents a complete business-level request.

  • Only CN slow queries support diagnosis and optimization suggestions, as they reflect the original application logic. You will primarily analyze and optimize these logical queries.

• Storage Node (DN) Slow Query (Physical SQL):

  • The internal sub-queries that PolarDB-X sends to the underlying storage nodes to execute a single CN query. One CN query can generate multiple DN queries.

  • DN slow queries do not support direct optimization. They are used for advanced troubleshooting of the distributed execution layer.

Key features

The Slow Query Log feature provides a comprehensive suite of tools to help you quickly diagnose and resolve performance issues:

• Connect performance spikes to queries.

  PolarDB-X console displays CPU utilization and the number of slow queries on a single graph. This lets you immediately see which specific queries caused a spike in database load.

• Group and analyze similar queries into a single SQL template. This helps you identify the most frequent performance problems. For each template, you can view key details, including:

  • Execution count

  • Average and maximum duration

  • Number of rows returned

• Get automated optimization suggestions.

  PolarDB-X analyzes slow queries and provides specific, actionable advice. This includes recommending new indexes (such as local indexes and global secondary indexes) and estimating the performance benefits you can expect after applying the fix.

How to view slow query logs

Follow these steps to locate and resolve performance bottlenecks.

1. Log on to the PolarDB-X console.

2. In the top navigation bar, select the region where the instance is deployed.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the instance and click its name.

5. In the navigation pane on the left, choose Diagnostics And Optimization > Slow Query Logs. Select the Role and Node as needed.

6. Specify a time range and view the Slow Query Log Trends, Event Distribution, Slow Query Log Statistics, and Slow Query Log Details within the specified time range.

   Note

   When you specify the time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs within the previous month.

   • In the Slow Query Log Trends section, you can click the Compute Node (CN) and Data Node (DN) tabs to view slow query logs on compute nodes and data nodes.

   • In the Slow Query Log Trends section, you can click a point in time in the trend chart and view the statistics and details of the slow query logs at the point in time.

     Note

     If the content of an SQL statement is not completely displayed due to length limits of the GUI, you can move the pointer over the SQL statement to view the complete content.

   • Select the desired node from the Node ID drop-down list to view the number of slow queries on the node.

   • Click the icon to download slow query logs to your computer.

   • Click  to populate the selected and entered parameters to the OpenAPI console for API debugging.

   • In the Event Distribution section, perform the following operation:

     You can query slow query log events within the specified time range. Click an event to view its details.

   • On the Slow Query Log Statistics tab, perform the following operations:

     • Find the SQL template that you want to manage and click Details in the Actions column. In the Slow Log Sample dialog box, view the slow query log sample of the SQL template.

     • Click Optimize in the Actions column corresponding to the SQL template. In the SQL Diagnostic Optimization dialog box, view the SQL diagnosis results.

       If you accept the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not accept the SQL optimization suggestions, click Cancel.

       Note

       DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostic engine provides diagnostic results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnostic results.

     • Find the SQL template that you want to manage and click Throttling in the Actions column. In the SQL Throttling dialog box, configure the parameters. For more information, see SQL throttling.

   • Find the SQL statement that you want to manage and click Logical SQL Statements in the Actions column to view the SQL statements on compute nodes and data nodes.