3

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?

Megan D
  • 379
  • 3
  • 13
  • Megan, this could work for you: http://stackoverflow.com/a/17015816/1757214 – manuerumx Nov 08 '16 at 23:34
  • @manuerumx it doesn't address my question ; i am already using the connection pool feature. the pool will automatically restore a lost connection when the application tries to use it, but if there are no active connections in the pool when the app requests one, there is a delay while it restores the connection. i want to keep at least one connection in the pool alive at all times to avoid the wait. – Megan D Nov 09 '16 at 00:33
  • @MeganD Did you find any solution? Although it 's a long time back, I am stuck with a similar kind of situation now. – Kishor Oct 22 '19 at 10:36
  • 1
    @Kishor No; the database closes stale connections, so when the API isn't used, the connections are closed. Eventually we added monitoring that periodically uses the API, and this kept the database connections active. – Megan D Dec 02 '19 at 18:22
  • i am using similar method to keep mysql connection alive – Vikas Kandari Aug 24 '21 at 09:50

2 Answers2

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
Victor Orletchi
  • 469
  • 1
  • 5
  • 15
1

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);
holydragon
  • 6,158
  • 6
  • 39
  • 62
mkt
  • 11
  • 2