Tuesday, 4 April 2017

Node.JS and MySQL - queries lock up and execute extremely slowly

I am getting strange behavior using Node.JS and MySQL with this driver - https://github.com/mysqljs/mysql

Essentially, I have a button on the frontend that triggers an app.get that makes a query in the database and I can happily use the results in my backend.

This works nicely, until I press the button 4-5 times in a second, where as the queries lock up and I have to wait for 2-3 minutes until they continue executing. I have a similar write function that behaves the same way.

Is it possible this is a problem, because I'm trying to execute the exact same query asynchronously? I.e. do I have to limit this from the front end or is it a backend problem?

Any ideas on how to debug what exactly is going on?

// database.js

var mysql = require('mysql');

var pool  = mysql.createPool({
    connectionLimit: 100,
    host     : 'localhost',
    user     : 'secret',
    password : 'secret',
    database : 'mydb'
});

exports.getConnection = function(callback) {
    pool.getConnection(function(err, connection) {
        callback(err, connection);
    });
};


// dbrw.js
var con = require('../config/database');

function read(id, done) {
    con.getConnection(function(id, connection){
        if(!err){
            connection.query("SELECT * FROM users WHERE id = ?",[id], function(err, rows) {
            connection.release();
                if (err)
                    done(err);
                if (rows.length) {
                    console.log("rows " + JSON.stringify(rows));
                    done(rows[0].progress);
                };
            });
        }
        else {
            console.log(err);
        }
    });
}

exports.read = read;


// routes.js
var dbrw = require('./dbrw.js');

app.get('/read', isLoggedIn, function(req, res) {
    dbrw.read(req.user.id, function(result) {
    console.log(result);
    });
});


// Frontend - angular app.js

$scope.tryread = function() {
    $http.get('/read');
}

Thanks in advance for any input.



via angularchobo

No comments:

Post a Comment