spark-sql-diagnosis

Spark SQL Diagnosis

When to Use

Use when the user provides any combination of:

• A Spark UI / History Server / YARN proxy address;
• A Spark SQL statement and its EXPLAIN FORMATTED;
• Kyuubi / Spark Driver runtime logs;
• /api/v1/... exported JSON or event logs.

The goal is not to "look at a few metrics," but to answer:

1. Which layer is the query slow in: scan, shuffle, join, aggregation, spill, GC, scheduling, cold start;
2. Is there data skew, and in which stage / task / SQL fragment;
3. Which issues are data problems, and which are resource or configuration problems;
4. What the next step should be: change the SQL, change the configuration, or fix the data.

Input Priority

Input	What It Can Do
UI Address + Execution ID / Submission Time + SQL	Most complete, task-level diagnosis
Runtime logs (with app ID and UI address) + SQL	Parse logs first, then access the UI; degrade to stage-level when UI is unavailable
EXPLAIN FORMATTED	Can only do plan structure and SQL fragment mapping
Event log / History Server	Fallback when UI has expired

Logs usually already contain:

application ID: application_xxx
application web UI: http://host:8088/proxy/application_xxx

So the user does not need to paste the UI address separately, but the agent must actually probe whether the address is accessible.

Workflow

1. Collect and Parse Input

• Extract app ID, UI address, operation ID, stage/job timeline, and Final Plan from logs;
• Extract table names, join conditions, group by / distinct / window / order by from the SQL text;
• Extract operators, input/output columns, join conditions, and aggregation keys from EXPLAIN FORMATTED.

2. Locate Actual Execution

• Prioritize using Execution ID;
• Otherwise, match by SQL text + submission time in the /sql list;
• /sql by default returns only a few records starting from offset=0; pagination or ID probing must be used;
• When there are multiple candidates, select the one with the closest time and matching description; if still ambiguous, ask the user.

3. Fetch Runtime Data

Run the script (recommended). The scripts/ path below is relative to this skill directory; if executing from another directory, please cd to the skill directory or use absolute paths.

python scripts/spark_diagnose.py \
  --url "http://host:8088/proxy/application_xxx" \
  --exec-id 94 \
  --sql-file query.sql \
  --explain explain.txt \
  --log run.log \
  --out report.md \
  --json report.json

If Execution ID is uncertain:

python scripts/spark_diagnose.py \
  --url "http://host:18080/history/application_xxx/" \
  --sql-file query.sql \
  --exec-time "2026-11-02T08:06:50Z" \
  --out report.md

Authentication parameters:

--cookie "name=value; name2=value2"
--header "X-Token: ..."
--user "user:password"
--kerberos          # use curl --negotiate
--insecure          # skip TLS validation, for intranet self-signed certs only

Read-only principle: The script only uses GET requests, does not execute SQL, and does not write to the cluster.

4. Interpret Metrics

The script outputs structured JSON and Markdown. Key areas to examine:

• Stage Timeline: Wall clock vs executorRunTime vs task launch delay;
• Task Distribution: max/median/p95 of duration, records, shuffle bytes;
• True Skew: One task's records and duration are both significantly above the median;
• False Long Tail: Task record count is normal, but executorDeserializeTime is high – usually a cold executor;
• Spill: memoryBytesSpilled / diskBytesSpilled, and spill size of plan nodes;
• Plan Nodes: Scan, Exchange, Join, Aggregate output rows / shuffle bytes / duration;
• Configuration: AQE, skew join threshold, shuffle partitions, broadcast threshold, dynamic allocation.

5. Write Final Report

The script provides evidence and candidate conclusions. The final response must supplement with SQL semantics:

• Operator → SQL fragment mapping;
• Business meaning of hot keys (if inferable from SQL / table creation logic);
• Priority and expected benefit of recommendations;
• Explicitly mark parts that require external verification.

Report structure:

1. Conclusion first (ordered by impact);
2. Execution overview and timeline;
3. Skew/long tail evidence table;
4. SQL plan mapping;
5. Configuration and risks;
6. Recommendations;
7. Evidence sources and limitations;
8. Issues still requiring external verification.

Key Judgment Rules

These rules must be followed to avoid the most common misdiagnoses:

1. Skew is judged by records and duration, not just shuffle bytes. A highly compressible hot key (e.g., many item_id=1) may only be a few MB but have 60% of the records.

2. Distinguish true skew from cold start. If a slow task has a normal record count but high executorDeserializeTime / duration, it's a cold executor, not data skew.

3. Partial aggregation can mask GROUP BY skew. When the plan contains partial_count / partial_sum, hot keys are compressed into a few rows before shuffle; check if the aggregation itself avoids skew.

4. count(DISTINCT) is a two-phase deduplication. The first shuffle's key often includes the distinct field (e.g., (item_id, buyer_id)), so hot spots may be spread; do not judge by ordinary GROUP BY logic.

5. AQE skew join is size-based. The judgment is based on compressed partition size vs skewedPartitionFactor / skewedPartitionThresholdInBytes; compressible hot keys may be below the threshold and not split.

6. Increasing shuffle partitions does not dilute a single hot key. A single key always goes to the same partition; more partitions only make other partitions smaller, increasing the max/median ratio.

7. When stage wall clock is much larger than task runtime, check scheduling first. Look at launchTime - submissionTime, dynamic allocation minExecutors, and YARN queue.

8. Only suspect join explosion when output row count is significantly larger than input row count. If the output row count equals the fact table row count, the problem is usually distribution, not duplicate keys.

Boundaries

• Cannot see actual data content; the specific values of hot keys need user data inspection;
• Specific reasons for YARN queue queuing and slow container allocation need RM/Kyuubi logs;
• UDF/custom code issues require code and executor logs;
• Without UI/event logs, normal driver logs can only do stage-level diagnosis;
• Does not execute SQL, modify data, or print cookies/tokens.

References

• references/endpoints.md: REST endpoints and pagination;
• references/metrics.md: Field differences and unit conversions;
• references/heuristics.md: Thresholds, plans, and skew judgment.