Bài 57: sqlx Pool Benchmark + Observability

Sau bài học, bạn sẽ:

• Cài và dùng oha tool (Rust-native, faster than wrk) đo throughput.
• Benchmark Shop API GET /products baseline + variant pool config 5 mức.
• Hiểu API pool.size(), pool.num_idle(), pool.num_active().
• Expose metrics endpoint Prometheus exposition format text.
• Implement /health/ready deep check (kiểm pool + DB ping).
• Pattern alert pool gần full (capacity utilization > 80%).
• Hiểu lựa chọn metrics crate vs Prometheus exposition format manual.

oha (viết tắt oh-my-load-application) là HTTP load testing tool viết bằng Rust, mở source 2020 bởi Hatoo trên GitHub. So với wrk (C, 2012) và hey (Go, 2016), oha ergonomic hơn nhờ TUI real-time + JSON output cho CI parse.

Cài đặt 2 cách phổ biến:

cargo install oha
# hoặc trên macOS
brew install oha

Verify phiên bản:

oha --version
# oha 1.4.x

Tính năng chính khiến oha phù hợp Rust project:

• TUI real-time hiển thị latency histogram, RPS, status code distribution suốt quá trình test.
• HTTP/2 + HTTP/1.1 support — test endpoint axum sẵn HTTP/2 nếu enable.
• JSON output qua flag -j cho CI parse: oha -j ... > report.json rồi jq extract field.
• HTTP method, header, body custom flag -m POST, -H 'X-Foo: bar', -d '{"x":1}' đầy đủ.

Test khởi động đầu tiên ngay sau khi Shop API chạy:

oha -n 1000 -c 10 http://localhost:3000/health/live
# -n 1000: tổng request
# -c 10:   concurrent client
# -z 30s:  duration mode (thay -n)

Output sample của oha in ra terminal:

Summary:
  Success rate: 100.00%
  Total:        2.4123 secs
  Requests/sec: 4145.2

Status code distribution:
  [200] 1000 responses

Latency distribution:
  10% in 0.002 secs
  50% in 0.003 secs
  90% in 0.005 secs
  99% in 0.009 secs

Đây là baseline cho endpoint không chạm DB — bước kế tiếp benchmark endpoint thật GET /products qua sqlx pool.

Chuẩn bị data: insert 100 product vào DB cho list endpoint trả nhiều row:

for i in {1..100}; do
  curl -X POST http://localhost:3000/api/v1/products \
    -H 'Content-Type: application/json' \
    -d "{\"name\":\"Product $i\",\"slug\":\"product-$i\",\"price\":\"$((i * 10000)).00\",\"stock\":$i}"
done

Baseline test 30 giây với 50 concurrent:

oha -z 30s -c 50 'http://localhost:3000/api/v1/products?page=1&per_page=20'

Kết quả mẫu trên máy laptop tham khảo (không chạy thực, dùng để minh họa pattern phân tích):

Summary:
  Success rate:  100.00%
  Total:         30.0123 secs
  Slowest:       0.082 secs
  Fastest:       0.003 secs
  Average:       0.012 secs
  Requests/sec:  4180.5

Latency distribution:
  50% in 0.011 secs
  90% in 0.018 secs
  99% in 0.034 secs

Bài tập variant: chạy lại với 4 mức POOL_MAX_CONN khác nhau và ghi lại RPS + p99:

POOL_MAX_CONN=5   cargo run -p shop-api  # cùng oha test 30s
POOL_MAX_CONN=20  cargo run -p shop-api  # default
POOL_MAX_CONN=100 cargo run -p shop-api
POOL_MAX_CONN=200 cargo run -p shop-api

Bảng so sánh kết quả tham khảo (laptop 8 core + Postgres local default):

Config                      | RPS    | p99   | Nhận xét
----------------------------+--------+-------+------------------------
POOL_MAX_CONN=5             | 1200   |  95ms | pool saturate, queue chờ
POOL_MAX_CONN=20 (default)  | 4180   |  34ms | sweet spot
POOL_MAX_CONN=100           | 4250   |  35ms | diminishing return
POOL_MAX_CONN=200           | 3900   |  45ms | overhead worse

