AnalyticDB for MySQL classifies slow queries by the resource they consume most: memory, CPU, or disk I/O. Each category has a key metric you can monitor in the SQL diagnostics feature, and each has a distinct set of root causes.
Slow queries that consume memory resources
Key metric: Peak Memory — the maximum memory a query uses during execution. Higher values indicate heavier memory consumption.
To identify memory-intensive queries, use the SQL diagnostics feature to search for long-running queries, then check Peak Memory in the Query Properties section or in the diagnosis results at different levels. For details, see View query properties and diagnostic results.
High memory consumption is typically caused by the following operations:
| Cause | How it consumes memory |
|---|---|
| GROUP BY | AnalyticDB for MySQL caches the column values referenced in GROUP BY clauses. The more unique values those columns contain, the more memory is used. |
| JOIN (hash join) | AnalyticDB for MySQL caches one table's data in memory to build the hash table. A larger cached table means more memory. |
| SORT | AnalyticDB for MySQL caches data in memory during sorting. More data to sort means more memory. |
| Window functions | AnalyticDB for MySQL caches data in memory when executing window functions. More input data means more memory. For details, see Window functions. |
Slow queries that consume CPU resources
Key metric: Time Consumed — the total execution duration of a query. Longer durations indicate heavier CPU consumption.
To identify CPU-intensive queries, use the SQL diagnostics feature to search for long-running queries, then check Time Consumed in the Query Properties section. For details, see View query properties.
High CPU consumption is typically caused by the following:
| Cause | How it consumes CPU |
|---|---|
| Filter conditions not pushed down to the storage layer | AnalyticDB for MySQL creates indexes for all columns by default. When indexes filter data, CPU usage drops significantly. If filter conditions are not pushed down, indexes cannot be used. For the scenarios where pushdown does not occur, see Filter conditions are not pushed down. |
| Filter operations embedded in join conditions | When a filter is part of a join condition rather than a standalone WHERE clause, AnalyticDB for MySQL joins the two tables first and then filters the result — without using any indexes. A large intermediate dataset from the join makes the subsequent filter operation CPU-intensive. |
| No join condition specified | Without a join condition, AnalyticDB for MySQL performs a Cartesian product: the result set has as many rows as the product of the row counts in the left and right tables. Cartesian products are highly CPU-intensive. |
Slow queries that consume disk I/O resources
Key metrics: Scanned Rows and Scan Size — the number of rows and the volume of data read from disk. Higher values indicate heavier disk I/O.
To identify I/O-intensive queries, use the SQL diagnostics feature to search for long-running queries, then check Scanned Rows and Scan Size in the Query Properties section or in the diagnosis results at different levels. For details, see View query properties and diagnostic results.
High disk I/O is typically caused by the following:
Filter conditions match only a small fraction of the data, reducing index efficiency and forcing a large number of index entries to be read.
Filter conditions are not pushed down to the storage layer, triggering a full scan of the source table.
Filter conditions are pushed down, but their filter scope is broad, so a large amount of data still needs to be scanned.
A large number of partitions must be scanned. More partitions generally mean more data to read.