perf-profile

/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
```bash
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?"