Database load analysis

Database Load Analysis

When to Use

Use when load tests reveal rising query latencies, connection pool timeouts, lock wait spikes, deadlocks, or CPU/I/O saturation in the database tier (PostgreSQL, MySQL/MariaDB, SQLite, Redis).

1. Connection Pool Sizing & Wait-Queue Analysis

A common failure mode under load is setting the application's DB pool too large or too small, or holding connections across slow external I/O:

• Pool Saturation Metrics: Track active connections, idle connections, waiting requests in the pool queue, and pool acquisition wait time (acquireTimeoutMillis).
• Connection Leak / Long Transaction Hazard:
  • Never hold an open DB transaction (BEGIN ... COMMIT) while awaiting an external HTTP call, LLM response, or file upload! Under load, a 2s external call inside a transaction exhausts a 20-connection pool at just $\lambda = 10\text{ RPS}$ ($L = 10 \times 2 = 20$).
• Optimal Pool Formula: Keep total active DB connections bounded near CPU core count + disk spindle/IOPS concurrency (or route through PgBouncer transaction pooling), rather than allowing hundreds of app connections to thrash PostgreSQL backend processes.

2. Lock Contention, Hot Rows & Deadlocks

When throughput flattens or collapses as concurrency rises (Gunther's $\alpha$ and $\beta$ coefficients), inspect lock contention:

• Hot-Row Write Contention: Updating the same counter row, tenant quota row, inventory record, or queue table row across concurrent requests serializes all transactions behind row-level locks (SELECT ... FOR UPDATE / UPDATE).
• Lock Ordering & Deadlocks: Concurrent transactions acquiring locks in inconsistent orders trigger deadlocks (40P01 in Postgres, 1213 in MySQL) or SQLITE_BUSY in SQLite.
• SQLite Concurrency Specifics:
  • Verify PRAGMA journal_mode = WAL;, PRAGMA synchronous = NORMAL;, and a reasonable PRAGMA busy_timeout = 5000;.
  • Remember that SQLite allows concurrent readers in WAL mode but only one writer at a time—keep write transactions sub-millisecond and batch bulk inserts inside a single short transaction.

3. Query Amplification ($N+1$) & Realistic Data Volume

A query that takes 0.5ms on a 10-row dev table can take 800ms via a sequential scan on a 2-million-row table under 50 concurrent users:

• Seed Realistic Cardinality: Test against representative row counts and realistic data skew (large tenants + small tenants).
• Count Queries per Request: Instrument query counters during load tests to detect $N+1$ query explosions, redundant auth/session lookups, or unindexed OFFSET pagination (LIMIT 50 OFFSET 100000 scanning 100k rows per request instead of keyset cursor pagination).
• Capture EXPLAIN (ANALYZE, BUFFERS): Inspect buffer hits vs. disk reads, rows removed by filter, sort/hash spills to disk (work_mem), and index scan efficiency on the slowest queries surfaced during load.