All Products
Search

This Product

    PolarDB:Slow SQL query

    Document Center

    PolarDB:Slow SQL query

    Last Updated:Sep 24, 2026

    PolarDB for MySQL defines SQL statements that take more than 1 second to execute as slow SQL queries, and provides an SQL analysis feature in the console. You can view slow log trends and slow query log statistics. The system also provides optimization suggestions and diagnostic analysis for the related SQL statements to help you perform in-depth analysis and optimization.

    Prerequisites (optional)

    To make sure that you can view slow SQL query logs, check whether your account has been granted the AliyunHDMFullAccess permission. For finer-grained permission management, you can use a custom policy to grant permissions to a RAM user for flexible control over PolarDB.

    Note

    The maximum length of a slow log is 16 KB. The portion that exceeds this length is truncated.

    View slow SQL queries

    1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters, select the region where the cluster is deployed, and click the ID of the target cluster to go to the cluster details page.

    2. In the left-side navigation pane, choose Diagnostics and Optimization > Slow SQL.

    3. Slow SQL page provides the following three features:

      Slow log analysis

      On the Slow Log Analysis tab, select a time range, and then view Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details.

      • In the Slow Log Trend chart, you can select a specific point in time to view the corresponding Slow Log Statistics and Slow Log Details.

        Note

        If a slow SQL statement is too long to be fully displayed, hover the pointer over the statement to view the complete text in a pop-up window.

      • In the Event Distribution section, you can find slow log events within the specified time range. Click an event to view its details.

      • From the Node ID drop-down list, you can view the number of slow requests for each node.

      • On the Slow Query Log Statistics and Slow Query Log Details tabs, click image to save the slow query log information to a local file.

      • Click image to go to OpenAPI Explorer and debug the API. The currently selected and entered parameters are passed automatically.

      • In the Slow Log Statistics section:

        • Above the list, you can select filter conditions to refine the data. The available filter conditions vary by database engine.

        • Click an ID in the Query ID column of a SQL template to view its correlation analysis and a detailed list, including user distribution, client distribution, and metric trends.

        • In the Actions column for the target SQL template, click Optimize. In the SQL Diagnostic Optimization dialog box, view the SQL diagnosis results.

          If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL statement into a database client or DMS to run it. If you do not accept the suggestion, click Cancel to close the dialog box.

          Note

          DAS diagnoses SQL statements based on their complexity, the data volume of related tables, and the database load. The diagnosis may take more than 20 seconds to complete. Once complete, the SQL diagnostics engine provides a result, an optimization suggestion, and the expected performance improvement. You can then decide whether to accept the suggestion.

        • In the Actions column for the target SQL template, click Throttling. On the SQL Throttling page, configure throttling parameters for the target SQL statement. For more information, see SQL throttling.

        • For PolarDB for MySQL database instances, click IMCI in the Actions column to view the documentation for In-Memory Column Index (IMCI).

          Note

          • The IMCI button is displayed for a PolarDB for MySQL instance if it does not have an IMCI node, its slow query log's Max Execution Time exceeds 20 seconds, and the Max Scanned Rows exceeds 200,000.

          • For complex queries on large volumes of data, we recommend that you use In-Memory Column Index (IMCI) to improve query performance.

      • In the Slow Log Details section, you can also click Optimize or Throttling in the Actions column for a target SQL statement to perform SQL Diagnostic Optimization or SQL Throttling.

      Automatic SQL optimization suggestions

      Automatic SQL optimization suggestions page: select the time range that you want to view.

      Enable automatic SQL optimization: automatically identifies problematic SQL queries, generates index optimization suggestions, and creates indexes without causing table locks.

      Note

      You can view the details based on your business needs. For more information, see Automatic SQL optimization.

      Query governance

      On the Query Governance page, view the query governance results.

      • Query governance results overview: view the result data that the system has tagged and categorized.

        Note

        Only instances with DAS Enterprise Edition enabled are counted for Failed SQL Executions.

      • Query governance trends: view how the query governance results change within the selected time range.

      • Top Rankings: view the Best-performing Instances and Worst-performing Instances.

        • Worst-performing instances: view the number of slow SQL query executions for each database instance.

        • Best-performing instances: view the change in the number of slow SQL query executions for each database instance. A negative value indicates a decrease in the number of slow SQL query executions (a good optimization result), and a positive value indicates an increase.

        DAS recommends that you focus on the Optimizable SQL rankings.

      • SQL to Be Optimized: set filter criteria to display only the SQL queries that require governance.

        Note

        You can filter by DB name, SQL keyword, rule tag, and database username. These four filters are combined with a logical AND.

        • Separate multiple DB names with commas (,). They are combined with a logical OR.

        • Separate multiple SQL keywords with spaces. They are combined with a logical AND.

        • Separate multiple database usernames with commas (,). They are combined with a logical OR.

        • You can select multiple rule tags. The selected rules are combined with a logical OR.

        • In the Actions column of the target SQL sample, click Suggestions to view the detailed governance recommendations.

        • In the Actions column of the target SQL sample, click Add Tag to tag this SQL query manually. For more information about the meaning of each tag, see SQL tags that support manual tagging.

          You can also select the SQL queries that you want to tag manually and tag them in batches.

        • In the Actions column of the target SQL sample, click Sample to view the details of the slow query log sample for this SQL query.

        • In the Actions column of the target SQL sample, click Trend to view the slow log analysis details of this SQL query. For more information about slow log analysis, see Slow query log.

        You can export and share the data of the SQL queries to be optimized as needed. For more information, see Slow query log.

      • Failed SQL: set filter criteria to display only the SQL queries that you want to view.

        Note

        • Only instances with DAS Enterprise Edition enabled are counted for Failed SQL.

        • You can filter by DB name and SQL keyword. These two filters are combined with a logical AND.

          • Separate multiple DB names with commas (,). They are combined with a logical OR.

          • Separate multiple SQL keywords with spaces. They are combined with a logical AND.

        In the Actions column of the target SQL sample, click Sample to view the details of this SQL sample.

    Adjust the slow SQL query threshold

    Go to the PolarDB console and open the Settings and Management > Parameters page, where you can set the long_query_time parameter to adjust the threshold that defines a slow SQL query.

    Parameter

    Description

    long_query_time

    Records all queries whose execution time exceeds the parameter value in the slow query log.

    Valid values: 0.03 to 31536000. Default value: 1. Unit: seconds.

    FAQ

    Why is the SQL execution completion time recorded in the slow query log different from the actual execution time of the SQL statement?

    This issue usually occurs because an SQL statement changed the time zone. The time zone of the SQL execution time recorded in the slow query log can be at the session level, the database level, or the system level. The slow query log time is set as follows: if the database has a time zone, the database time zone is used; otherwise, the system time zone is used. If you change the session-level time zone by running an SQL statement, the time zone recorded in the slow query log may fail to be converted correctly.

    How do I find slow SQL queries?

    You can find slow SQL queries in the following two ways:

    • Query slow SQL queries directly in the console.

    • After you connect to the database cluster, run show processlist to find the SQL queries that take too long to execute. For more information about how to connect to a database cluster, see How to connect to a Database.

    What is the number of scanned rows (rows_examined)? What do I do if the number of scanned rows is much larger than the number of returned rows?

    The number of scanned rows (rows_examined) indicates the number of rows that are actually read when an SQL statement is executed. The database engine collects this metric automatically when it runs the query, and no additional configuration is required.

    If the number of scanned rows of a slow SQL query is much larger than the number of returned rows, the query reads a large amount of unnecessary data. This usually happens for one of the following reasons:

    • The query conditions lack a suitable index, which causes a full table scan or an index with low selectivity.

    • The table statistics are outdated, which causes the optimizer to choose a suboptimal execution plan.

    To optimize the query, follow these steps:

    1. Run EXPLAIN to view the execution plan of the SQL statement and check whether the expected index is used.

    2. Run ANALYZE TABLE to recollect the table statistics so that the optimizer can choose a better execution plan.

    3. If the execution plan is still suboptimal, add or adjust indexes based on the query conditions.

    The Automatic SQL optimization suggestions feature described in this topic can automatically identify problematic SQL queries and generate index optimization suggestions, which helps you determine whether the indexes are adequate.

    What do I do if the InvalidVersion error is returned when I call a slow log API operation by using an SDK?

    When you use an SDK (for example, a Python script) to call a slow log API operation such as DescribeSlowLogRecords and the InvalidVersion error is returned, first check whether the Version value in the request parameters is correct.

    (Recommended) Use the debugging tool in Alibaba Cloud OpenAPI Explorer to verify that the parameters are correct and that the request path works. After you confirm this, update the configuration in your code.

    Why does the execution time of an identical SQL statement, or an SQL statement that updates data by primary key, occasionally fluctuate significantly (for example, from milliseconds to seconds)?

    Troubleshoot the issue in the following order:

    1. Check the statistics: Run ANALYZE TABLE to recollect the statistics of the related tables and prevent the optimizer from choosing an incorrect index because of outdated statistics.

    2. Compare execution plans: Get the execution plans of both a normal execution and an abnormal execution, and check whether the fluctuation is caused by a different index choice.

    3. Check the client and the runtime environment: If the database side shows no anomaly and the slow query log has no matching record (that is, the execution time does not exceed the threshold in Adjust the slow SQL query threshold: long_query_time), the time is probably not consumed in the database execution phase. Check the client server for network latency, resource contention, or connection pool exhaustion (for example, a HikariCP connection timeout).

    4. Temporary mitigation: For connection pool exhaustion, you can temporarily increase the maximumPoolSize parameter of the connection pool to reduce waiting. To resolve the issue, prioritize optimizing the slow SQL queries that account for a large share of the execution time so that connections are held for shorter periods.

    Best practices

    High CPU utilization for PolarDB for MySQL

    Related APIs

    API

    Description

    DescribeSlowLogs

    Queries the statistics of slow query logs for a PolarDB for MySQL cluster.

    DescribeSlowLogRecords

    Queries the details of slow query logs for a PolarDB for MySQL cluster.

    DescribeDBClusterAuditLogCollector

    Queries whether SQL collection features (such as audit logs and SQL Explorer) are enabled for a PolarDB for MySQL cluster.

    ModifyDBClusterAuditLogCollector

    Enables or disables SQL collection features (such as audit logs and SQL Explorer) for a PolarDB for MySQL cluster.