PoolCluster connections

[kelchy]

I am struggling to find the reason why tcp connections to my server kept growing ang growing and it does not go down. currently using 2.13.0

i do not seem to notice this issue on older version. any idea where to start hunting?

var mysqlpool = mysql.createPoolCluster({ "restoreNodeTimeout" : 3 * 60 * 1000 });

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.1',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.2',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.3',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

function mysqlQuery(q, callback) {
    mysqlpool.getConnection(function (err, connection) {
        if (!err) {
            connection.query(q, function (err, rows) {
                connection.release();`
                if (!err) callback(rows);
                else callback(undefined);
            });
        } else {
            callback(undefined);
        }
    });
}

[dougwilson]

Hi @kelchy that is weird, indeed! I just quickly tried out your code above, using three dockers for the three hosts and run it for many hours just with a setInterval around your mysqlQuery but wasn't able to reproduce. Can you provide instructions for how to reproduce the issue?

[kelchy]

i used a sample test.js which require another db handler

var db = require('./db');

for (var i=0; i<200; i++) {
    db.mysqlQuery("SELECT " + i, function(rows) {
       console.log(rows);
    });
}

then my db handler db.js looks like this

var mysql = require('mysql');
var mysqlpool = mysql.createPoolCluster({ "restoreNodeTimeout" : 3 * 60 * 1000 });

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.1',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.2',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

mysqlpool.add({
        connectionLimit : 100,
        host     : '192.168.1.3',
        user     : 'username',
        password : 'password',
        database : 'db_name'
});

function mysqlQuery(q, callback) {
    mysqlpool.getConnection(function (err, connection) {
        if (!err) {
            connection.query(q, function (err, rows) {
                connection.release();`
                if (!err) callback(rows);
                else callback(undefined);
            });
        } else {
            callback(undefined);
        }
    });
}

the expectation is after creating 300 connections. after a while, it should go down.
but it is not going down, it stays at 300. in real live situations, it does not actually reach 300
but it keeps growing and growing over time, i have a graph monitoring it and it never goes
down ever.

[dougwilson]

I'm not sure I understand your reproduction case there. You queued up 200 queries all simultaneously, so they all try to execute at once in parallel. Since you have three pools of 100 each, that means the cluster can open up to 300 connections, and 200 is less than 300. Pool management will never close a connection, just keep it idle waiting for the next query.

It sounds like you are describing the expected behavior of the pool to me: you have it configured to use up to 300 connections and it will do that...

[dougwilson]

I believe #1276 is the issue for the feature you are requesting. I don't think anyone has made a PR to implement it, so you're definitely welcome to :) !

[kelchy]

shouldn't it recycle idle connections first before creating a new one?
recycling will have to be faster than tcp handshake right?
but yes i believe "connection lifetime" should be a good a feature to have

[dougwilson]

Hi @kelchy it does recycle the idle connections, but in your example code, none are idling yet. This is because you queued up 200 queries all simultaneously, so they all try to execute at once in parallel. Since you have three pools of 100 each, that means the cluster can open up to 300 connections, and 200 is less than 300. Pool management will never close a connection, just keep it idle waiting for the next query.