AnalyticDB for MySQL provides the SQL diagnostics feature to separately collect statistics for SQL query information at the query, stage, and operator levels, use the statistics to diagnose issues, and then provide optimization suggestions. This topic describes how to view and analyze query-level diagnosis results.

Diagnosis result types

Note For information about how to view query-level diagnosis results, see View diagnosis results.

Large amounts of data are returned to the client

  • Problem
    When large amounts of data are returned to the client, queries may slow down and occupy frontend network resources.
    Note You can view Returned Data in the Query Properties section of the query details page. For more information, see View query properties.
  • Suggestion
    • Reduce the amount of data returned to the client. For example, you can add the LIMIT keyword or add filter conditions when you query data.
    • Export data to other systems by using external tables to reduce the amount of data returned to the client. For example, you can export data to Object Storage Service (OSS). For more information, see Use external tables to export data from AnalyticDB for MySQL to OSS.

Large amounts of memory resources are consumed by queries

  • Problem
    Queries consume a large amount of memory resources. This may cause execution failures in other queries, reduce execution speeds, and affect the overall stability of AnalyticDB for MySQL clusters.
    Note You can view Peak Memory in the Query Properties section of the query details page. For more information, see View query properties.
  • Suggestion

    First, determine the reason why a query consumes a large amount of memory resources. Then, find the stage or operator that is consuming a large amount of memory resources by using execution plans in AnalyticDB for MySQL SQL diagnostics. For more information, see Slow queries that consume memory resources and Use execution plans to analyze queries.

Large number of stages are generated for a query

  • Problem
    Large number of stages are generated for a query. These stages occupy a large amount of network resources and make system processing more complex, which poses risks to the overall stability of clusters.
    Note For more information, see Factors that affect query performance.
  • Suggestion
    • Before data is written to an AnalyticDB for MySQL cluster, join tables in the cluster to reduce the number of tables.
    • The greater the number of joins in SQL statements, the greater the number of stages generated by AnalyticDB for MySQL for a query. Therefore, you can reduce the number of joins to reduce the number of generated stages.
    • When you use materialized views to perform queries, AnalyticDB for MySQL can reuse stages to reduce the number of stages that are generated for a query. For more information, see Overview.

A large amount of data is read by a query

  • Problem
    A query is reading a large amount of data, which consumes a large amount of disk I/O resources and affects other queries or data writes.
    Note You can view Scanned Data in the Query Properties section of the query details page. For more information, see View query properties.
  • Suggestion

    First, find the stage that is reading a large amount of data and the related TableScan operator. You can view Scanned Rows and Scan Size of a stage or Input Rows and Amount of Input Data of the TableScan operator in the Statistics section of the execution plan at the stage or operator level in AnalyticDB for MySQL SQL diagnostics. For more information, see State statistics and Operator statistics.

    After you find the TableScan operator that scans a large amount of data, you can use one of the following methods to perform optimization:
    • Add the AND filter condition in a query.
    • Adjust the existing filter conditions to reduce the amount of filtered data.
    • Check whether filter conditions that are not pushed down exist. If these filter conditions exist, reference optimization suggestions in Filter conditions are not pushed down to perform optimization.