perf-profile

name	perf-profile
version	1.0.0
description	Performance profiling — measures code execution time, DB call time, and identifies bottlenecks at both the application and database layer. Use when asked to "profile performance", "measure execution time", "find bottlenecks", "check response times", or "why is this slow".
triggers	["profile performance","measure execution time","find bottlenecks","why is this slow","check response times","performance profiling"]
allowed-tools	["Bash","Read","Grep","Glob"]

/perf-profile

Profile application performance: code execution time, DB call time, and bottleneck identification at both the application and database layer.

Step 1: Identify the target

Ask the user (or infer from context): what are we profiling?

• A specific endpoint / route
• A background job / worker
• A specific function or module
• The whole application (general audit)

Step 2: DB call time profiling

PostgreSQL

# Enable timing in psql
psql "$DATABASE_URL" -c "\timing on"

# Check pg_stat_statements for query timing (if available)
psql "$DATABASE_URL" -c "
  SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND((total_exec_time / SUM(total_exec_time) OVER ()) * 100, 1) AS pct_total
  FROM pg_stat_statements
  ORDER BY mean_exec_time DESC
  LIMIT 20;
" 2>/dev/null || echo "pg_stat_statements not available — enable with: CREATE EXTENSION pg_stat_statements;"

MySQL / MariaDB

mysql -e "
  SELECT
    LEFT(DIGEST_TEXT, 80) AS query,
    COUNT_STAR AS calls,
    ROUND(AVG_TIMER_WAIT/1e9, 2) AS mean_ms,
    ROUND(SUM_TIMER_WAIT/1e9, 2) AS total_ms
  FROM performance_schema.events_statements_summary_by_digest
  ORDER BY AVG_TIMER_WAIT DESC
  LIMIT 20;
" 2>/dev/null || echo "MySQL performance_schema unavailable"

Report: table of top 20 queries by mean execution time, with call count and % of total DB time.

Step 3: Application-level profiling

Detect the runtime and suggest the right profiler:

# Detect language/framework
[ -f package.json ] && echo "RUNTIME=node"
[ -f Gemfile ] && echo "RUNTIME=ruby"
[ -f requirements.txt ] || [ -f pyproject.toml ] && echo "RUNTIME=python"
[ -f go.mod ] && echo "RUNTIME=go"
[ -f pom.xml ] || [ -f build.gradle ] && echo "RUNTIME=java"

Node.js:

• Built-in: node --prof app.js + node --prof-process isolate-*.log
• For web: add --inspect and use Chrome DevTools profiler
• Suggest: clinic.js (npx clinic doctor -- node app.js) for flame graphs

Ruby:

• Suggest: stackprof or rack-mini-profiler for Rails
• For a specific method: wrap with Benchmark.measure { ... }

Python:

• Built-in: python -m cProfile -s cumulative script.py
• For web: py-spy top --pid <pid> for live profiling
• Suggest: snakeviz for visualization

Go:

• Built-in: go tool pprof http://localhost:6060/debug/pprof/profile
• Add import _ "net/http/pprof" to enable the endpoint

Scan for existing timing instrumentation:

grep -rn "benchmark\|timing\|elapsed\|duration\|stopwatch\|time\.now\|Time\.now\|perf_hook\|performance\.now" \
  --include="*.rb" --include="*.py" --include="*.js" --include="*.ts" --include="*.go" \
  . 2>/dev/null | head -20

Step 4: Code-level bottleneck scan

Scan for common performance anti-patterns in the codebase:

Synchronous blocking in async contexts:

grep -rn "fs\.readFileSync\|execSync\|spawnSync\|sleep\|time\.Sleep" \
  --include="*.js" --include="*.ts" --include="*.go" . 2>/dev/null | head -10

Missing pagination (unbounded queries):

grep -rn "\.all\b\|find_all\|SELECT \*\b" \
  --include="*.rb" --include="*.py" --include="*.js" --include="*.ts" . 2>/dev/null | head -10

Repeated expensive calls in loops:

grep -rn "each\|forEach\|for " \
  --include="*.rb" --include="*.py" --include="*.js" --include="*.ts" . 2>/dev/null | head -20

For each flagged file, read the context and assess impact.

Step 5: Response time targets

Evaluate findings against these thresholds:

Metric	Good	Acceptable	Needs Fix
DB query mean time	<10ms	<100ms	>100ms
DB calls per request	≤3	≤10	>10
Endpoint response time	<100ms	<500ms	>500ms
Background job duration	<1s	<30s	>30s

Step 6: Bottleneck report

## Performance Profile Report

### DB Layer
- Slowest query: <query> — Xms mean (Y calls/min)
- Total DB time as % of request: Z%
- Queries over 100ms: N

### Application Layer
- Blocking calls found: X
- Unbounded queries (no LIMIT): Y
- Loops with expensive operations: Z

### Top 3 Bottlenecks (by estimated impact)
1. <bottleneck> — estimated Xms saved per request
2. <bottleneck> — estimated Yms saved per request
3. <bottleneck> — estimated Zms saved per request

### Recommended profiling tools for this stack
- <tool 1>
- <tool 2>

Ask: "Want me to implement fixes for the top bottlenecks, or set up profiling instrumentation so you can measure before/after?"