Kết luận từ data thực nghiệm: sweet spot quanh 20 max cho cấu hình dev/staging. Pool quá nhỏ (5) bị queue, pool quá lớn (200) thêm overhead Postgres process per connection + context switch + lock contention nội bộ pg_locks — RPS không tăng còn p99 tệ hơn. Quy luật chung từ PgBadger + benchmark community: pool size = (CPU cores × 2) + effective spindles cho workload OLTP.

Lock decision Shop API: 20 max là default OK cho dev/staging, production tune theo CPU cores DB server (vd RDS db.r6g.xlarge 4 core → 4 × 2 = 8-10 max per replica, scale ngang qua replica + PgBouncer chi tiết G18).

sqlx PgPool expose 2 method public về metrics, method thứ 3 tính bằng phép trừ:

use shop_api::state::AppState;
use sqlx::PgPool;

fn snapshot(state: &AppState) {
    let pool: &PgPool = &state.db;

    let size = pool.size();              // tổng connection trong pool (idle + active)
    let num_idle = pool.num_idle();      // connection idle chờ acquire (usize)
    let num_active = size - num_idle as u32;  // connection đang query
}

Field meaning chi tiết:

• size: số connection vật lý hiện có trong pool (lazy grow theo demand, không tới max_connections ngay từ đầu).
• num_idle: connection sẵn sàng acquire, đang chờ trong queue.
• num_active = size - num_idle: connection đang được handler giữ để chạy query.

Theo dõi 3 con số theo thời gian phát hiện bottleneck:

• num_active luôn xấp xỉ max_connections → pool exhausted, cần tăng pool hoặc tối ưu query.
• num_idle luôn cao (vd 18/20 idle suốt) → over-provisioned, có thể giảm pool tiết kiệm RAM.
• size nhỏ hơn max nhiều → traffic chưa đủ để lazy grow, pool đang ở giai đoạn warm.

Pattern lock Shop API cho bài này:

• Expose 3 con số qua endpoint /metrics theo Prometheus format.
• Endpoint /health/ready trả 503 khi num_active / max_connections > 0.95 để load balancer take pod out of rotation.
• Alert Prometheus PoolHighUtilization khi num_active / max > 0.8 kéo dài 2 phút (capacity warning).

Refactor file crates/shop-api/src/routes/health.rs đã có ở B5 (placeholder chỉ trả OK text) thành 2 handler đầy đủ:

// File: crates/shop-api/src/routes/health.rs
use axum::{extract::State, http::StatusCode, response::Json};
use serde_json::{json, Value};
use crate::state::AppState;

pub async fn liveness() -> StatusCode {
    StatusCode::OK
}

pub async fn readiness(State(state): State<AppState>) -> (StatusCode, Json<Value>) {
    // Ping DB nhanh
    let db_ok = sqlx::query("SELECT 1")
        .execute(&state.db)
        .await
        .is_ok();

    // Pool stats
    let pool_size = state.db.size();
    let pool_idle = state.db.num_idle() as u32;
    let pool_active = pool_size - pool_idle;
    let pool_max = state.config.pool_max_connections;
    let utilization = pool_active as f64 / pool_max as f64;

    let status = if db_ok && utilization < 0.95 {
        StatusCode::OK
    } else {
        // !db_ok hoặc pool gần exhausted >= 95%
        StatusCode::SERVICE_UNAVAILABLE
    };

    let body = json!({
        "status": if status.is_success() { "ok" } else { "degraded" },
        "checks": {
            "database": {
                "ok": db_ok,
                "pool_size": pool_size,
                "pool_idle": pool_idle,
                "pool_active": pool_active,
                "pool_max": pool_max,
                "utilization": format!("{:.2}", utilization),
            }
        }
    });

    (status, Json(body))
}

