connection pool does not seem to evict closed connections

[chanan]

Please answer these questions before submitting your issue. Thanks!

1. What version of NodeJS are you using (node --version and npm --version)? 16.5.1

2. What operating system and processor architecture are you using?

Linux (AWS k8s)

4. What are the component versions in the environment (npm list)?

5. What did you do? Created a pool per documentation, At first all is good. The longer the application is up, the more errors I get.

6. What did you expect to see? Pool should return valid connections

7. What did you see instead? Error: Error executing select [snip]. Error: Unable to perform operation using terminated connection.

8. Add this to get standard output.

sync function executeAsync(options) {
  const logStr = `${options.sqlText} params: ${options.binds}`;
  console.time(logStr);
  return new Promise((resolve, reject) => {
    pool.use(async (clientConnection) => {
      await clientConnection.execute({
        ...options,
        complete: function (err, stmt, rows) {
          console.timeEnd(logStr);
          if (err) {
            reject(err);
          } else {
            resolve({ stmt, rows });
          }
        },
      });
    });
  });
}

[manvydasu]

+1 on this.

I left the application running and now connection pool is full of "dead" connections and queries fail with:

Unable to perform operation using terminated connection.

Isn't it that one of the requirements for proper connection pool is to have proper connection management and dead connections should be renewed?

[sfc-gh-dszmolka]

i know this comment is super late and probably not useful anymore, but still putting it here in case someone stumbles upon this issue in the future, maybe it would help them.

so this behaviour is sorta-kinda expected and the closed connections are really not evicted - by default. we do document (in a way which is easy to miss :( ) that the ConnectionPool uses node-pool for implementing the pools and further information about the pool's options is available in the node-pool library documentation

Under the opts and Idle Object Eviction sections they mention that the pool does have an evictor which is off by default, so the initial observation is correct, the closed connections are really not evicted.

To address this behaviour, one could 1., create the Snowflake ConnectionPool with enabled evictor by adding evictionRunIntervalMillis to the pool options, something like

const pool = snowflake.createPool(
    {
      account: account,
      username: username,
..rest of the connection options..
    },
    {
      evictionRunIntervalMillis: 60000 // default = 0, off
      idleTimeoutMillis: 60000, // default = 30000
      max: 2,
      min: 0,
    },
  );

would run the evictor every minute and evict any connections which is idle for more than a minute.

2. alternatively, keep alive the existing connections in the pool with adding clientSessionKeepAlive: true (default = false) and if a keepalive is necessary more often than every hour, then clientSessionKeepAliveHeartbeatFrequency: n where n is between 900 (15m) and 3600 (1h), default being 3600.

const pool = snowflake.createPool(
    {
      account: account,
      username: username,
..rest of the connection options..
      clientSessionKeepAlive: true,  // default = false
      clientSessionKeepAliveHeartbeatFrequency: 900 // default = 3600
    },
    {
      max: 2,
      min: 0,
    },
  );

This would send a 'heartbeat' call to Snowflake every n seconds, keeping the connection alive even if there's no other activities like queries from the client. clientSessionKeepAlive of course should without using pooled connections too.

Relevant Snowflake documentation for the session keepalive can be found here.

(also I noticed that despite #377, the connector still seems to be sending select /* nodejs:heartbeat */ 1; queries to Snowflake as a heartbeat in the pool, instead of calling the appropriate endpoint, that will be handled separately from this issue)

hope this helps someone.

[sfc-gh-dszmolka]

closing this issue for now, but please reopen if you still need help with the same matter.