Skip to main content

SQL connection pools

Connection pools take up connections even if they're not being used

Your database server is going to have some maximum number of connections it can handle at one time (depending on the hardware configuration, and also the SQL engine version). If you allocate a large number of connections to a connection pool, you can cause your database to return errors due to having no available connections to handle new incoming requests.

For instance, here's an example of instantiating a connection pool for a server handling web requests:

const pool = mariadb.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    connectionLimit: 25,
});

No matter what your server is doing, whether it's handling loads of requests or no requests at all - those 25 connections are being taken up on your database server. They're not eating into CPU load - they're just sleeping, waiting to be used - but they are unavailable to be used by other applications or requests you make to your database.

Diagnosing this problem

You'll know if this problem is happening if you have "SQL error 1040 (Too many connections)" in your logs, or if for whatever reason your logs don't show it, you can check in the MariaDB/MySQL command line interface. The following commands give you information about the connection limits and usage:

-- Shows the maximum allowed number of active connections.
SELECT @@max_connections;

/* Shows the maximum number of active connections that have
 * existed during this server's uptime. */
SHOW STATUS WHERE `variable_name` = 'Max_used_connections';

/* These commands both perform the same function.
 * They show the number of current active connections. */
SELECT COUNT(host) FROM information_schema.processlist;
SHOW STATUS WHERE variable_name = 'threads_connected';

-- Shows a detailed list of all current active connections
SHOW FULL PROCESSLIST\G;

If you have a connection pool eating up connection resources, you'll see a lot of entries like this, where connections are sleeping and doing nothing, from the same user and host.

*************************** 6. row ***************************
      Id: 45
    User: <your connected DB user>
    Host: <host ip address>:<client port>
      db: <database user is connected to>
 Command: Sleep
    Time: 25
   State:
    Info: NULL
Progress: 0.000

Fixing the problem

I'm sure it's obvious, but... reduce the number of maximum connections for the connection pool in question.

const pool = mariadb.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    connectionLimit: 3,
});

Of course, you'll need to bear in mind that with less connections in the pool, your app may struggle to serve larger volumes of requests if it is bottlenecked on waiting for available connections in its pool. You therefore need to set a connection limit that roughly aligns with the amount of connections your server may actually need; the only pitfall here is setting a big number thinking it won't actually be opening all those connections (as the 'connectionLimit' parameter name would imply otherwise).

Back to top