Performance Profiling & Bottleneck Analysis Questions

Queries and metrics to collect (to prove DB -> latency causality)

• Per-request traces/spans (distributed tracing): request span + DB span durations, timestamps, trace ids.
• DB-level: query text, normalized fingerprint, start/end timestamps, duration (p50/p95/p99), rows returned, query params.
• DB server metrics: CPU, memory, disk IO, iowait, context switches, swap, disk queue length.
• DB internals: locks/waits, cache hit ratio (buffer cache), index usage, connections, active vs idle, transaction count, long-running transactions.
• Aggregates over time: QPS per query, error rates, queueing length, connection pool exhaustion.
• App metrics: request rate, concurrency, request latency percentiles, thread/worker saturation, GC/pause times.
  Collect via tracing (OpenTelemetry/Jaeger), APM, pg_stat_statements (Postgres), slow query log, performance counters, and system monitoring (Prometheus + node_exporter).

How to profile DB queries

• Capture slow queries: enable slow_query_log / auto_explain with threshold (e.g., >100ms) to log plans.
• Use EXPLAIN (ANALYZE, BUFFERS) on representative slow queries to get actual runtime + I/O.
• Aggregate fingerprints with pg_stat_statements or pt-query-digest/pgbadger to find top time-consuming queries (total time, calls, mean, p95).
• Run EXPLAIN ANALYZE on production-like dataset; use pg_trace or performance_schema (MySQL) for waits.
• Use perf/strace or eBPF (bcc/tools) to check syscalls, I/O patterns, and CPU hotspots.
• Check index statistics (pg_stats), table bloat, stale stats (ANALYZE), and plan changes over time; capture historical plans.
• Reproduce with a load test (k6/Locust) to profile at scale and validate optimizations.

Application-side optimizations to reduce request latency

• Eliminate N+1 queries: eager load / JOINs or batch fetching.
• Cache responses or DB query results (in-memory, Redis) with appropriate TTL and invalidation strategy.
• Batch writes/reads where possible; use bulk operations to reduce round trips.
• Use read replicas for read-heavy traffic; route consistent reads appropriately.
• Tune connection pooling (pgbouncer/ HikariCP): avoid connection storms and queueing.
• Use prepared statements and parameterized queries to reduce parse/plan cost.
• Set sensible DB timeouts and deadline propagation to avoid backlog on threads.
• Move non-critical work to background jobs (async), return early to user.
• Reduce payload/columns selected, add proper indexes or composite indexes based on slow query EXPLAINs.
• Implement circuit breakers / rate limiting for DB-bound endpoints.
• Monitor and iterate: deploy one change at a time, run canary/load tests, verify via tracing that DB span reductions correspond to request latency improvement.

Example quick check workflow

1. Pull traces for high-latency requests -> confirm DB spans correlate with request tails.
2. Run pg_stat_statements to list top 10 by total_time and p99 latency.
3. EXPLAIN ANALYZE the top offenders, inspect indexes, and implement one change (index/add JOIN/batch).
4. Re-run traces and metrics to confirm p95/p99 request latency decreased.

Profiling in SRE is the systematic measurement of where a system spends resources (CPU, memory, I/O, network, lock contention) so you can target performance and reliability improvements with evidence. Main goals:

• Identify hotspots and bottlenecks (hot code paths, memory leaks, heavy syscalls)
• Quantify impact (cost, latency, error amplification)
• Reproduce and validate optimizations safely
• Guide capacity planning and SLO decisions

Common profiler types:

• Sampling profilers: periodically sample program state (stack traces) at intervals. Low overhead, good for CPU and latency hotspots; produces statistical view.
• Instrumentation profilers: insert timers/counters around functions or events. Higher fidelity and overhead; useful for precise timing, allocations, and custom metrics.

Typical metrics collected:

• CPU: CPU usage by process/thread, per-function CPU time
• Memory: heap size, allocation rate, GC pauses, object counts
• I/O: syscall counts, read/write bytes, latency distributions
• Network: bytes/sec, packet rates, per-call latency, retransmits
• Concurrency: thread counts, lock contention, wait times

Measure-first, change-later rationale:

• Prevents “optimizing the wrong thing” by grounding changes in data
• Ensures reproducibility: record baseline metrics and profiles, apply change, compare
• Keeps safety: small, verifiable changes reduce regressions; use canary rollouts and benchmarks
• Provides audit trail for cost/benefit and informed postmortems

