AnalyticDB for MySQL provides the Spark SQL diagnostics feature. If your Spark SQL queries have performance issues, you can use diagnostic information to quickly identify, analyze, and resolve performance bottlenecks to optimize Spark SQL queries. This topic describes how to perform Spark SQL diagnostics.
Prerequisites
An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
A database account is created for the AnalyticDB for MySQL cluster.
If you use an Alibaba Cloud account, you need to only create a privileged account.
If you use a Resource Access Management (RAM) user, you must create a privileged account and a standard account and associate the standard account with the RAM user.
AnalyticDB for MySQL is authorized to assume the AliyunADBSparkProcessingDataRole role to access other cloud resources.
Usage notes
You can perform diagnostics only on Spark SQL queries that are successfully executed in the last 14 days.
Procedure
Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
-
In the left-side navigation pane, choose .
The query list displays information about SQL queries for diagnostics. The following table describes the parameters of SQL queries.
Parameter
Description
Peak Memory
The Query ID operation that allows you to view execution details such as the SQL statement and diagnostic results.
Scanned Data
The executed SQL statement.
SQL
The ID of the query.
Started At
The sequence ID of the SQL statement in the Spark SQL application.
Status
The execution status of the SQL statement. Valid values:
-
Completed
-
Running
-
Failed
Execution Duration
The time when the query is submitted.
Query Properties
The amount of time consumed to execute the SQL statement.
Query-level Diagnostic Results
The maximum execution time during which an operator uses resources exclusively.
Execution ID
The peak memory usage of the query.
Maximum Operator Exclusive Time
The amount of data returned from the storage layer to the compute layer.
-
-
Find the SQL query that you want to diagnose and click Diagnostics and Optimization in the Spark SQL Diagnostics and Optimization column.
-
On the Execution Plan page, click the Execution Plan tab to view the hierarchy chart of the execution plan tree of the query.
-
Click Query-level Diagnostic Results to the right of the execution plan tree to view the query-level diagnostic results.
Query-level diagnostic results
AnalyticDB for MySQL provides the SQL diagnostics feature to collect query-level statistics on SQL queries for diagnostics and deliver optimization suggestions.
Data bloat in a join
-
Problem description:
If the number of output rows of a join operator is greater than the number of input rows, data bloat occurs because the join operation is considered unreasonable. As a result, a large number of computing and memory resources are occupied, which slows down the query.
-
Suggestions:
Optimize the code, such as filtering out data that is not involved in the join operation in advance to reduce the amount of data to be processed.
Skew in the amount of scanned data
-
Problem description:
If a significant difference exists in the amount of scanned data among multiple tables during the execution of a Spark SQL query, an extended period of time is required to read data due to the long tail effect, which affects the final query performance.
-
Suggestions:
Select appropriate distribution key columns to reduce the amount of scanned data.