This topic describes typical slow queries in AnalyticDB for MySQL and their causes.
Slow queries that consume memory resources
The maximum memory of a query specified by the Peak Memory parameter helps you evaluate the memory usage. Typically, the larger the maximum memory, the more the memory resources consumed.
You can use the SQL diagnostics feature to search for queries that take a long time to execute within a specific period of time. Then, you can view Peak Memory of a specific query in the Query Properties section or in the diagnosis results at different levels. For more information, see View query properties and diagnostic results.
- GROUP BY operations are performed in a stage.
AnalyticDB for MySQL caches values of the columns referenced in GROUP BY clauses to the memory. If the columns referenced in GROUP BY clauses have multiple unique values, large amounts of memory resources are consumed.
- JOIN operations are performed in a stage.
When hash joins are used, AnalyticDB for MySQL caches the data of a table to the memory. The larger the size of the cached table, the more memory resources that are consumed.
- SORT operations are performed in a stage.
When data is being sorted, AnalyticDB for MySQL caches the data to the memory. The more data that needs to be sorted, the more memory resources that are consumed.
- Window functions are executed in a stage.
When window functions are being executed, AnalyticDB for MySQL caches the data to the memory. The more data that needs to be executed by window functions, the more memory resources that are consumed. For more information, see Window functions.
Slow queries that consume CPU resources
The execution duration of a query specified by the Time Consumed parameter helps you evaluate the CPU utilization. Typically, the longer the execution duration, the more CPU resources that are consumed.
You can use the SQL diagnostics feature to search for queries that take a long time to execute within a specific period of time. Then, you can view Time Consumed of a specific query in the Query Properties section. For more information, see View query properties.
- Filter conditions are not pushed down to the storage layer. By default, AnalyticDB for MySQL creates indexes for all columns when you create a table. Indexes used to filter data can greatly reduce the CPU resources consumed. However, filter conditions are not pushed down in some scenarios. For more information, see Filter conditions are not pushed down.
- Filter operations exist in join conditions. If filter conditions are included in a join, AnalyticDB for MySQL first joins two tables and then filters the joined data. In this case, no indexes can be used for filtering. If a large amount of data is generated after the join, the filter operation consumes large amounts of CPU resources.
- No join condition is specified for a join. If no join condition is specified, AnalyticDB for MySQL performs the Cartesian product operation on the left and right tables. The number of rows generated is the product of the numbers of rows in the left and right tables. This type of operation consumes large amounts of CPU resources.
Slow queries that consume disk I/O resources
The number of scanned rows specified by the Scanned Rows parameter and the amount of scanned data specified by the Amount of Scanned Data parameter help you evaluate the disk I/O usage. Typically, the more the scanned rows and the larger the amount of scanned data, the more the disk I/O resources consumed.
You can use the SQL diagnostics feature to search for queries performed within a specified period of time that took a long time to execute. Then, you can view Scanned Rows and Scan Size of a specific query in the Query Properties section or in the diagnosis results at different levels. For more information, see View query properties and diagnostic results.
- The filter conditions filter only a small amount of data, which causes low index efficiency and large numbers of indexes to be read.
- Filter conditions are not pushed down. This causes a full scan of a source table.
- Filter conditions are pushed down. However, their filter scopes are large, and large amounts of data must be scanned.
- Large numbers of partitions must be scanned. Typically, more partitions indicate larger amounts of data to be scanned.