Multi-Agent Performance Profiling

Overview

Prescriptive workflow for spawning parallel profiling agents to comprehensively identify performance bottlenecks across multiple system layers. Successfully discovered that QuestDB ingests at 1.1M rows/sec (11x faster than target), proving database was NOT the bottleneck - CloudFront download was 90% of pipeline time.

When to Use This Skill

Use this skill when:

• Performance below SLO (e.g., 47K vs 100K rows/sec target)

• Multi-stage pipeline optimization (download → extract → parse → ingest)

• Database performance investigation

• Bottleneck identification in complex workflows

• Pre-optimization analysis (before making changes)

Key outcomes:

• Identify true bottleneck (vs assumed bottleneck)

• Quantify each stage's contribution to total time

• Prioritize optimizations by impact (P0/P1/P2)

• Avoid premature optimization of non-bottlenecks

Core Methodology

1. Multi-Layer Profiling Model (5-Agent Pattern)

Agent 1: Profiling (Instrumentation)

• Empirical timing of each pipeline stage

• Phase-boundary instrumentation with time.perf_counter()

• Memory profiling (peak usage, allocations)

• Bottleneck identification (% of total time)

Agent 2: Database Configuration Analysis

• Server settings review (WAL, heap, commit intervals)

• Production vs development config comparison

• Expected impact quantification (<5%, 10%, 50%)

Agent 3: Client Library Analysis

• API usage patterns (dataframe vs row-by-row)

• Buffer size tuning opportunities

• Auto-flush behavior analysis

Agent 4: Batch Size Analysis

• Current batch size validation

• Optimal batch range determination

• Memory overhead vs throughput tradeoff

Agent 5: Integration & Synthesis

• Consensus-building across agents

• Prioritization (P0/P1/P2) with impact quantification

• Implementation roadmap creation

2. Agent Orchestration Pattern

Parallel Execution (all 5 agents run simultaneously):

Agent 1 (Profiling) → [PARALLEL] Agent 2 (DB Config) → [PARALLEL] Agent 3 (Client Library) → [PARALLEL] Agent 4 (Batch Size) → [PARALLEL] Agent 5 (Integration) → [PARALLEL - reads tmp/ outputs from others]

Key Principle: No dependencies between investigation agents (1-4). Integration agent synthesizes findings.

Dynamic Todo Management:

• Start with investigation plan (5 agents)

• Spawn agents in parallel using single message with multiple Task tool calls

• Update todos as each agent completes

• Integration agent waits for all findings before synthesizing

3. Profiling Script Structure

Each agent produces:

• Investigation Script (e.g., profile_pipeline.py )

• time.perf_counter() instrumentation at phase boundaries

• Memory profiling with tracemalloc

• Structured output (phase, duration, % of total)

• Report (markdown with findings, recommendations, impact quantification)

• Evidence (benchmark results, config dumps, API traces)

Example Profiling Code:

import time

Profile multi-stage pipeline

def profile_pipeline(): results = {}

# Phase 1: Download
start = time.perf_counter()
data = download_from_cdn(url)
results["download"] = time.perf_counter() - start

# Phase 2: Extract
start = time.perf_counter()
csv_data = extract_zip(data)
results["extract"] = time.perf_counter() - start

# Phase 3: Parse
start = time.perf_counter()
df = parse_csv(csv_data)
results["parse"] = time.perf_counter() - start

# Phase 4: Ingest
start = time.perf_counter()
ingest_to_db(df)
results["ingest"] = time.perf_counter() - start

# Analysis
total = sum(results.values())
for phase, duration in results.items():
    pct = (duration / total) * 100
    print(f"{phase}: {duration:.3f}s ({pct:.1f}%)")

return results

4. Impact Quantification Framework

Priority Levels:

• P0 (Critical): >5x improvement, addresses primary bottleneck

• P1 (High): 2-5x improvement, secondary optimizations

• P2 (Medium): 1.2-2x improvement, quick wins

• P3 (Low): <1.2x improvement, minor tuning

Impact Reporting Format:

Recommendation: [Optimization Name] (P0/P1/P2) - [IMPACT LEVEL]

Impact: 🔴/🟠/🟡 Nx improvement Effort: High/Medium/Low (N days) Expected Improvement: CurrentK → TargetK rows/sec

Rationale:

• [Why this matters]
• [Supporting evidence from profiling]
• [Comparison to alternatives]

Implementation: [Code snippet or architecture description]

5. Consensus-Building Pattern

Integration Agent Responsibilities:

• Read all investigation reports (Agents 1-4)

• Identify consensus recommendations (all agents agree)

• Flag contradictions (agents disagree)

• Synthesize master integration report

• Create implementation roadmap (P0 → P1 → P2)

Consensus Criteria:

• ≥3/4 agents recommend same optimization → Consensus

• 2/4 agents recommend, 2/4 neutral → Investigate further

• Agents contradict (one says "optimize X", another says "X is not bottleneck") → Run tie-breaker experiment

Workflow: Step-by-Step

Step 1: Define Performance Problem

Input: Performance metric below SLO Output: Problem statement with baseline metrics

Example Problem Statement:

Performance Issue: BTCUSDT 1m ingestion at 47K rows/sec Target SLO: >100K rows/sec Gap: 53% below target Pipeline: CloudFront download → ZIP extract → CSV parse → QuestDB ILP ingest

Step 2: Create Investigation Plan

Directory Structure:

tmp/perf-optimization/ profiling/ # Agent 1 profile_pipeline.py PROFILING_REPORT.md questdb-config/ # Agent 2 CONFIG_ANALYSIS.md python-client/ # Agent 3 CLIENT_ANALYSIS.md batch-size/ # Agent 4 BATCH_ANALYSIS.md MASTER_INTEGRATION_REPORT.md # Agent 5

