AnalyticDB:Query-level diagnosis results

Diagnosis types

Large amounts of data returned to the client

Problem: When a query returns large amounts of data to the client, it occupies frontend network resources and causes slow query execution. Check Returned Data in the Query Properties section of the query details page to confirm the volume. For more information, see View query properties.

Fix: Reduce the amount of data returned to the client:

• Add a LIMIT clause or tighten filter conditions in the query.

• Export large result sets to Object Storage Service (OSS) using external tables instead of returning them to the client. Only CSV and Parquet files are supported for export. For more information, see Use external tables to export data from AnalyticDB for MySQL to OSS.

Large amounts of memory resources consumed by a query

Problem: A query is consuming a large amount of memory resources. This can cause execution failures in other queries, reduce execution speeds, and affect the overall stability of your AnalyticDB for MySQL cluster. Check Peak Memory in the Query Properties section of the query details page to confirm the memory usage. For more information, see View query properties.

Fix: Use execution plans in SQL diagnostics to locate the stage or operator consuming excessive memory, then apply the appropriate optimization:

• For queries that are slow due to high memory usage, see Slow queries that consume memory resources.

• For a step-by-step walkthrough of reading execution plans, see Use execution plans to analyze queries.

Large number of stages generated for a query

Problem: A query is generating a large number of stages, which consumes network resources, increases system processing complexity, and poses risks to overall cluster stability. The greater the number of joins in SQL statements, the greater the number of stages generated. For background on what affects stage count, see Factors that affect query performance.

Fix: Reduce the number of stages generated:

• Join tables before writing data into AnalyticDB for MySQL to reduce the total number of tables in the cluster.

• Reduce the number of joins in SQL statements. Fewer joins produce fewer stages.

• Use materialized views. AnalyticDB for MySQL reuses stages when executing queries against materialized views, which lowers the total stage count. For more information, see Overview.

Large amount of data read by a query

Problem: A query is scanning a large amount of data, consuming disk I/O resources and affecting the performance of other queries and data writes. Check Scanned Data in the Query Properties section of the query details page to confirm the scan volume. For more information, see View query properties.

Fix: First, identify the stage and TableScan operator responsible for the large scan. In the Statistics section of the execution plan (stage level), check Scanned Rows and Scan Size. In the Statistics section at the operator level, check Input Rows and Amount of Input Data for the TableScan operator. For reference, see State statistics and Operator statistics.

After identifying the TableScan operator with the large scan, use one of the following approaches to reduce the scan volume:

• Add an AND filter condition to the query.

• Adjust existing filter conditions to reduce the amount of data filtered at the scan stage.

• Check whether any filter conditions are not pushed down to the scan. If so, follow the optimization suggestions in Filter conditions are not pushed down.