Node-mysql stale connections

Question

I have a node-mysql app with mysql-pool set as:

var db = db.createPool({
    host     : (process.env.NODE_ENV == "development") ? config.db.dev.host : config.db.prod.host,
    user     : (process.env.NODE_ENV == "development") ? config.db.dev.uname : config.db.prod.uname,
    password : (process.env.NODE_ENV == "development") ? config.db.dev.passwd : config.db.prod.passwd,
    database : (process.env.NODE_ENV == "development") ? config.db.dev.dbname : config.db.prod.dbname,
    port     : (process.env.NODE_ENV == "development") ? config.db.dev.port : config.db.prod.port,
    connectionLimit    : 100,
    multipleStatements : true,
  acquireTimeout     : 10000,
});

After few days of activity my app starts accumulating stale connections for which I have checked the threadId's doesn't exists in the mysql using

show processlist

I am using connection.release() after each query completes its execution. And I get around 50000 API call a day.

I logged the Pool properties whenever I use a connection using db.getConnection() pool._allConnections.length gradually increases till the max limit (100 in my case) and the app will then hang's for any API calls which uses mysql connection returning 502 Bad Gateway error. db.getConnection() doesn't even throws any error after that.

MySql @@global.wait_timeout is set to 300.

I have sockets and redis also being used in the same app and they continue to work just fine even after the mysql API stops responding.

Can anyone share any insights on this. Thanks in advance.

Answer 1

In this case you should increase the connection limit to for your mysql pool. Your active connections to mysql at a time is crossing your connection limit and your request is getting expired before mysql pool can allot you a connection. You can also try increasing timeout limit.