MySQL Connection Pooling

Also Known As

TL;DR

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

Why It Matters

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

// 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();
}

// 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

Tags

Related Terms