Bottleneck Identification & Analysis

// ══════════════════════════════════════════════════════════════
// REAL PERFORMANCE INVESTIGATION SCENARIO
// Observation: p95 response time for GET /api/orders = 8 seconds under 100 users
// Expected SLA: p95 < 500ms
// ══════════════════════════════════════════════════════════════

// STEP 1: k6 per-URL breakdown shows GET /api/orders is the outlier
// k6 --out json=results.json results:
// http_req_duration{url:"/api/orders"}: p(95)=8132ms  ← 16x over SLA!
// http_req_duration{url:"/api/products"}: p(95)=245ms  ← Fine

// STEP 2: Server monitoring shows CPU normal, DB connections at 100%
// MySQL max_connections hit → requests wait in queue
// Fix attempt 1: Increase connection pool from 10 → 50

// STEP 3: Database slow query log during test:
// Query: SELECT * FROM orders o 
//        JOIN order_items i ON i.order_id = o.id
//        JOIN products p ON p.id = i.product_id  
//        WHERE o.user_id = ? 
//        ORDER BY o.created_at DESC
// Execution time: 2.4 seconds (on 500k orders table)
// EXPLAIN: Full table scan on order_items (NO INDEX on order_id!)

// STEP 4: Add missing index:
// CREATE INDEX idx_order_items_order_id ON order_items(order_id);
// CREATE INDEX idx_orders_user_id ON orders(user_id);
// Query execution time: 2400ms → 4ms (600x improvement!)

// STEP 5: Rerun load test after fixes:
// http_req_duration{url:"/api/orders"}: p(95)=87ms  ← Below SLA!

// ── BOTTLENECK TYPES AND THEIR SYMPTOMS ──────────────────────
const bottleneckPatterns = [
    {
        bottleneck: "Missing database index",
        symptoms: ["Slow as data grows", "CPU spikes on DB server", "EXPLAIN shows full table scan"],
        fix: "Add index on frequently queried/joined column"
    },
    {
        bottleneck: "N+1 query problem",
        symptoms: ["Response time scales linearly with result size", "1 page load → 51 SQL queries"],
        fix: "Use JOIN instead of separate queries per item"
    },
    {
        bottleneck: "Connection pool exhaustion",
        symptoms: ["Sudden response time spike at specific concurrency", "Requests queue, then all fail at once"],
        fix: "Increase pool size or add connection timeout with retry"
    },
    {
        bottleneck: "Memory leak",
        symptoms: ["Performance degrades over time (soak test)", "Memory grows continuously, never released"],
        fix: "Profile heap dumps; close connections/streams properly"
    },
    {
        bottleneck: "Inefficient algorithm",
        symptoms: ["CPU at 100% under moderate load", "One endpoint much slower than others with similar data volumes"],
        fix: "Profile with CPU profiler; optimize O(n²) to O(n log n) where possible"
    }
];