Database Connection Pooling Explained

The Problem

Opening a database connection for every request is expensive — it involves TCP handshake, authentication, and memory allocation. Connection pooling maintains a set of reusable connections, dramatically improving performance.

How Pooling Works

1. Application requests a connection from the pool
2. Pool provides an existing idle connection (or creates a new one)
3. Application uses the connection
4. Connection is returned to the pool (not closed)

PHP with PDO

// PDO with persistent connections (basic pooling)
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true
]);

External Pool with ProxySQL

sudo apt install -y proxysql

# ProxySQL sits between app and MySQL
# App connects to ProxySQL (port 6033)
# ProxySQL manages a pool of real MySQL connections

Node.js with mysql2

const mysql = require('mysql2');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'myapp',
    connectionLimit: 20,     // Max connections in pool
    waitForConnections: true,
    queueLimit: 0
});

Sizing Your Pool

Rule of thumb for web applications:

pool_size = (CPU cores * 2) + number_of_disks

For a 4-core VPS with SSD: (4 * 2) + 1 = 9 connections is a good starting point.

Monitoring

# Check MySQL connection usage
SHOW STATUS LIKE 'Threads_%';
SHOW PROCESSLIST;