Node.js server not returning response after certain requests

Question

Recently I developed a rest api using nodejs, express , https and mysql to the production. This working good in start but after two to three days I started to get complains that server is not responding and requests returning time out most of the time.

Basically after a certain amount of quests my node.js app stops responding to requests.I even see routes being fired on my console and the HTTP calls from my client (Android app) reach the server all of them.But after restart my node.js app server everything starts working again, until things inevitable stop again. The app never crashes, it just stops responding to requests.

I'm not getting any errors, and I've made sure to handle and log all DB connection errors so I'm not sure where to start.

My database connection code is here :

// call the packages we need
var mysql      = require('mysql');  //call mysql for db connection

// configure the function to connect to the database
var connection = mysql.createConnection({
    connectionLimit : 1000,
    host     : 'localhost',
    user     : 'test',
    password : 'abcdefghi',
    socketPath : '/var/run/mysqld/mysqld.sock'
 });

 exports.connection = connection;

My database query code is here :

var db = require('../dbconnection');
var getPostId = function(id,callback){
    var sql = "SELECT post_id FROM posts.post_feeds WHERE id=?";
    db.connection.query(sql,[id],callback);
}

My app server code:

const fs = require('fs');
const https = require('https');
const express    = require('express');
const app        = express();
const bodyParser = require('body-parser');
const getData = require('./dbhandler/getData');

router.route('/postids/')
    .post(function(req,res){
        var success;
        try {
            var categoryId = req.body.categoryId;
            getData.getPostId(categoryId,function(error, results, fields){
                    if (!error){
                        success = 1;
                        datasets = [];
                        db = {}
                        for (var i = 0 ; i < results.length; i++) {
                            db = {
                                "postId" : results[i]['post_id']
                            }
                            datasets.push(db);
                        }
                        res.json({"success" : success, "datasets" : datasets});
                    } else{
                        logger.error("Route = /postfeedsbycategory/, error = "+error);
                        success = 0;
                        res.json({"success" : success});
                    }
                });
        } catch(error){
            logger.error("Route = /postfeedsbycategory/, error = "+error);
            success = 0;
            res.json({"success" : success});
        }
    });
app.use('/api/v2', router);

app.listen(port);

Any clue as to what might be happening and how I can solve this problem?

Here's my stack:

Node.js on Digital Ocean server with Ubutnu 14.04 and Nginx (using Express 4.15.2 + PM2 2.4.6) Database running MySQL (using node-mysql)

Answer 1

It would be better to use connection pooling:

var mysql = require('mysql');

// configure the function to connect to the database
var pool = mysql.createPool({
    connectionLimit : 1000,
    host     : 'localhost',
    user     : 'test',
    password : 'abcdefghi',
    socketPath : '/var/run/mysqld/mysqld.sock'
 });

 exports.pool = pool;

In your query code:

var db = require('../dbconnection');
var getPostId = function(id,callback) {
    var sql = "SELECT post_id FROM posts.post_feeds WHERE id=?";
    db.pool.query(sql,[id],callback);
}

---

As a point of style, do the following when you process the result:

let datasets = results.map( result => { "postId": result['post_id'] });