← Back to glossary

Database Connection Pool Sizing

performance PHP 5.0+ Advanced

debt(d5/e5/b7/t7)

d5 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'specialist tool catches' (d5). The term's detection_hints.tools list mysql-processlist, datadog, and pgbouncer-stats — all specialist monitoring tools. Pool exhaustion shows as 'Too many connections' errors or high connection wait times, but only visible through database metrics or APM tools, not caught by linters or static analysis.

e5 Effort Remediation debt — work required to fix once spotted

Closest to 'touches multiple files / significant refactor' (e5). The quick_fix involves calculating optimal pool size and potentially introducing a connection pooler (PgBouncer/ProxySQL). This requires infrastructure changes, configuration across PHP-FPM settings, database server config, and possibly adding a new proxy layer — more than a one-line fix but not a full architectural rewrite.

b7 Burden Structural debt — long-term weight of choosing wrong

Closest to 'strong gravitational pull' (b7). Applies to both web and queue-worker contexts per applies_to. Connection pool sizing is a load-bearing infrastructure decision that affects every database query in the system. The common_mistakes show this touches PHP-FPM worker counts, database max_connections, and monitoring infrastructure. Every scaling decision and capacity planning exercise must account for this choice.

t7 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'serious trap' (t7). The misconception explicitly states developers believe 'larger pool always improves performance' when the opposite is true beyond a threshold. This contradicts intuition from other resource scaling (more memory = better, more CPU = better). The HikariCP formula (cores × 2 + spindles) producing better results than hundreds of connections is counterintuitive. Developers from other ecosystems expect pooling to behave differently than PHP's per-process model.

About DEBT scoring → scored by claude-opus-4-5-20251101 · 2026-05-06 · reviewed by human

Also Known As

pool size tuning max connections connection pool config

TL;DR

The optimal pool size is smaller than you think — follow the formula (cores × 2) + effective_spindle_count rather than matching thread count.

Explanation

The HikariCP team (Java) popularised the formula: pool_size = (core_count × 2) + effective_disk_spindle_count. For a 4-core SSD server that's roughly 9 connections — far fewer than the hundreds developers instinctively configure. Why: databases are CPU and I/O bound; adding more connections beyond saturation causes context-switching overhead and lock contention that slows everything down. For PHP-FPM: total connections across all workers should not exceed the database's connection limit (max_connections in PostgreSQL, max_connections in MySQL) minus overhead. Use a connection pooler (pgBouncer, ProxySQL) to mediate between many FPM workers and a small DB connection pool. Monitor active vs idle connections and pool wait time to tune.

Diagram

flowchart TD
    FORMULA[Optimal pool size formula]
    FORMULA --> THREADS[CPU cores x 2 + disk spindles<br/>baseline starting point]
    THREADS --> MEASURE[Measure under real load<br/>watch pool wait time]
    subgraph Too_Small
        SMALL[Workers queue waiting<br/>for connections]
        SMALL --> LATENCY[High latency<br/>requests pile up]
    end
    subgraph Too_Large
        LARGE[DB overwhelmed<br/>too many concurrent queries]
        LARGE --> CONTENTION[Lock contention<br/>memory pressure on DB]
    end
    subgraph Sweet_Spot
        RIGHT[Pool wait time near zero<br/>DB CPU under 80pct]
    end
style SMALL fill:#f85149,color:#fff
style LARGE fill:#f85149,color:#fff
style RIGHT fill:#238636,color:#fff

Common Misconception

✗ A larger connection pool always improves database performance. Beyond the database's thread concurrency limit, additional connections queue and compete — "pool = cores * 2 + disk spindles" (HikariCP formula) often outperforms pools of hundreds because it reduces context switching.

Why It Matters

Pool size too small means requests queue waiting for connections; too large means the database server is overwhelmed. The optimal size is determined by the database's max connections and the number of PHP workers.

Common Mistakes

Setting pool size equal to the maximum database connections — leaves no headroom for admin connections or other services.
Not accounting for multiple PHP-FPM workers each holding their own connections.
Not monitoring connection wait time — pool exhaustion is invisible without queue depth metrics.
Using persistent connections (pconnect) without understanding they are per-process, not pooled.

Code Examples

✗ Vulnerable

# php-fpm with 50 workers, each opening 5 DB connections = 250 connections:
pm.max_children = 50
# database.php:
'pool_size' => 5  # 50 × 5 = 250 — likely exceeds DB max_connections
# Formula: pool_size = (DB max_connections - admin_reserve) / php_workers

✓ Fixed

// Rule of thumb: pool_size = (core_count * 2) + effective_spindle_count
// For SSD-backed DB: pool ≈ core_count * 2
// For network I/O dominant workloads: slightly larger

// PHP-FPM: max_children × 1 connection per worker = max open connections
// pm.max_children = 50 → MySQL needs max_connections > 50

// MySQL max_connections check:
// SHOW VARIABLES LIKE 'max_connections';
// SHOW STATUS LIKE 'Threads_connected';

// ProxySQL connection pool sizing:
// mysql_hostgroups table → max_connections per backend
// Tune: monitor wait_timeout, Threads_running, connection refusals

// AWS RDS: max_connections ≈ (DBInstanceClassMemory / 12582880)
// db.t3.medium (2GB): ~160 connections — use RDS Proxy for serverless

References

↗ https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing