# debug-slow-spark-job

Adopt this skill: https://proskills.md/skills/other/embrasureai-spark-observability-skills-skills-debug-slow-spark-job

Page: https://proskills.md/skills/other/embrasureai-spark-observability-skills-skills-debug-slow-spark-job
This document is the public skill bundle for that page. It contains SKILL.md and every helper file the catalog carries. No login is required.

## File: skills/debug-slow-spark-job/SKILL.md
size: 7561
sha: 94cad0924160b8b816d3797a234baf92ac4dceb7
upstream: https://github.com/embrasureai/spark-observability-skills/blob/main/skills/debug-slow-spark-job/SKILL.md

````text
---
name: debug-slow-spark-job
description: Diagnose slow, expensive, or regressed Apache Spark and PySpark applications by comparing runtime evidence against a healthy run. Use for long stages, stragglers, skew, shuffle, spill, garbage collection, poor parallelism, small files, slow scans, scheduler delay, executor imbalance, and unexplained compute-cost growth.
---

# Debug a slow Spark job

Find the root cause of the slowdown or cost growth. Compare against a healthy run whenever one exists, and normalize for input size before calling anything a regression.

## Get the evidence

```bash
export SPARK_HISTORY_URL="https://history.example.com"
python3 scripts/spark_history_api.py slow --app-id <slow-application-id> > /tmp/spark-slow.json
python3 scripts/spark_history_api.py slow --app-id <healthy-application-id> > /tmp/spark-healthy.json
```

Run from this skill directory. If `SPARK_HISTORY_URL` is unset, find the server before asking the user: try `http://localhost:18080`, a running application's UI on `http://localhost:4040`, and the history-server or eventLog settings in the local Spark config; ask only when nothing responds. Authentication comes from `SPARK_HISTORY_AUTHORIZATION`, `SPARK_HISTORY_COOKIE`, or `SPARK_HISTORY_HEADERS_JSON`; never ask for credentials in chat and never disable TLS verification (`--ca-file` for a private CA).

Other subcommands: `applications [--status completed|running]` to find app IDs, `sql-list --app-id <id>` and `sql --app-id <id> --execution-id <n>` for the executed plan with per-node metrics, `failure --app-id <id>` for failed-stage detail; all accept `--stage-limit N --task-limit N`. For anything the profiles omit, call `$SPARK_HISTORY_URL/api/v1` directly with the same auth headers: `/applications/{app}/jobs`, `/stages/{stage}/{attempt}/taskSummary?quantiles=0.05,0.5,0.95`, `/stages/{stage}/{attempt}/taskList?sortBy=-runtime`, `/allexecutors`, `/environment`, `/sql/{execution}?details=true&planDescription=true`.

In `taskSummary`, metric arrays align with `quantiles` `[0, 0.5, 0.95, 0.99, 1.0]`: the middle entry is the median, the last is the max. Read `spark.master` and deploy mode from `environment.sparkProperties` to know where driver and executor logs live. Compare stage timelines between the two runs and start where they first diverge; wall time alone mixes queue time, driver work, execution, and commit.

## Likeliest causes, in order, and how to check each

1. **Skew.** Max far above median for run time, input, or shuffle read in one stage:

   ```bash
   jq '.longestStages[] | {stage: .stage.stageId, quantiles: .taskSummary.quantiles, run: .taskSummary.executorRunTime, input: .taskSummary.inputMetrics.bytesRead, shuffleRead: .taskSummary.shuffleReadMetrics.readBytes}' /tmp/spark-slow.json
   ```

   `.longestStages[].tasks` names the hot partitions and hosts. Check the executed plan (`sql` subcommand) for AQE skew handling (`skewed=true`) before proposing salting.

2. **Wrong partition count.** Tasks uniformly large and spilling mean too few; tens of thousands of sub-second tasks mean scheduler overhead:

   ```bash
   jq '{stageTasks: [.longestStages[].stage | {id: .stageId, tasks: .numTasks}], totalCores: ([.allExecutors[] | select(.isActive) | .totalCores] | add), conf: [.environment.sparkProperties[] | select(.[0] | test("shuffle.partitions|executor.cores"))]}' /tmp/spark-slow.json
   ```

   Try `spark.sql.shuffle.partitions` or AQE targets before inserting explicit repartitions.

3. **Excess spill.** Spill without skew means operator state outgrew execution memory:

   ```bash
   jq '.longestStages[] | {stage: .stage.stageId, memSpill: .taskSummary.memoryBytesSpilled, diskSpill: .taskSummary.diskBytesSpilled}' /tmp/spark-slow.json
   ```

   Reduce state (narrower rows, partial aggregation) or raise partitions before raising memory.

