Process-spawning issue with node js setInterval and nohup

Question

I'm running a node.js script to broadcast data to all connected web-app users every 15 seconds. It's running with this command...

nohup node /pushNotifications.js &

And this is the code...

var https = require('https'), fs = require('fs'), app = require("express"), key = fs.readFileSync('apache.key', 'utf8'), cert  = fs.readFileSync('apache.crt', 'utf8')
var server = https.createServer({key: key, cert: cert}, app); 

server.listen(8080)
var io = require("socket.io").listen(server);
var mysql = require('mysql');

function handler (req, res) {
}

io.on('connection', function (socket) {

  setInterval( function() {

    var connection = mysql.createConnection({
      host: 'db.server.com', user: 'dbuser', password: 'dbpass', database: 'dbname', port: 3306
    });

    connection.connect(function(err){
    });

    connection.query('SELECT someColumn FROM someTable ', function(err, rows, fields) {
      if (!err) {
        socket.emit('notifications', JSON.stringify(rows));
        connection.end();
      } else {
        connection.end(); 
      }
    }); 

  }, 15000); //15 seconds

}); 

It's always worked fine, but recently I've started getting errors in the web app saying "User already has more than 'max_user_connections' active connections", and upon investigation at the DB level using MySQL's "show processlist", I see rapidly spawning/dying connections - all I need to do is kill/restart the pushNotifications.js script and everything is back to normal.

What I'm hoping is that somebody sees something wrong with my code that may be failing to handle a scenario that could lead to processes repeatedly spawning at intervals more regular than every 15 seconds. Appreciate any thoughts at all because I'm out of ideas to diagnose this further.

Answer 1

You're creating a new database connection for each client connection and each interval, which is rather wasteful.

It's much better to create a decently sized connection pool once, and use connections from that:

let pool  = mysql.createPool({
  connectionLimit : 10, // this may require tweaking depending on # of clients
  ...
});

io.on('connection', function(socket) {
  setInterval(function() {
    pool.query('SELECT someColumn FROM someTable ', function(err, rows, fields) {
      if (!err) {
        socket.emit('notifications', JSON.stringify(rows));
        connection.end();
      } else {
        connection.end(); 
      }
    }); 
  }, 15000);
});

Some additional remarks:

• once a client disconnects, its associated interval isn't cleared/stopped. At some point that will start causing problems, because its running queries on behalf of a client that isn't there anymore; you should probably use a listener on the disconnect event to call clearInterval to clean up resources when the server detects a client disconnected.
• your example code doesn't show if the database query is specific for each client. If it's not, you should move the interval to outside the io.on() block entirely, and use Socket.IO broadcasting to send all connections clients the data (instead of running the exact same query for each client separately)