Mysql: Long response time

[ajpyoung]

First off I'd like to apologize if this is the wrong place to ask this question. I've already exhausted all my other options, even posted at stackoverflow (http://stackoverflow.com/questions/35544503/google-cloud-sql-or-node-mysql-answers-a-long-time).

We have this project using Polymer as the FrontEnd and Node.js as the API being consumed by Polymer, and our Node API replies a really long time especially if you just leave the page alone for like 10 minutes. Upon further investigation by inserting a DATE calculation in the MySQL Query, I found out that MySQL responds a Really long time. The query looks like this:

var query = dataStruct['formed_query'];
console.log(query);
var now = Date.now();
console.log("Getting Data for Foobar Query============ "+Date());
console.log(query);
GLOBAL.db_foobar.getConnection(function(err1, connection) {
    ////console.log("requesting MySQL connection");
    if(err1==null)
    {
        connection.query(query,function(err,rows,fields){
            console.log("response from MySQL Foobar Query============= "+Date());
            console.log("MySQL response Foobar Query=========> "+(Date.now()-now)+" ms");
            if(err==null)
            {
                //respond.respondJSON is just a res.json(msg); but I've added a similar calculation for response time starting from express router.route until res.json occurs
                respond.respondJSON(dataJSON['resVal'],res,req);

            }else{
                var msg = {
                    "status":"Error",
                    "desc":"[Foobar Query]Error Getting Connection",
                    "err":err1,
                    "db_name":"common",
                    "query":query
                };
                respond.respondError(msg,res,req);
            }
            connection.release();
        });
    }else{
        var msg = {
            "status":"Error",
            "desc":"[Foobar Query]Error Getting Connection",
            "err":err1,
            "db_name":"common",
            "query":query
        };
        respond.respondJSON(msg,res,req);
        respond.emailError(msg);
        try{
            connection.release();
        }catch(err_release){
            respond.LogInConsole(err_release);
            respond.LogInConsole(err_release.stack);
        }
    }
});
}

When Chrome Developer tools reports a LONG PENDING time for the API, this happens to my log.

SELECT * FROM `foobar_table`  LIMIT 0,20;
MySQL response Foobar Query=========> 10006 ms

I'm dumbfounded as to why this is happening.

We have our system hosted in Google Cloud Services. Our MySQL is a Google SQL service with an activation policy of ALWAYS. We've also set that our Node Server, which is a Google Compute Engine, to keep alive TCP4 connections via:

echo 'net.ipv4.tcp_keepalive_time = 60' | sudo tee -a /etc/sysctl.conf
sudo /sbin/sysctl --load=/etc/sysctl.conf

I'm using mysql Pool from node-mysql

db_init.database = 'foobar_dbname';
db_init=ssl_set(db_init);
//GLOBAL.db_foobar = mysql.createConnection(db_init);
GLOBAL.db_foobar = mysql.createPool(db_init);
GLOBAL.db_foobar.on('connection', function (connection) {
    setTimeout(tryForceRelease, mysqlForceTimeOut,connection);
});

db_init looks like this:

db_init = {
            host     : 'ip_address_of_GCS_SQL',
            user     : 'user_name_of_GCS_SQL[![enter image description here][1]][1]',
            password : '',
            database : '',
            supportBigNumbers: true,
            connectionLimit:100
        };

I'm also forcing to release connections if they're not released in 2 minutes, just to make sure it's released

function tryForceRelease(connection)
{
    try{
        //console.log("force releasing connection");
        connection.release();
    }catch(err){
        //do nothing
        //console.log("connection already released");
    }
}

This is really wracking my brains out here. If anyone can help please do.

[dougwilson]

Hi @ajpyoung, I'm sorry to hear you are having issues with your MySQL setup. The reason you are see the 10s time is because that is the default for the acquireTimeout pool option (https://github.com/felixge/node-mysql#pool-options). When you call getConnection, before handing you back an establish connection, we send a MySQL PING packet to the server. For some reason, your setup is probably dropping TCP packets during that operation, as Node.js is not reporting to this module that the TCP connection has closed, but yet Node.js is not getting a response back from the server, so after the 10 second timeout, we give up on that connection and just make a new one.

You have several options here:

1. Get a packet capture and perform netstat operations on both your Node.js server and your MySQL server to see what the connection state is on each side, to start to locate where the issue is.
2. Just ignore the issue and set a really low acquireTimeout value so you'll timeout quicker.
3. Don't use the pool at all and just create a brand new connection to your server every time, as the pool cannot seem to determine that your connection has gone down.

I hope this helps!

[dougwilson]

An additional option of course may be to debug your issue in Node.js and perhaps a pull request is in order :) Every time I've heard this issue it has already ended up being a configuration issue with those services, but I have never used them and no one has shared with me any tips to give out for configuration.

[ajpyoung]

ok will implement the low acquireTimeout value. Thanks!

although I've implemented your solution 3 (create a new single connection and end/destroy once data comes in). It seems to work, but I don't know if it's as fast as the Pool solution (which was EXTREMELY FAST; about 5ms-8ms response from MySQL).

[dougwilson]

Yea, the pool will be faster, since you won't have to keep re-establishing a connection each time you need to do something, but in order for the pool to be that fast, it has to keep idling connections around to hand back out when needed. Unfortunately the issue you end up running into is that during the idling connection process, something is dropping the connection between Node.js and your MySQL server in some way, which was not expected.