pub fn routes() -> axum::Router<AppState> {
    use axum::routing::get;
    axum::Router::new()
        .route("/health/live", get(liveness))
        .route("/health/ready", get(readiness))
}

Liveness vs Readiness theo Kubernetes pattern:

• /health/live: process còn alive (chỉ trả 200). K8s liveness probe fail → restart pod. Endpoint này không chạm DB, không touch pool — chỉ trả OK nếu tokio runtime còn chạy được async function.
• /health/ready: ready accept traffic (DB OK + pool còn slot). K8s readiness probe fail → take pod out of rotation (load balancer ngừng route traffic về pod), pod vẫn chạy không bị restart. Đây là cách elegant xử lý overload tạm thời.

Acquire timeout dành cho health: phải nhỏ — pool config Shop API set 5s default (B56 lock), nhưng probe K8s default check 10s timeout 1s, nếu DB chết hoặc pool exhausted phải fail nhanh để LB đưa pod ra khỏi rotation trong cùng probe interval.

Test thủ công:

curl http://localhost:3000/health/live
# 200 OK (body rỗng)

curl http://localhost:3000/health/ready | jq
# {
#   "status": "ok",
#   "checks": {
#     "database": {
#       "ok": true,
#       "pool_size": 5,
#       "pool_idle": 4,
#       "pool_active": 1,
#       "pool_max": 20,
#       "utilization": "0.05"
#     }
#   }
# }

Khi DB dừng (vd docker stop shop-postgres), readiness trả ngay 503 + "status":"degraded" + "database.ok":false — LB rút pod khỏi rotation, mọi instance còn DB tiếp tục phục vụ traffic.

Prometheus exposition format (RFC text format 0.0.4) là text/plain UTF-8 với mỗi metric gồm 3 dòng:

# HELP shop_db_pool_size Total connections in pool
# TYPE shop_db_pool_size gauge
shop_db_pool_size 5

# HELP shop_db_pool_idle Idle connections in pool
# TYPE shop_db_pool_idle gauge
shop_db_pool_idle 4

# HELP shop_db_pool_active Active connections in pool
# TYPE shop_db_pool_active gauge
shop_db_pool_active 1

# HELP shop_db_pool_max Maximum connections allowed
# TYPE shop_db_pool_max gauge
shop_db_pool_max 20

Tạo handler mới crates/shop-api/src/routes/metrics.rs:

// File: crates/shop-api/src/routes/metrics.rs
use axum::{extract::State, http::header, response::Response};
use crate::state::AppState;

pub async fn metrics(State(state): State<AppState>) -> Response {
    let size = state.db.size();
    let idle = state.db.num_idle();
    let active = size - idle as u32;
    let max = state.config.pool_max_connections;
    let utilization = active as f64 / max as f64;

    let body = format!(
        "# HELP shop_db_pool_size Total connections in pool\n\
         # TYPE shop_db_pool_size gauge\n\
         shop_db_pool_size {size}\n\
         # HELP shop_db_pool_idle Idle connections in pool\n\
         # TYPE shop_db_pool_idle gauge\n\
         shop_db_pool_idle {idle}\n\
         # HELP shop_db_pool_active Active connections in pool\n\
         # TYPE shop_db_pool_active gauge\n\
         shop_db_pool_active {active}\n\
         # HELP shop_db_pool_max Maximum connections allowed\n\
         # TYPE shop_db_pool_max gauge\n\
         shop_db_pool_max {max}\n\
         # HELP shop_db_pool_utilization Pool utilization ratio (0..1)\n\
         # TYPE shop_db_pool_utilization gauge\n\
         shop_db_pool_utilization {utilization:.4}\n"
    );

    Response::builder()
        .header(header::CONTENT_TYPE, "text/plain; version=0.0.4; charset=utf-8")
        .body(body.into())
        .unwrap()
}

Wire route trong crates/shop-api/src/router.rs:

// File: crates/shop-api/src/router.rs (đoạn build_router)
use axum::{routing::get, Router};
use crate::{routes, state::AppState};

