Is there a way to close a connection after an elapsed time of idling/sleeping

[y2241]

I have found during load testings (2000 concurrent users) that mysqljs /mysql connection pool uses tons of connections (more than 3000) and those connections are not really closed on client side after the run is over, even I call connection.release() after each query. They are just put into sleep mode. It relies on mysql server to destroy those connections after a certain idling time (by default 24 hours on Aurora).

In most applications I worked on, the client is responsible for disconnecting the connection after idling/stuck/sleep, not by the db server. So to me, this library behaves differently. There is a node mysql-connection-pool-manager module created to make up this gap by periodically destroying the long idling connections. However, I still want to use mysqljs/mysql as my codebase is written on this.

My question is why mysqljs/mysql does not offer this option to do the same cleaning up on client side? The pool.end() does this but this should be only invoked when the application terminates.

If I call connection.destroy() every-time my query is done, will it actually destroy the connection? I am afraid if I call destroy, then the pool won't be able to reuse the connection and cause performance issue.

Also, regarding the high connection usages, does the connection.release() a synchronous call? because I notice one user sometimes needs 2 connections even I execute my queries sequentially (excluding the connection.release()).

Please help.

Thanks.

Richard

[mbaumgartl]

Someone already created a PR for this: #2218

[Zikoel]

Waiting some news about this feature and the PR, I have implemented something like this. This is working fine in my production.

TS

  const getConnectionFromPool = (
    pool: mysql.Pool
  ): Promise<mysql.PoolConnection> => {
    return new Promise((resolve, reject) => {
      pool.getConnection(
        (err: mysql.MysqlError, connection: mysql.PoolConnection) => {
          if (err) {
            debug(`Cant get connection from pool`)
            return reject(err)
          }

          // This is a provissory solution waiting this pull request https://github.com/mysqljs/mysql/pull/2218
          const connectionIdleTimer = (connection as any).__idleCloseTimer
          if (connectionIdleTimer) {
            clearTimeout(connectionIdleTimer)
          }

          ;(connection as any).__idleCloseTimer = setTimeout(() => {
            debug('close connection due inactivity')
            (pool as any)._purgeConnection(connection)
          }, 30 * 1000)

          return resolve(connection)
        }
      )
    })
  }

JS:

  const getConnectionFromPool = pool => {
    return new Promise((resolve, reject) => {
      pool.getConnection(
        (err, connection) => {
          if (err) {
            debug(`Cant get connection from pool`)
            return reject(err)
          }

          // This is a provissory solution waiting this pull request https://github.com/mysqljs/mysql/pull/2218
          const connectionIdleTimer = connection.__idleCloseTimer
          if (connectionIdleTimer) {
            clearTimeout(connectionIdleTimer)
          }

          connection.__idleCloseTimer = setTimeout(() => {
            debug('close connection due inactivity')
             pool._purgeConnection(connection)
          }, 30 * 1000)

          return resolve(connection)
        }
      )
    })
  }