MySQL Connection Pool: A Connection Management Guide

On a busy game server or a high-traffic web application, a MySQL connection pool is one of the cheapest ways to boost performance. The reason is that opening and closing a fresh connection for every database operation is far more expensive than it looks: a TCP handshake, authentication, character-set negotiation and a server-side thread allocation. If all of that is repeated on every request, you end up spending more time and CPU on setup than on the actual queries. This article explains what a connection pool is, why and how it reduces server load, and how to set one up correctly across different languages.

Why is opening a connection expensive?

When you call mysql_real_connect or create a new PDO object, the following happens behind the scenes:

• A TCP connection is established between client and server (three-way handshake; extra round-trip latency on a remote host).
• MySQL sends a handshake packet, the client replies with a username/password, and the server authenticates it.
• If SSL/TLS is enabled, an additional encryption handshake takes place.
• The server allocates a thread to serve the connection and initialises session variables.

For a single connection this may take a few milliseconds, but on a server handling hundreds of requests per second, paying this cost on every request both inflates latency and quickly pushes you against MySQL's max_connections limit. This is exactly where a connection pool comes in: it opens connections once, and instead of closing them after use it returns them to the pool, so the next request reuses a ready connection.

What exactly does a connection pool do?

A connection pool is a component that keeps a set of pre-opened, ready-to-use database connections in memory. When the application asks for a connection, the pool lends out an idle one; when the work is done, the connection is not closed but returned to the pool. Typical settings are:

• Minimum pool size: the least number of connections to keep permanently open (avoids cold-start latency).
• Maximum pool size: the most connections allowed at once; it must stay below MySQL's max_connections.
• Idle timeout: how long an unused connection is kept before it is closed.
• Connection validation: checking that a connection is still alive before handing it out (usually a simple ping or SELECT 1).

The result: the handshake cost is paid only as many times as the pool size over the application's lifetime, not for thousands of requests. This lowers per-request latency and nearly eliminates the thread create/destroy load on the MySQL server.

PHP / Laravel: persistent connections

PHP's classic "each request starts from scratch" model makes a traditional pool harder, because the process dies when the request ends. The solution is persistent connections, which keep the connection alive for as long as the PHP-FPM processes live. With PDO:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
]);

In Laravel you do the same in config/database.php:

'mysql' => [
    'driver'  => 'mysql',
    // ...
    'options' => [
        PDO::ATTR_PERSISTENT => true,
    ],
],

One caveat: persistent connections keep a separate connection for each PHP-FPM worker. If your worker count is high, the total number of connections can exceed MySQL's limit. Plan your worker count and max_connections together. If you want a real pool (size control, health checks), placing ProxySQL in front of your PHP processes is a stronger solution.

Node.js: a real pool example

Because Node is single-process and long-lived, a connection pool fits naturally here. With the mysql2 library:

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'game',
  password: process.env.DB_PASS,
  database: 'server',
  connectionLimit: 20,
  waitForConnections: true,
  queueLimit: 0,
});

const [rows] = await pool.query(
  'SELECT level FROM players WHERE id = ?', [playerId]
);

pool.query() takes a connection from the pool on each call, runs the query, and returns the connection automatically. For operations that need the same connection, such as transactions, you grab one manually with pool.getConnection() and return it with connection.release() when done — forgetting to release is the most common mistake that exhausts a pool.

The game server (C++) side

On a Metin2-based or a custom C++ game server there is usually no ready-made ORM; you manage the pool yourself. The logic is the same: at startup you open N connections and place them in a thread-safe queue; when a thread needs to run a query it pulls a connection from the queue and puts it back when finished. Key points:

• Thread safety: guard access to the pool with a mutex or a lock-free queue. Two threads cannot use the same MySQL connection at the same time.
• Reconnection: when wait_timeout elapses, MySQL closes the idle connection and you get a "MySQL server has gone away" error. Check with mysql_ping() before handing it out, and reconnect if it is dead.
• Sizing: tune the pool to the number of DB threads in the game core; more connections than needed just waste MySQL memory.

Sizing the pool correctly

The most common mistake is to assume "more connections is always better". The opposite is true: too many concurrent connections slow things down by increasing context switching and lock contention in MySQL. A practical starting point is a relatively small pool based on the CPU core count and disk concurrency (for most applications a few connections per core is enough). Verify by measuring:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

If Threads_running is consistently high, the bottleneck is not the pool but your queries; fix slow queries and missing indexes first. If Max_used_connections presses against max_connections, either lower your pool limits or you genuinely need more capacity.

Frequently Asked Questions

Does MySQL have a built-in connection pool?

No, the classic connection pool is a client-side concept. On the server side, MySQL Enterprise and MariaDB offer a "thread pool" plugin; it serves connections with a small number of threads but does not remove the cost of establishing a connection. The two solve different problems: the thread pool tackles thread explosion on the server, the connection pool tackles connection-setup cost on the client.

Is a persistent connection the same as a connection pool?

Not exactly. A persistent connection avoids closing a connection after a request and reuses it, but size and health management are limited. A real pool offers policies such as minimum/maximum size, idle timeout, health checks and queuing. In PHP, a middleware like ProxySQL is used for a stronger pool.

When do I need ProxySQL?

ProxySQL makes sense when you have multiple application servers and want to centrally cap the total number of connections, route queries, or split reads and writes. For small single-server projects, an in-application pool is usually enough.

Are your database connections choking your server? If you need help with connection pool setup, sizing or MySQL-side tuning for your game server or web project, get in touch with me; I'll review your current setup and lay out a concrete improvement plan.