pub fn build_router(state: AppState) -> Router {
    let api_v1 = Router::new()
        .merge(routes::products::routes());

    Router::new()
        .route("/", get(routes::root))
        .merge(routes::health::routes())                  // /health/live + /health/ready
        .route("/metrics", get(routes::metrics::metrics)) // /metrics
        .nest("/api/v1", api_v1)
        // ... compression + decompression + request_id middleware giữ nguyên B50
        .with_state(state)
}

Verify:

curl http://localhost:3000/metrics

Response sample:

# HELP shop_db_pool_size Total connections in pool
# TYPE shop_db_pool_size gauge
shop_db_pool_size 5
# HELP shop_db_pool_idle Idle connections in pool
# TYPE shop_db_pool_idle gauge
shop_db_pool_idle 4
# HELP shop_db_pool_active Active connections in pool
# TYPE shop_db_pool_active gauge
shop_db_pool_active 1
# HELP shop_db_pool_max Maximum connections allowed
# TYPE shop_db_pool_max gauge
shop_db_pool_max 20
# HELP shop_db_pool_utilization Pool utilization ratio (0..1)
# TYPE shop_db_pool_utilization gauge
shop_db_pool_utilization 0.0500

Pattern lock Shop API: /metrics endpoint manual format text — KHÔNG cần metrics crate cho basic case. Group 15 (B141-B150) sẽ refactor sang metrics crate + prometheus-client khi cần histogram/counter chi tiết (request latency, status code distribution, query duration). Hiện tại 5 gauge đủ giám sát pool capacity.

Scenario kiểm chứng pool exhaustion + alert behavior — chạy server với pool nhỏ rồi stress:

# Terminal 1 — set pool nhỏ để dễ saturate
POOL_MAX_CONN=5 cargo run -p shop-api

# Terminal 2 — stress test concurrent > pool size
oha -z 30s -c 50 'http://localhost:3000/api/v1/products'

# Terminal 3 — theo dõi metrics song song
watch -n 1 'curl -s http://localhost:3000/metrics | grep pool'

Terminal 3 thấy giá trị thay đổi real-time:

shop_db_pool_size 5
shop_db_pool_idle 0
shop_db_pool_active 5
shop_db_pool_max 5
shop_db_pool_utilization 1.0000

Khi pool saturate, request mới chờ tới acquire_timeout 5s (B56 lock) rồi nhận 503 ServiceUnavailable qua mapping PoolTimedOut → AppError::ServiceUnavailable (B55 lock). Output oha thể hiện rate 503 tăng song song.

Alert rule Prometheus 2 mức cho production (deep dive Group 15):

groups:
  - name: shop_api_pool
    rules:
      - alert: PoolHighUtilization
        expr: shop_db_pool_utilization > 0.8
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "Shop API pool > 80% utilization"
          description: "Pool đang dùng {{ $value | humanizePercentage }}, cần scale lên."

      - alert: PoolExhausted
        expr: shop_db_pool_utilization >= 1.0
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "Shop API pool exhausted — scale up needed"
          description: "Pool đầy, request mới sẽ 503 sau acquire_timeout."

3 action khi alert fire theo runbook đề xuất:

1. Check Postgres max_connections còn slot không: psql -c 'SHOW max_connections; SELECT count(*) FROM pg_stat_activity;'.
2. Tăng POOL_MAX_CONN qua env hoặc add replica (rolling update K8s), không restart full fleet.
3. Audit query slow gây giữ connection lâu — pg_stat_statements + EXPLAIN ANALYZE; B59 sẽ deep dive dynamic query timeout per endpoint.

Pattern phổ biến: alert PoolHighUtilization 80% chạy 2 phút để loại bỏ false positive spike ngắn, alert PoolExhausted 100% chạy 30 giây vì critical phải react nhanh.

Cập nhật crates/shop-api/src/routes/mod.rs để khai báo 2 module mới:

