Why Your Database-Driven Application Is Slow
Your web application handles 500 requests per second. Each request queries the database, sometimes multiple times. At the most basic level, every database query follows this sequence: open a TCP connection, perform the TLS handshake, authenticate the user, execute the query, receive the result, and close the connection. For a simple SELECT query that takes 2 milliseconds, the connection overhead can add another 5-15 milliseconds. Your 2ms query suddenly takes 17ms, and your application spends 80% of its time just establishing and tearing down connections.
This is where connection pooling transforms your application performance. Instead of creating a new connection for every query, a pool maintains a set of pre-established connections that are shared across requests. Your application borrows a connection from the pool, runs its query, and returns the connection for the next request. The overhead drops from 15ms per query to nearly zero.
This guide explains connection pooling from the ground up: how it works, how to size your pool correctly, how to configure it in PHP, Node.js, and Go, and how to diagnose and fix pool-related performance problems. Whether you are using MySQL, PostgreSQL, or any other relational database, the principles are the same.
How Connection Pooling Works
Without Pooling: Connection Per Request
In the simplest model, every database operation creates a new connection and destroys it when done:
Each step has latency. On a local socket connection, the overhead might be 1-3ms total. Over a network with TLS, it can be 10-30ms. Multiply that by hundreds of queries per second, and you are wasting enormous amounts of CPU time and network bandwidth on connection management.
With Pooling: Reusable Connections
With pooling, connections are created once and reused thousands of times. The pool manages the lifecycle: opening connections when needed, keeping them alive during idle periods, closing stale connections, and limiting the maximum number of simultaneous connections.
Connection Lifecycle in a Pool
Understanding the lifecycle of a pooled connection helps you configure pools correctly and diagnose problems:
Pool Size Calculation
The most common mistake in connection pooling is setting the pool too large. More connections does not mean more performance. In fact, setting your pool too large can reduce throughput dramatically because of contention on shared resources (CPU caches, disk I/O, memory bandwidth).
pool_size = (core_count * 2) + effective_spindle_countFor a server with 4 CPU cores and 1 SSD (spindle count = 1):
pool_size = (4 * 2) + 1 = 9This formula comes from PostgreSQL documentation and applies equally to MySQL. It accounts for the fact that each core can handle one active query plus one waiting on I/O, with an extra connection per disk for I/O parallelism.
With modern SSDs that handle massive parallel I/O, the spindle count is somewhat arbitrary. A practical starting point for most applications:
| Server Cores | Recommended Pool Size | Maximum Pool Size |
|---|---|---|
| 2 cores | 5 | 10 |
| 4 cores | 10 | 20 |
| 8 cores | 17 | 30 |
| 16 cores | 33 | 50 |
| 32 cores | 65 | 100 |
MySQL Server Settings for Connection Pooling
The database server needs to accommodate your connection pools. These MySQL settings control how connections are handled:
[mysqld]
# Maximum total connections (all pools combined)
max_connections = 200
# Cache idle threads for reuse
thread_cache_size = 16
# Close idle connections after 8 hours
wait_timeout = 28800
interactive_timeout = 28800
# Thread handling (for high connection counts)
thread_handling = pool-of-threads # MariaDB / Percona only
max_connections Planning
Your max_connections must accommodate all connection pools from all application instances, plus monitoring tools, admin connections, and headroom:
App server 1 pool: 20 connections
App server 2 pool: 20 connections
Worker pool: 10 connections
Monitoring: 5 connections
Admin reserve: 5 connections
─────────────────────────────────
Total: 60 connections
With 2x headroom: 120 connections
SET GLOBAL max_connections = 150;
Connection Pooling in PHP
PHP has a unique relationship with connection pooling because of its process-per-request model. Each PHP-FPM worker process is independent, so "connection pooling" in PHP means persistent connections within each worker, not a shared pool across workers.
PDO Persistent Connections
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');
// With persistent connections (reuses across requests in same worker)
$pdo = new PDO(
'mysql:host=localhost;dbname=mydb',
'user',
'pass',
[PDO::ATTR_PERSISTENT => true]
);
- Each PHP-FPM worker maintains its own persistent connection. With 50 workers, that is 50 connections, not 1 shared pool.
- Transaction state carries over: if a request crashes mid-transaction, the next request using that connection inherits uncommitted changes.
- Temporary tables, session variables, and lock states persist across requests.
- Always explicitly commit/rollback transactions and reset session state.
ProxySQL as an External Pooler for PHP
For true connection pooling in PHP environments, use ProxySQL. It sits between PHP and MySQL, maintaining a real pool of connections:
50 workers
Connection multiplexing
20 connections
apt install proxysql
# Configure via admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032
# Add MySQL backend
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (1, '127.0.0.1', 3306);
# Set connection pool size
UPDATE mysql_servers SET max_connections = 50
WHERE hostname = '127.0.0.1';
# Add application user
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'password', 1);
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
Connection Pooling in Node.js
Node.js is single-threaded and event-driven, which makes it naturally suited to connection pooling. The mysql2 library provides built-in pool support:
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'appuser',
password: 'secure_password',
database: 'myapp',
// Pool configuration
connectionLimit: 10, // Max connections in pool
queueLimit: 0, // Unlimited queue (0 = no limit)
waitForConnections: true, // Queue when pool full
idleTimeout: 60000, // Close idle connections after 60s
maxIdle: 5, // Keep max 5 idle connections
enableKeepAlive: true, // TCP keepalive
keepAliveInitialDelay: 30000
});
// Usage - pool automatically manages connections
const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [userId]);
// For transactions, get explicit connection
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, fromId]);
await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, toId]);
await conn.commit();
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release(); // ALWAYS release back to pool
}
conn.release() in a finally block when using getConnection(). Forgetting to release a connection is the number one cause of connection pool exhaustion. The pool runs out of connections and your application hangs, waiting for a connection that will never be returned.
Connection Pooling in Go
Go's database/sql package has connection pooling built into the standard library. Every sql.DB object is a pool, not a single connection:
import (
"database/sql"
"time"
_ "github.com/go-sql-driver/mysql"
)
db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/mydb")
if err != nil {
log.Fatal(err)
}
// Pool settings
db.SetMaxOpenConns(25) // Max open connections
db.SetMaxIdleConns(10) // Max idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Max connection age
db.SetConnMaxIdleTime(3 * time.Minute) // Max idle time
// Verify pool is working
if err := db.Ping(); err != nil {
log.Fatal("Database unreachable:", err)
}
| Setting | What It Controls | Default | Recommended |
|---|---|---|---|
MaxOpenConns | Maximum total connections (active + idle) | Unlimited | 25 |
MaxIdleConns | Maximum idle connections kept alive | 2 | 10 |
ConnMaxLifetime | Maximum age before connection is closed | Unlimited | 5 min |
ConnMaxIdleTime | Maximum idle time before closing | Unlimited | 3 min |
Monitoring Go Pool Statistics
stats := db.Stats()
fmt.Printf("Open: %d InUse: %d Idle: %d\n",
stats.OpenConnections, stats.InUse, stats.Idle)
fmt.Printf("WaitCount: %d WaitDuration: %v\n",
stats.WaitCount, stats.WaitDuration)
fmt.Printf("MaxIdleClosed: %d MaxLifetimeClosed: %d\n",
stats.MaxIdleClosed, stats.MaxLifetimeClosed)
PgBouncer for PostgreSQL
If you are using PostgreSQL, PgBouncer is the industry-standard connection pooler. It operates as a lightweight proxy between your application and PostgreSQL:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
pool_mode = transaction # Most common mode
default_pool_size = 20
max_client_conn = 1000
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
| Pool Mode | Connection Sharing | Use Case |
|---|---|---|
| session | Connection assigned for entire client session | Legacy apps using session features |
| transaction | Connection assigned per transaction | Most web applications (recommended) |
| statement | Connection assigned per statement | Simple stateless queries only |
Diagnosing Connection Pool Problems
Symptom: Application Hangs Under Load
This is almost always pool exhaustion. All connections are in use, and new requests are waiting indefinitely for a connection to become available.
Causes
- Connection leaks (not releasing connections)
- Long-running queries holding connections
- Pool too small for traffic volume
- Deadlocked transactions
Fixes
- Always release in
finallyblocks - Set query timeouts
- Increase pool size (with care)
- Add connection wait timeout
Symptom: "Too Many Connections" Error
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
-- See who is connected
SELECT user, host, db, command, time, state
FROM information_schema.PROCESSLIST
ORDER BY time DESC;
-- Count connections per user
SELECT user, COUNT(*) as conn_count
FROM information_schema.PROCESSLIST
GROUP BY user ORDER BY conn_count DESC;
Symptom: Intermittent "Connection Reset" Errors
This happens when the pool serves a connection that MySQL has already closed (typically due to wait_timeout expiration). The solution is to set your pool's connection maximum lifetime to be shorter than MySQL's wait_timeout:
ConnMaxLifetime (or equivalent) to 80% of MySQL's wait_timeout. If wait_timeout = 28800 (8 hours), set your pool lifetime to 23040 seconds (6.4 hours). This ensures your pool retires connections before MySQL kills them.
Common Connection Pooling Mistakes
| Mistake | Impact | Fix |
|---|---|---|
| Pool size = max_connections | No headroom for admin/monitoring | Pool should be 50-70% of max_connections |
| No connection timeout | App hangs forever waiting for connection | Set wait timeout of 5-30 seconds |
| No max lifetime | Stale connections after server restart | Set 5-10 minute max lifetime |
| MaxIdle = MaxOpen | Too many idle connections consume memory | MaxIdle should be 30-50% of MaxOpen |
| Not validating on borrow | Dead connections cause query errors | Enable connection validation/ping |
| Connection leaks | Pool exhaustion under load | Always release in finally/defer blocks |
Monitoring Pool Health
Track these metrics to ensure your connection pool is healthy:
- Active connections — Should not consistently hit the maximum. If it does, increase pool size or optimize queries.
- Wait count/time — Number of requests that had to wait for a connection. High wait counts indicate pool is too small.
- Idle connections — Some idle connections are good (ready for instant use). Too many waste memory.
- Connection creation rate — Should be low and steady. Spikes indicate pool thrashing or eviction issues.
- Error rate — Connection failures, timeouts, and dead connection errors. Should be near zero.
- Query latency — Average query time should not increase as load increases (if pool is right-sized).
Summary
Connection pooling is one of the highest-impact optimizations you can make for any database-driven application. The principles are simple: reuse connections instead of creating new ones, size your pool based on CPU cores rather than user count, always release connections in finally/defer blocks, and set maximum lifetimes to prevent stale connections.
For PHP applications, consider ProxySQL for true connection multiplexing beyond what persistent connections offer. For Node.js, the mysql2 pool is excellent out of the box. For Go, the standard library database/sql pool is production-ready with just four configuration calls. And for PostgreSQL, PgBouncer in transaction mode gives you the best connection multiplexing available.
Start with a small pool, monitor the metrics, and increase only when you see wait times climbing. A pool of 10 well-managed connections almost always outperforms a pool of 1,000 poorly-managed ones.