Spark SQL diagnostics
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
Actions
Click Diagnose to view the execution details, including the full SQL statement and the diagnostic results.
SQL
The executed SQL statement.
Query ID
The ID of the query.
Execution ID
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
Start Time
The time when the query is submitted.
Execution Duration
The amount of time consumed to execute the SQL statement.
Maximum Operator Exclusive Time
The maximum execution time during which an operator uses resources exclusively.
Peak Memory
The peak memory usage of the query.
Scanned Data
The amount of data returned from the storage layer to the compute layer.
-
-
Find the SQL query you want to diagnose and click Diagnose in the Actions column.
-
On the Query Properties page, click the Execution Plan tab to view 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.