Nodejs Cluster with MySQL connections

Question

Looking on advice on clustering of Nodejs and the method of connection to mysql server. Do we open one connection for each child process or just one single connection for all processes? Or do we create a connection pool for all the child processes? Which is the recommended method?

one node process

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

node cluster option 1:

var cluster = require('cluster');
var http = require('http');
var numCPUs = require('os').cpus().length;

if (cluster.isMaster) {
  // Fork workers.
  for (var i = 0; i < numCPUs; i++) {
    cluster.fork();
  }

  cluster.on('exit', function(worker, code, signal) {
    console.log('worker ' + worker.process.pid + ' died');
  });
} else {
  // open one connection for each process
  var mysql      = require('mysql');
    var connection = mysql.createConnection({
      host     : 'example.org',
      user     : 'bob',
      password : 'secret'
    });

    connection.connect(function(err) {
      if (err) {
        console.error('error connecting: ' + err.stack);
        return;
      }

      console.log('connected as id ' + connection.threadId);
    });
}

option2:

var cluster = require('cluster');
var http = require('http');
var numCPUs = require('os').cpus().length;

var mysql = require('mysql');
var pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'example.org',
    user            : 'bob',
    password        : 'secret'
});

if (cluster.isMaster) {
  // Fork workers.
  for (var i = 0; i < numCPUs; i++) {
    cluster.fork();
  }

  cluster.on('exit', function(worker, code, signal) {
    console.log('worker ' + worker.process.pid + ' died');
  });
} else {

  // accept http connections and query
  pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
     if (err) throw err;

     console.log('The solution is: ', rows[0].solution);
  });
}

Answer 1

After some trial and error, I'm using option 2 and it is working well. Created the connection globally and use in the cluster.

Each time you need to query, getConnection.

pool.getConnection(function(err, connection) {
        if(err) {
            console.log('Error getting sql connection');
            console.dir(err);

            if(typeof connection !== "undefined")
                connection.release();

            callback(err);
        }

        if(typeof cb === "undefined") {
            //console.log('with 2 params');
            connection.query( sql, function(err, rows) {
                connection.release();
                console.dir(sql);
                // console.dir('data=>' + data);

                if(err) {
                    console.log('err:' + err);
                    callback(err, rows);
                }else{
                    console.log( rows );
                    callback(err, rows);
                }
            });
        } else {
            // console.log('with 3 params:' + cb);
            connection.query( sql, data, function(err, rows){
                connection.release();
                console.log(sql);
                console.dir(data);

                if(err) {
                    console.log('err:' + err);
                    callback(err, rows);
                }else{
                    console.log( rows );
                    callback(err, rows);
                }

            });
        }
    });
}