// File: crates/shop-api/src/routes/mod.rs
pub mod demo_async;
pub mod demo_error;
pub mod health;       // refactor B57 — 2 handler liveness + readiness
pub mod metrics;      // NEW B57 — Prometheus exposition format
pub mod products;
pub mod version;

Router cuối cùng đảm bảo 3 endpoint mới có trong stack:

// File: crates/shop-api/src/router.rs
use axum::{routing::get, Router};
use crate::{routes, state::AppState};

pub fn build_router(state: AppState) -> Router {
    let api_v1 = Router::new()
        .merge(routes::products::routes());

    Router::new()
        .route("/", get(routes::root::root))
        .route("/version", get(routes::version::version))
        .merge(routes::health::routes())
        .route("/metrics", get(routes::metrics::metrics))
        .nest("/api/v1", api_v1)
        // middleware stack giữ nguyên B50
        // (compression > decompression > request_id > enrich_error)
        .with_state(state)
}

Full benchmark workflow đề xuất ghi vào scripts/bench.sh để tái lập sau:

#!/usr/bin/env bash
# scripts/bench.sh
set -euo pipefail

# Start server (background)
cargo run -p shop-api &
SERVER_PID=$!
trap "kill $SERVER_PID" EXIT

# Wait warm-up
sleep 3

# Baseline JSON output
oha -z 30s -c 50 -j 'http://localhost:3000/api/v1/products' > baseline.json

# Parse output
jq '.summary.requestsPerSec' baseline.json
jq '.latencyPercentiles."99"' baseline.json

# Snapshot metrics
curl -s http://localhost:3000/metrics | grep pool > pool-snapshot.txt

Suggested commit: B57: /health/live + /health/ready + /metrics Prometheus + oha benchmark.

• oha Rust-native load test tool — TUI real-time + HTTP/2 + JSON output cho CI parse.
• Pool metrics API: pool.size(), pool.num_idle(), pool.num_active() = size - num_idle.
• Liveness vs Readiness pattern Kubernetes:
  • /health/live process alive (200 luôn) — restart pod khi fail.
  • /health/ready accept traffic (DB OK + pool < 95% utilization) — take pod out of rotation khi fail.
• /metrics Prometheus format manual text — đủ cho basic, scale lên metrics crate ở G15.
• 5 metrics Shop API: shop_db_pool_size, shop_db_pool_idle, shop_db_pool_active, shop_db_pool_max, shop_db_pool_utilization.
• Alert threshold: 80% warning (for 2m), 100% critical (for 30s).
• 3 action khi alert fire: check Postgres max_connections, tăng POOL_MAX_CONN hoặc add replica, audit slow query.
• Stress test workflow: oha + watch metrics + parse JSON output.
• Pool tune sweet spot: 20 default OK cho dev/staging; tune theo CPU cores DB server (rule of thumb cores × 2 + spindles).
• Diminishing return pool size > sweet spot: thêm overhead Postgres process per connection + context switch, RPS không tăng còn p99 tệ hơn.
• File path lock: crates/shop-api/src/routes/{health.rs, metrics.rs} (mới + refactor B5).
• Foundation cho B58 (sqlx offline cache + CI), Group 15 OpenTelemetry tracing deep.

Tự trả lời, đáp án ở cuối:

1. oha so với wrk/hey khác gì? Tại sao chọn oha cho Rust project?
2. Liveness vs Readiness probe — Kubernetes xử lý khác nhau ra sao? Use case mỗi loại.
3. Pool metrics 3 con số chính (size/idle/active) — quan hệ toán học ra sao?
4. Alert threshold 80% warning vs 100% critical — tại sao 2 mức? Action mỗi mức là gì?
5. Pool size sweet spot tune theo gì? Tại sao tăng quá cao lại làm RPS giảm?

Bài 58: sqlx Offline Cache + CI Workflow — deep .sqlx/ offline cache + cargo sqlx prepare command + GitHub Actions workflow với SQLX_OFFLINE=true + Docker compose Postgres testcontainer integration test, áp Shop API CI/CD pipeline.