Connection pooling

The mysql npm module creates one connection per client instance. Every database call opens a connection, runs the query, and you’re responsible for closing it. Under concurrent requests, connections pile up. MySQL has a max_connections setting – default 151 – and when you hit it, new queries fail.

I hit this in production. The app was handling maybe forty concurrent requests and MySQL started refusing connections. The error is Too many connections and there’s no graceful fallback. Queries fail.

The pool

generic-pool is a general-purpose object pool for Node.js. It manages a set of resources – create them, hand them out, take them back, destroy them. I wrapped the MySQL driver with it:

var mysql = require('mysql');
var GenericPool = require('generic-pool');

var pool = GenericPool.Pool({
  name: 'db-pool',
  create: function(cb) {
    var conn = mysql.createClient(config);
    conn.on('error', function(err) {
      util.log('[db] ' + err);
    });
    cb(null, conn);
  },
  destroy: function(conn) {
    conn.end();
  },
  max: config.poolSize || 5
});

Five connections in the pool by default. The create function makes a new MySQL client. The destroy function closes it. generic-pool handles the rest – queueing requests when all connections are busy, creating new ones up to max, recycling idle ones.

The wrapper

The pool’s acquire/release API is verbose for something you do on every query. I wrapped it:

pool.query = function(sql, params, cb) {
  if (typeof params === 'function') {
    cb = params;
    params = [];
  }

  pool.acquire(function(err, conn) {
    if (err) return cb(err);
    conn.query(sql, params, function(err, rows) {
      pool.release(conn);
      cb(err, rows);
    });
  });
};

Acquire a connection, run the query, release the connection. Every model in the app calls db.query(sql, params, callback) and doesn’t think about connections. The pool handles contention.

The params argument is optional – the typeof check lets you call db.query('show tables;', callback) without passing an empty array. Small thing but it matters when you have a lot of model files.

What changed

Five pooled connections replaced unlimited ad-hoc connections. The Too many connections error went away. Under the same load that was failing before, the pool queues requests instead of opening new connections. Fifty lines, one module, and the problem is gone.