Concrete practice: capture a sampling CPU profile and allocation profile in prod (low overhead), reproduce locally or in staging, design a minimal change, run A/B/canary with the same profiling to confirm improvement before full rollout.

Start by measuring, quantify the problem, locate hotspots, then fix algorithm/data layout. Key metrics: L1/L2/L3 miss counts and miss rates, MPKI (misses per 1k instructions), LLC misses, CPI, and memory bandwidth. Tools: Linux perf, Intel VTune, perf-tools/FlameGraphs, Valgrind Cachegrind, and hardware counters (pcm, likwid).

Example commands:

# summary counts and rates
perf stat -e cache-references,cache-misses,LLC-load-misses,LLC-store-misses,cycles,instructions ./app

# sample to find hot functions
perf record -g -e LLC-load-misses ./app; perf report --stdio

# cachegrind (detailed per-line)
valgrind --tool=cachegrind ./app
cg_annotate cachegrind.out.<pid>

How to interpret:

• Use MPKI = (cache misses / instructions) * 1000 to normalize across workloads. High MPKI (e.g., >10) at a hotspot indicates poor locality.
• Correlate CPI increases with memory-bound behavior: rising CPI with many LLC misses implies waiting on DRAM.
• Flamegraphs/profiler annotated by cache-miss events show call paths producing misses—identify functions allocating or traversing large data.

Common root causes & algorithm-level fixes:

• Poor spatial locality: switch from Array-of-Structs to Struct-of-Arrays, pack fields you iterate together.
• Poor temporal locality: reorganize loops to reuse hot data, loop interchange.
• Working set too large: apply blocking/tiling to reduce footprint (e.g., matrix multiply blocked).
• Excessive random access: use index reordering or radix/bucket approaches to make accesses sequential.
• False sharing in multithreaded code: pad shared cache-line variables or align to cache-line boundaries.
• Unnecessary precision/size: use smaller data types to fit more of working set in caches.
• Hardware prefetch: add software prefetch intrinsics where predictable streaming occurs; avoid over-prefetching.
• Data structure choice: prefer contiguous arrays and B-tree variants tuned for cache.

Verification and trade-offs:

• Measure before/after with same perf counters and MPKI/CPI comparisons.
• Consider trade-offs: tiling increases code complexity; prefetching risks wasted bandwidth; smaller types may lose precision.

In SRE context, automate these measurements in perf jobs or CI benchmarks on representative workloads, set alerts for sudden MPKI/CPI regressions, and include cache-aware performance tests in release gates.

Approach: Expose standard net/http/pprof endpoints (they are lightweight — sampling profiler) and protect them. In production, use the /debug/pprof/profile?seconds=30 endpoint (HTTP request triggers a 30s CPU sample) to avoid stopping the process. Store results to durable object storage for offline analysis.

Code (minimal, with basic auth middleware example):

package main

import (
	"log"
	"net/http"
	"net/http/pprof"
)

// simple basic auth middleware (replace with real auth in prod)
func basicAuth(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		user, pass, ok := r.BasicAuth()
		if !ok || user != "admin" || pass != "secret" {
			w.Header().Set("WWW-Authenticate", `Basic realm="pprof"`)
			http.Error(w, "unauthorized", http.StatusUnauthorized)
			return
		}
		next.ServeHTTP(w, r)
	})
}

func registerPprof(mux *http.ServeMux) {
	mux.HandleFunc("/debug/pprof/", pprof.Index)
	mux.HandleFunc("/debug/pprof/cmdline", pprof.Cmdline)
	mux.HandleFunc("/debug/pprof/profile", pprof.Profile) // accepts ?seconds=30
	mux.HandleFunc("/debug/pprof/symbol", pprof.Symbol)
	mux.HandleFunc("/debug/pprof/trace", pprof.Trace)
	mux.HandleFunc("/debug/pprof/heap", pprof.Handler("heap").ServeHTTP)
}

func main() {
	mux := http.NewServeMux()
	registerPprof(mux)

	// protect the entire pprof subtree
	http.Handle("/debug/pprof/", basicAuth(mux))
	// other app handlers...
	log.Println("listening :6060")
	log.Fatal(http.ListenAndServe(":6060", nil))
}

How to capture a 30s CPU profile with minimal disruption:

• Use the pprof HTTP endpoint: curl -sS "http://admin:secret@<host>:6060/debug/pprof/profile?seconds=30" -o cpu_30s.prof
  • This triggers the running process to sample CPU for 30s — no stop/start, low overhead (~1-5%).