Agent Assignment:

• Agent 1: Empirical profiling (instrumentation)

• Agent 2: Database configuration analysis

• Agent 3: Client library usage analysis

• Agent 4: Batch size optimization analysis

• Agent 5: Synthesis and integration

Step 3: Spawn Agents in Parallel

IMPORTANT: Use single message with multiple Task tool calls for true parallelism

Example:

I'm going to spawn 5 parallel investigation agents:

[Uses Task tool 5 times in a single message]

• Agent 1: Profiling
• Agent 2: QuestDB Config
• Agent 3: Python Client
• Agent 4: Batch Size
• Agent 5: Integration (depends on others completing)

Execution:

All agents run simultaneously (user observes 5 parallel tool calls)

Each agent writes to its own tmp/ subdirectory

Integration agent polls for completed reports

Step 4: Wait for All Agents to Complete

Progress Tracking:

• Update todo list as each agent completes

• Integration agent polls tmp/ directory for report files

• Once 4/4 investigation reports exist → Integration agent synthesizes

Completion Criteria:

• All 4 investigation reports written

• Integration report synthesizes findings

• Master recommendations list created

Step 5: Review Master Integration Report

Report Structure:

Master Performance Optimization Integration Report

Executive Summary

• Critical discovery (what is/isn't the bottleneck)
• Key findings from each agent (1-sentence summary)

Top 3 Recommendations (Consensus)

1. [P0 Optimization] - HIGHEST IMPACT
2. [P1 Optimization] - HIGH IMPACT
3. [P2 Optimization] - QUICK WIN

Agent Investigation Summary

Agent 1: Profiling

Agent 2: Database Config

Agent 3: Client Library

Agent 4: Batch Size

Implementation Roadmap

Phase 1: P0 Optimizations (Week 1)

Phase 2: P1 Optimizations (Week 2)

Phase 3: P2 Quick Wins (As time permits)

Step 6: Implement Optimizations (P0 First)

For each recommendation:

• Implement highest-priority optimization (P0)

• Re-run profiling script

• Verify expected improvement achieved

• Update report with actual results

• Move to next priority (P1, P2, P3)

Example Implementation:

Before optimization

uv run python tmp/perf-optimization/profiling/profile_pipeline.py

Output: 47K rows/sec, download=857ms (90%)

Implement P0 recommendation (concurrent downloads)

[Make code changes]

After optimization

uv run python tmp/perf-optimization/profiling/profile_pipeline.py

Output: 450K rows/sec, download=90ms per symbol * 10 concurrent (90%)

Real-World Example: QuestDB Refactor Performance Investigation

Context: Pipeline achieving 47K rows/sec, target 100K rows/sec (53% below SLO)

Assumptions Before Investigation:

• QuestDB ILP ingestion is the bottleneck (4% of time)

• Need to tune database configuration

• Need to optimize Sender API usage

Findings After 5-Agent Investigation:

• Profiling Agent: CloudFront download is 90% of time (857ms), ILP ingest only 4% (40ms)

• QuestDB Config Agent: Database already optimal, tuning provides <5% improvement

• Python Client Agent: Sender API already optimal (using dataframe() bulk ingestion)

• Batch Size Agent: 44K batch size is within optimal range

• Integration Agent: Consensus recommendation - optimize download, NOT database

Top 3 Recommendations:

• 🔴 P0: Concurrent multi-symbol downloads (10-20x improvement)

• 🟠 P1: Multi-month pipeline parallelism (2x improvement)

• 🟡 P2: Streaming ZIP extraction (1.3x improvement)

Impact: Discovered database ingests at 1.1M rows/sec (11x faster than target) - proving database was never the bottleneck

Outcome: Avoided wasting 2-3 weeks optimizing database when download was the real bottleneck

Common Pitfalls

1. Profiling Only One Layer

❌ Bad: Profile database only, assume it's the bottleneck ✅ Good: Profile entire pipeline (download → extract → parse → ingest)

2. Serial Agent Execution

❌ Bad: Run Agent 1, wait, then run Agent 2, wait, etc. ✅ Good: Spawn all 5 agents in parallel using single message with multiple Task calls

3. Optimizing Without Profiling

❌ Bad: "Let's optimize the database config first" (assumption-driven) ✅ Good: Profile first, discover database is only 4% of time, optimize download instead

4. Ignoring Low-Hanging Fruit

❌ Bad: Only implement P0 (highest impact, highest effort) ✅ Good: Implement P2 quick wins (1.3x for 4-8 hours effort) while planning P0

5. Not Re-Profiling After Changes

❌ Bad: Implement optimization, assume it worked ✅ Good: Re-run profiling script, verify expected improvement achieved

Resources

scripts/

Not applicable - profiling scripts are project-specific (stored in tmp/perf-optimization/ )

references/

• profiling_template.py

• Template for phase-boundary instrumentation

• integration_report_template.md

• Template for master integration report

• impact_quantification_guide.md

• How to assess P0/P1/P2 priorities

assets/

Not applicable - profiling artifacts are project-specific

Troubleshooting

Issue Cause Solution

Agents running sequentially Using separate messages Spawn all agents in single message with multi-Task

Integration report empty Agent reports not written Wait for all 4 investigation agents to complete

Wrong bottleneck identified Single-layer profiling Profile entire pipeline, not just assumed layer

Profiling results vary No warmup runs Run 3-5 warmup iterations before measuring

Memory not profiled Missing tracemalloc Add tracemalloc instrumentation to profiling script

P0/P1 priority unclear No impact quantification Include expected Nx improvement for each finding

Consensus missing Agents not compared Integration agent must synthesize all 4 reports

Re-profile shows no change Caching effects Clear caches, restart services before re-profiling