How to keep mysql connections alive in node.js?

Question

I'm using mysql connection pools in Node JS. After some idle time, the connections expire and the next time I perform a query, a new connection needs to be created. This can cause a delay of several seconds. Unacceptable!

I would like to implement keepalive functionality to periodically poll the database and ensure the consistent health of connections to the backend. I am looking for input from others who have attempted the same, or feedback on my approach.

const mysql = require('mysql');
const pool = createConnectionPool();

setInterval(keepalive, 180000); // 30 mins

function keepalive() {
  pool._freeConnections.forEach((connection) => pool.acquireConnection(connection,function () {
    connection.query('SELECT 1 + 1 AS solution', function (err) {
      if (err) {
        console.log(err.code); // 'ER_BAD_DB_ERROR'
      }
      console.log('Keepalive RDS connection pool using connection id', connection.threadId);
    })
  }));
}

This keepalive has been somewhat successful:
- once a connection is opened, it stays open
- connections never time out
- if a connection is lost, it is recreated on the next interval

This keepalive is not ideal:
- the mysql connection pool is lazy, only creating and restoring connections as needed. with this keepalive, the pool is no longer lazy. once a connection is opened, the keepalive will keep it open. the pool no longer scales depending on traffic.
- i'm not confident that my method of iterating through the list of free connections and performing a query will be a wise approach. is it possible for the application to check out the same connection from the pool while the same connection is being used by keepalive?

Another possible approach is to ditch the keepalive functionality within the application, and rely on heartbeat traffic to keep a minimum of connections alive.

Has anybody attempted to implement keepalive functionality, use a package or tool that provides this feature, or ended up using a different approach?

Answer 1

did you try this one in case if using pool connection

const pool = mysql.createPool({...});

function keepAlive() { 
  pool.getConnection(function(err, connection){
    if(err) { console.error('mysql keepAlive err', err); return; }
    console.log('ping db')
    connection.ping();     // this is what you want
    connection.release();
  });
}
setInterval(keepAlive, 60000); // ping to DB every minute

Answer 2

I don't use pool and this code works

function pingdb() {
  var sql_keep = `SELECT 1 + 1 AS solution`; 
  con.query(sql_keep, function (err, result) {
    if (err) throw err;
    console.log("Ping DB");
  });
}
setInterval(pingdb, 40000);