• Prefer capturing from a single instance (not all replicas) or from a canary to avoid cluster-wide noise.
• If traffic must remain constant, capture on a non-production replica or temporarily drain traffic from that instance (use LB or pod eviction).
• For very sensitive services, run on a low-traffic window and keep duration short.

Where to store profiles for offline analysis:

• Save locally then upload to object storage (S3/GCS) with metadata (service, host, timestamp, git SHA, load).
  • Example: aws s3 cp cpu_30s.prof s3://my-bucket/profiles/serviceX/hostA/2025-11-22_cpu.prof
• Retain in a central profiling datastore (e.g., Parca, Speedscope-compatible storage) for querying and long-term retention.
• Ensure profiles are access-controlled and rotated/garbage-collected.

Analysis:

• Use go tool pprof: go tool pprof -http=:8080 /path/to/binary cpu_30s.prof
• Look for hot functions, contention, and off-CPU stacks.
• Correlate with logs/metrics and repeat capture if needed.

Operational notes:

• Always protect pprof endpoints (auth, network ACLs).
• Record runtime version and binary build id with the profile.
• For heap snapshots, use /debug/pprof/heap or runtime/pprof.WriteHeapProfile to get heap profiles.
• Automate capture and upload in runbooks or a tool (e.g., a small script or operator) for on-call use.

Requirements & goals:

• Toggle profiling per-service and per-host
• Low overhead by default; richer capture on demand
• Secure, rate-limited, auditable
• Integrates with alerting and incident workflows

Design overview:

• Lightweight in-process hooks: always-compiled thin probes (cpu, heap, eBPF if kernel), but idle by default (sampling=0.1%).
• Remote controller + local agent: central control plane stores toggles; agents poll or subscribe via secure channel to apply per-service/per-host configs.

API design (REST + gRPC for realtime):

• GET /config?service=svc-name&host=host-id -> returns {enabled:bool, mode: "low"/"burst"/"continuous", sample_rate:0.001, duration_sec:60, allowed_profiles:[cpu,alloc,trace]}
• POST /request-profile -> {service, host, mode, duration, reason, requester} returns job-id; controller validates and issues short-lived token to agent for richer capture.
• Webhook/gRPC stream to push immediate toggles for on-call overrides.

Modes:

• low: continuous ultra-low overhead sampling (statistical summaries)
• burst: higher-rate for short window (e.g., 60s) for troubleshooting
• continuous: sustained profiling allowed only for non-prod or with strict quotas

Security & audit:

• AuthN: mTLS + service accounts; RBAC on controller (who can request burst/continuous)
• AuthZ: fine-grained policies: only devs for their service, SREs for production, emergency role for on-call
• Tokens: short TTL per profiling job
• Audit logs: every toggle/request recorded with requester, reason, timestamps, and job-id; integrate with SIEM.

Rate limiting & safety:

• Global and per-host quotas: e.g., max 4 burst jobs per host per 24h; circuit-breaker if CPU or latency impact > threshold
• Profiler enforces runtime guardrails: if profiling raises CPU/latency beyond X% of baseline, automatically reduce sampling or abort and emit metric
• Backpressure: controller denies requests when cluster-wide profiling load high

Data capture & storage:

• Sampled flamegraphs, stack traces, allocation traces; metadata: service, host, git-revision, build-id, correlation-id (job-id)
• Upload to secure object store with TTL and retention policies; sensitive data scrubber for secrets in stacks

Integration with alerting:

• Emit metrics: profiling.active_jobs, profiling.cpu_overhead, profiling.aborts, profiling.requests_denied
• Alert rules:
  • High profiling overhead -> page SRE runbook and auto-disable profiling on impacted hosts
  • Unusual profiling requests (e.g., from unexpected principal or high frequency) -> security alert to on-call + audit
  • Excessive denied requests -> ops alert
• On incident runbook: from alert, on-call can trigger POST /request-profile with mode=burst; controller attaches job-id and auto-links profile artifacts to the incident ticket (e.g., PagerDuty/Slack link).

Operational considerations:

• Canary rollout of profiler to subset of hosts; measure overhead before broader enablement
• Simulate load & failure modes; ensure profiler self-throttles
• Provide SDK for apps to annotate traces and to opt-out specific code regions

Example config JSON returned by controller:
{
"enabled": true,
"mode": "low",
"sample_rate": 0.001,
"burst_quota_remaining": 3
}

This design prioritizes safety by default, gives controlled escalation for deeper diagnostics, enforces policy & quotas, and ties profiling events and metrics into alerting and incident workflows for fast, auditable response.