4. **Large shuffles.** Shuffle bytes dominating stage runtime:

   ```bash
   jq '.longestStages[].stage | {id: .stageId, shuffleRead: .shuffleReadBytes, shuffleWrite: .shuffleWriteBytes, run: .executorRunTime}' /tmp/spark-slow.json
   ```

   Map the stage to its `Exchange` in the executed plan and ask whether the shuffle is avoidable: broadcast, pre-aggregation, or already-partitioned data.

5. **Slow shuffle fetch.** Low CPU with high fetch wait or heavy remote reads:

   ```bash
   jq '.longestStages[] | {stage: .stage.stageId, fetchWait: .taskSummary.shuffleReadMetrics.fetchWaitTime, remote: .taskSummary.shuffleReadMetrics.remoteBytesRead}' /tmp/spark-slow.json
   ```

   Dead entries in `.allExecutors[] | select(.isActive | not)` mean data was refetched or recomputed. If `environment` shows Celeborn or an external shuffle service, confirm from the driver log it actually served the shuffle before tuning it.

6. **GC pressure.** GC a large fraction of run time:

   ```bash
   jq '{stages: [.longestStages[] | {id: .stage.stageId, gc: .taskSummary.jvmGcTime, run: .taskSummary.executorRunTime}], executors: [.allExecutors[] | {id, gc: .totalGCTime, dur: .totalDuration}]}' /tmp/spark-slow.json
   ```

   Check peak memory, cache use, and object-heavy code before resizing heaps.

7. **Python UDF transport.** Time concentrated at Python boundaries in the plan:

   ```bash
   python3 scripts/spark_history_api.py sql --app-id <id> --execution-id <n> | jq '.sqlExecution.nodes[] | select(.nodeName | test("Python")) | {nodeName, metrics}'
   ```

   Prefer native expressions or vectorized UDFs over resource changes.

8. **Retry churn.** Failures that succeeded on retry inflate runtime without failing the job:

   ```bash
   jq '{stages: [.longestStages[].stage | select(.numFailedTasks > 0) | {id: .stageId, failed: .numFailedTasks}], jobs: [.jobs[] | select(.numFailedTasks > 0) | {jobId, failed: .numFailedTasks}]}' /tmp/spark-slow.json
   ```

   Find the flaky cause (one bad host, preemption, timeouts) in the executor log or node events.

9. **Scan overhead.** Long scans with little output:

   ```bash
   python3 scripts/spark_history_api.py sql --app-id <id> --execution-id <n> | jq '.sqlExecution.nodes[] | select(.nodeName | startswith("Scan")) | {nodeName, metrics}'
   ```

   Too many small files, missing partition or pushed filters, or slow storage.

10. **Driver and queue time.** Gaps before the first task or between jobs:

    ```bash
    jq '[.jobs[] | {jobId, submissionTime, completionTime}] | sort_by(.submissionTime)' /tmp/spark-slow.json
    ```

    That time is planning, file listing, queueing, or provisioning: check the driver log for what it was doing, not stage tuning.

The commands above are starting points, not limits: compose your own jq, call the REST API directly, or pull logs, code, and platform state when a question needs it. Rule each candidate in or out with evidence, and when a signal is suggestive but not conclusive, go a level deeper (more task samples, the exact log lines, the executed plan) until you are confident it is or is not the cause. Do not settle for the first plausible explanation.

