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

Back to top