When should you NOT use MySQL Connection Pooling?

Avoid PDO::ATTR_PERSISTENT when using transactions — a crashed request can leave an open transaction on a reused connection. Do not use persistent connections with different database users in the same pool — connections are reused regardless of credentials.

When is MySQL Connection Pooling the right choice?

Use PDO::ATTR_PERSISTENT for simple single-server apps where max_connections is a concern. Use ProxySQL or PgBouncer for multi-server production environments needing health checks and load balancing.

← Back to glossary

MySQL Connection Pooling

PHP PHP 5.1+ Intermediate

debt(d7/e5/b5/t7)

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

Closest to 'only careful code review or runtime testing' (d7). The detection_hints state 'automated: no' and identify the code pattern as 'new PDO() inside a function or loop.' No automated tool is listed that catches this; it requires manual code review to spot connection misuse or transaction leaks, or runtime monitoring when 'Too many connections' errors appear under load.

e5 Effort Remediation debt — work required to fix once spotted

Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix mentions using PDO::ATTR_PERSISTENT or deploying ProxySQL. For simple cases it may be a one-liner, but the common_mistakes highlight needing a singleton/DI container pattern to share one connection, ensuring no open transactions on reused connections, and potentially deploying external infrastructure (ProxySQL). This spans multiple files and architectural decisions rather than a single-line patch.

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

Closest to 'persistent productivity tax' (b5). The choice applies to both web and CLI contexts and affects every database interaction in the codebase. Using a singleton DI container, managing transaction cleanup, and configuring pooling infrastructure impose an ongoing tax on all DB-touching code. However, it doesn't fully define system shape, so it stays at b5 rather than b7.

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

Closest to 'serious trap' (t7). The misconception field explicitly states 'PDO::ATTR_PERSISTENT is safe to use everywhere' — a competent developer enabling persistent connections would reasonably assume this is safe, but the real behavior is that crashed requests can leave open transactions that poison subsequent requests. This contradicts the intuition that persistent simply means 'reuse the connection,' and it's a well-documented but frequently missed gotcha that can cause subtle data corruption.

About DEBT scoring → scored by claude-sonnet-4-6 · 2026-05-10 · reviewed by human

Also Known As

PDO persistent connection PHP MySQL connection pool ProxySQL PHP

TL;DR

Reusing database connections across requests instead of opening and closing a new connection on every request.

Explanation

Opening a MySQL connection involves TCP handshake, authentication, and session setup — typically 5–20ms. In high-traffic PHP-FPM applications, creating a new connection per request wastes significant time. PDO does not have a built-in connection pool, but PDO::ATTR_PERSISTENT enables persistent connections reused across requests within the same PHP-FPM worker process. External poolers like ProxySQL or PgBouncer are preferred for production — they provide proper connection limits, health checks, and load balancing.

Common Misconception

✗ PDO::ATTR_PERSISTENT is safe to use everywhere. Persistent connections can leak transaction state between requests — always ensure no open transaction exists before returning a persistent connection to the pool.

Why It Matters

Under load, hundreds of simultaneous short-lived connections overwhelm MySQL's max_connections limit, causing 'Too many connections' errors. Pooling keeps the connection count stable regardless of request volume.

Common Mistakes

Using persistent connections with transactions — a crashed request leaves an open transaction on a reused connection.
Setting max_connections too low in MySQL without a connection pooler — causing connection queue timeouts.
Opening multiple PDO instances in the same script — use a singleton or DI container to share one connection.

Avoid When

Avoid PDO::ATTR_PERSISTENT when using transactions — a crashed request can leave an open transaction on a reused connection.
Do not use persistent connections with different database users in the same pool — connections are reused regardless of credentials.

When To Use

Use PDO::ATTR_PERSISTENT for simple single-server apps where max_connections is a concern.
Use ProxySQL or PgBouncer for multi-server production environments needing health checks and load balancing.

Code Examples

✗ Vulnerable

// New connection on every request — 10ms × 1000 req/s = 10s wasted per second
function getUser(int $id): array {
    $pdo = new PDO($dsn, $user, $pass); // new connection every call
    return $pdo->query("SELECT * FROM users WHERE id = $id")->fetch();
}

✓ Fixed

// ProxySQL handles pooling externally — PHP connects to ProxySQL port
$pdo = new PDO('mysql:host=127.0.0.1;port=6033;dbname=app', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// OR: PDO persistent for single-server low-traffic apps
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
]);

References

https://www.php.net/manual/en/pdo.connections.php