For a regression, end by naming what changed: code, data volume or distribution, configuration (diff the two snapshots' `environment`), or infrastructure. Adding memory for a skewed partition, adding executors when task count caps parallelism, and caching once-used data are common wrong answers.

## Report

State the root cause and confidence, the first divergent stage, the evidence for and against, alternatives you rejected, and the most likely fix. Do not change production settings or launch expensive reruns without approval.
````

## File: skills/debug-slow-spark-job/scripts/spark_history_api.py
size: 10866
sha: 395e7a319d35677abbe988a6ea4fdb2b7adc0109
upstream: https://github.com/embrasureai/spark-observability-skills/blob/main/skills/debug-slow-spark-job/scripts/spark_history_api.py

```text
#!/usr/bin/env python3
"""Read bounded diagnostic evidence from the Spark History Server REST API."""

from __future__ import annotations

import argparse
import json
import os
import re
import ssl
import sys
from datetime import datetime
from typing import Any
from urllib.error import HTTPError, URLError
from urllib.parse import quote, urlencode
from urllib.request import Request, urlopen

MAX_RESPONSE_BYTES = 25 * 1024 * 1024
SENSITIVE = re.compile(r"(secret|password|token|credential|access[._-]?key|private[._-]?key|keytab|cookie)", re.I)

class HistoryClient:
    def __init__(self, base_url: str, timeout: float, ca_file: str | None) -> None:
        base = base_url.rstrip("/")
        self.base = base if base.endswith("/api/v1") else f"{base}/api/v1"
        self.timeout = timeout
        self.context = ssl.create_default_context(cafile=ca_file)
        self.headers = {"Accept": "application/json", "User-Agent": "spark-history-skill/1"}
        authorization = os.getenv("SPARK_HISTORY_AUTHORIZATION")
        cookie = os.getenv("SPARK_HISTORY_COOKIE")
        if authorization:
            self.headers["Authorization"] = authorization
        if cookie:
            self.headers["Cookie"] = cookie
        extra = os.getenv("SPARK_HISTORY_HEADERS_JSON")
        if extra:
            parsed = json.loads(extra)
            if not isinstance(parsed, dict):
                raise ValueError("SPARK_HISTORY_HEADERS_JSON must be a JSON object")
            for key, value in parsed.items():
                if not isinstance(key, str) or not isinstance(value, str) or "\n" in key + value or "\r" in key + value:
                    raise ValueError("History headers must be single-line strings")
                self.headers[key] = value

    def get(self, path: str, **query: Any) -> Any:
        params = [(key, item) for key, value in query.items() if value is not None for item in (value if isinstance(value, list) else [value])]
        url = f"{self.base}/{path.lstrip('/')}"
        if params:
            url += "?" + urlencode(params)
        request = Request(url, headers=self.headers, method="GET")
        try:
            with urlopen(request, timeout=self.timeout, context=self.context) as response:
                length = response.headers.get("Content-Length")
                if length and int(length) > MAX_RESPONSE_BYTES:
                    raise RuntimeError(f"History response exceeds {MAX_RESPONSE_BYTES} bytes")
                body = response.read(MAX_RESPONSE_BYTES + 1)
        except HTTPError as exc:
            raise RuntimeError(f"Spark History API returned HTTP {exc.code} for {path}") from exc
        except URLError as exc:
            raise RuntimeError(f"Could not reach Spark History API for {path}: {exc.reason}") from exc
        if len(body) > MAX_RESPONSE_BYTES:
            raise RuntimeError(f"History response exceeds {MAX_RESPONSE_BYTES} bytes")
        return json.loads(body)

def app_path(app_id: str, suffix: str) -> str:
    return f"applications/{quote(app_id, safe='/')}/{suffix.lstrip('/')}"

def sanitize_environment(payload: Any) -> dict[str, Any]:
    if not isinstance(payload, dict):
        return {"available": False}
    result: dict[str, Any] = {}
    if "runtime" in payload:
        result["runtime"] = payload["runtime"]
    properties = []
    for entry in payload.get("sparkProperties", []):
        if not isinstance(entry, list) or len(entry) < 2:
            continue
        key, value = str(entry[0]), entry[1]
        properties.append([key, "<redacted>" if SENSITIVE.search(key) else value])
    result["sparkProperties"] = properties
    system = {}
    for entry in payload.get("systemProperties", []):
        if isinstance(entry, list) and len(entry) >= 2 and entry[0] in {"java.version", "java.vendor", "os.name", "os.version", "os.arch"}:
            system[str(entry[0])] = entry[1]
    result["systemProperties"] = system
    return result

def stage_duration_ms(stage: dict[str, Any]) -> float:
    def parse(value: Any) -> datetime | None:
        if not isinstance(value, str):
            return None
        try:
            return datetime.fromisoformat(value.replace("GMT", "+00:00").replace("Z", "+00:00"))
        except ValueError:
            return None
    start, end = parse(stage.get("submissionTime")), parse(stage.get("completionTime"))
    if start and end:
        return max(0.0, (end - start).total_seconds() * 1000)
    return float(stage.get("executorRunTime") or 0)

def top_stages(stages: Any, limit: int) -> list[dict[str, Any]]:
    if not isinstance(stages, list):
        return []
    return sorted(stages, key=stage_duration_ms, reverse=True)[:limit]

def earliest_stages(stages: Any, limit: int) -> list[dict[str, Any]]:
    if not isinstance(stages, list):
        return []
    return sorted(stages, key=lambda stage: str(stage.get("submissionTime") or ""))[:limit]

def stage_evidence(client: HistoryClient, app_id: str, stages: list[dict[str, Any]], task_limit: int, failed_only: bool) -> list[dict[str, Any]]:
    evidence = []
    for stage in stages:
        stage_id = stage.get("stageId")
        attempt_id = stage.get("attemptId", 0)
        if stage_id is None:
            continue
        root = app_path(app_id, f"stages/{stage_id}/{attempt_id}")
        item = {"stage": stage}
        try:
            item["taskSummary"] = client.get(f"{root}/taskSummary", quantiles="0.0,0.5,0.95,0.99,1.0")
            item["tasks"] = client.get(
                f"{root}/taskList",
                offset=0,
                length=task_limit,
                sortBy=None if failed_only else "-runtime",
                status="failed" if failed_only else None,
            )
        except RuntimeError as exc:
            item["collectionError"] = str(exc)
        evidence.append(item)
    return evidence

def collect_failure(client: HistoryClient, app_id: str, stage_limit: int, task_limit: int) -> dict[str, Any]:
    failed_stages = client.get(app_path(app_id, "stages"), status="failed")
    return {
        "profile": "failure",
        "applicationId": app_id,
        "failedJobs": client.get(app_path(app_id, "jobs"), status="failed"),
        "failedStages": stage_evidence(client, app_id, earliest_stages(failed_stages, stage_limit), task_limit, True),
        "allExecutors": client.get(app_path(app_id, "allexecutors")),
        "environment": sanitize_environment(client.get(app_path(app_id, "environment"))),
        "sqlExecutions": client.get(app_path(app_id, "sql"), details="false", planDescription="false", offset=0, length=100),
        "limitations": ["Driver logs and cluster-manager termination details are not part of this REST snapshot."],
    }

def collect_slow(client: HistoryClient, app_id: str, stage_limit: int, task_limit: int) -> dict[str, Any]:
    stages = client.get(
        app_path(app_id, "stages"),
        withSummaries="true",
        quantiles="0.0,0.5,0.95,0.99,1.0",
    )
    selected = top_stages(stages, stage_limit)
    return {
        "profile": "slow",
        "applicationId": app_id,
        "jobs": client.get(app_path(app_id, "jobs")),
        "longestStages": stage_evidence(client, app_id, selected, task_limit, False),
        "allExecutors": client.get(app_path(app_id, "allexecutors")),
        "environment": sanitize_environment(client.get(app_path(app_id, "environment"))),
        "sqlExecutions": client.get(app_path(app_id, "sql"), details="false", planDescription="false", offset=0, length=100),
    }

def collect_sql(client: HistoryClient, app_id: str, execution_id: str, stage_limit: int, task_limit: int) -> dict[str, Any]:
    stages = client.get(
        app_path(app_id, "stages"),
        withSummaries="true",
        quantiles="0.0,0.5,0.95,0.99,1.0",
    )
    return {
        "profile": "sql",
        "applicationId": app_id,
        "executionId": execution_id,
        "sqlExecution": client.get(
            app_path(app_id, f"sql/{quote(execution_id, safe='')}"),
            details="true",
            planDescription="true",
        ),
        "jobs": client.get(app_path(app_id, "jobs")),
        "longestStages": stage_evidence(client, app_id, top_stages(stages, stage_limit), task_limit, False),
        "environment": sanitize_environment(client.get(app_path(app_id, "environment"))),
    }

def parser() -> argparse.ArgumentParser:
    shared = argparse.ArgumentParser(add_help=False)
    shared.add_argument("--base-url", default=os.getenv("SPARK_HISTORY_URL"), help="History Server root or /api/v1 URL; defaults to SPARK_HISTORY_URL")
    shared.add_argument("--timeout", type=float, default=20.0)
    shared.add_argument("--ca-file", default=None, help="Custom CA bundle; TLS verification remains enabled")
    shared.add_argument("--stage-limit", type=int, default=8)
    shared.add_argument("--task-limit", type=int, default=20)
    result = argparse.ArgumentParser(description=__doc__)
    sub = result.add_subparsers(dest="command", required=True)
    apps = sub.add_parser("applications", parents=[shared])
    apps.add_argument("--status", choices=["completed", "running"])
    apps.add_argument("--limit", type=int, default=50)
    sql_list = sub.add_parser("sql-list", parents=[shared])
    sql_list.add_argument("--app-id", required=True)
    sql_list.add_argument("--limit", type=int, default=100)
    for name in ("failure", "slow", "sql"):
        command = sub.add_parser(name, parents=[shared])
        command.add_argument("--app-id", required=True)
        if name == "sql":
            command.add_argument("--execution-id", required=True)
    return result

def main() -> int:
    args = parser().parse_args()
    if not args.base_url:
        raise SystemExit("Set --base-url or SPARK_HISTORY_URL")
    if not 1 <= args.stage_limit <= 50 or not 1 <= args.task_limit <= 200:
        raise SystemExit("Limits out of range: stage 1-50, task 1-200")
    client = HistoryClient(args.base_url, args.timeout, args.ca_file)
    if args.command == "applications":
        payload = client.get("applications", status=args.status, limit=args.limit)
    elif args.command == "sql-list":
        payload = client.get(
            app_path(args.app_id, "sql"),
            details="false",
            planDescription="false",
            offset=0,
            length=args.limit,
        )
    elif args.command == "failure":
        payload = collect_failure(client, args.app_id, args.stage_limit, args.task_limit)
    elif args.command == "slow":
        payload = collect_slow(client, args.app_id, args.stage_limit, args.task_limit)
    else:
        payload = collect_sql(client, args.app_id, args.execution_id, args.stage_limit, args.task_limit)
    json.dump(payload, sys.stdout, indent=2, sort_keys=True)
    sys.stdout.write("\n")
    return 0

if __name__ == "__main__":
    raise SystemExit(main())
```

## File: README.md
upstream: https://github.com/embrasureai/spark-observability-skills/blob/main/README.md

````text
# Spark observability skills

Open-source agent skills from [Embrasure](https://embrasure.ai) for diagnosing and optimizing [Apache Spark](https://spark.apache.org) workloads. Each skill is a single `SKILL.md` with an ordered list of the highest-impact causes to check, plus a read-only Spark History Server REST client under `scripts/` that collects the runtime evidence in one bounded snapshot.

## Install

```bash
git clone https://github.com/EmbrasureAI/spark-observability-skills.git
ln -s "$PWD"/spark-observability-skills/skills/* ~/.codex/skills/
```

Point the symlinks at whichever skills directory your harness reads (`~/.codex/skills`, `~/.claude/skills`, ...), creating it first if needed, then restart or reload the harness.

Or paste this into your agent:

> Clone https://github.com/EmbrasureAI/spark-observability-skills and symlink each directory under `skills/` into your skills directory, then tell me to reload.

## Setup

The skills need HTTP access to a Spark History Server, or to the live UI of a running application (the driver UI on port 4040 serves the same REST API):

```bash
export SPARK_HISTORY_URL="https://<your-history-server>"   # e.g. http://localhost:18080 locally or via a tunnel
export SPARK_HISTORY_AUTHORIZATION="Bearer <token>"        # only if the server requires auth
```

- History data exists only for applications that ran with `spark.eventLog.enabled=true`.
- If the server is cluster-internal, open a tunnel first, for example `kubectl port-forward svc/spark-history-server 18080:18080`.
- Behind an SSO proxy, reuse your browser session with `SPARK_HISTORY_COOKIE` or `SPARK_HISTORY_HEADERS_JSON`; pass `--ca-file` for a private CA.

## Skills

- [Debug Spark failures](skills/debug-spark-failure/SKILL.md): a run failed. Trace driver and executor crashes, out-of-memory kills, fetch failures, task exceptions, and aborted stages back to the earliest supported cause instead of the last retry error.
- [Debug slow Spark jobs](skills/debug-slow-spark-job/SKILL.md): a run is slower or more expensive than it should be. Compare against a healthy run to localize the first divergence: skew, shuffle, spill, GC, poor parallelism, scheduler delay, or infrastructure.
- [Optimize Spark SQL plans](skills/optimize-spark-sql-plan/SKILL.md): a query works but costs too much. Read its final adaptive plan and runtime metrics to cut scans, shuffles, joins, and unnecessary work without changing query results.

## Safety

The collector is read-only, bounds large responses by default, redacts sensitive Spark properties, and keeps TLS verification enabled. Review every command against your environment and access policies before running it.

## Contributing

Each skill directory is self-contained so it can be symlinked or copied on its own. As a result, `scripts/spark_history_api.py` is intentionally identical across the three skills. If you change one copy, sync all three.

## License

Apache-2.0. Apache Spark, Apache Celeborn, and their respective marks belong to the Apache Software Foundation. This project is not an official Apache Software Foundation project.
````
