Kill all sleeping mysql pool connections using node

Question

I am using node for my project and using connection pooling, whenever I query show processlist I found more than 200 sleep connections even though i am releasing it after every query like this-

    return new Promise((resolve, reject) => {

    sql.getConnection(function (err, conn) {
        if (err) {
            conn.release();
            reject(err)
        }
        conn.query('QUERY', function (err, rows) {
            conn.release();
            if (err) {
                reject(err)
            }
            else {
                resolve(rows[0])
            }
        })
    })
})

Still i found 200 plus sleep connections. Is there any way to kill useless sleeps connection through node? Or is it fine having so many sleep connections?

Thanks in advance!

score 0 · Answer 1 · answered Mar 12 '20 at 05:44

0

If you are using connection pooling, you will need to close all the connections in the pool when you're finished using them. Otherwise the connections will stay open until they are closed by the MySQL server.

pool.end(function (err) {
  // all connections in the pool have ended
});

This GitHub issue explains a little more about the nuance between releasing a connection (conn.release()) and closing the underlying connection pool (pool.end()).

answered Mar 12 '20 at 05:44

Daniel Bank

3,581
3
39
50

But I cannot close my pool connections, because if i did, nothing will work as it is a live server – ADITYA HAZARIKA Mar 13 '20 at 05:21
You would want to end the pool at least when the application ends under any condition. Otherwise all those connections remain open after you stop the server. Also what is your pool `connectionLimit` set at? – Daniel Bank Mar 13 '20 at 05:25
For now I have not set any limit – ADITYA HAZARIKA Mar 13 '20 at 06:49
Do you start and stop your node server every so often? Every time you do that, it's leaving all the connections it had open. – Daniel Bank Mar 13 '20 at 07:02
Yes, I do start stop often – ADITYA HAZARIKA Mar 13 '20 at 17:47

Kill all sleeping mysql pool connections using node

1 Answers1