Node-oracledb: connectionsOpen never reaches poolMin of 0

[bnason]

I have created a pool, with a poolMin of 0 and a poolTimeout of 10 seconds (for testing purposes). However, connectionsOpen never reaches 0 after having closed all open connections.

The following example simply creates a pool, creates 1 connection and immediately closes it. It then checks the value of connectionsOpen every 5 seconds. I would expect this to go down to 0 after 10 seconds, however it never does.

In our application, no connection may be used for a long period of time, for instance overnight. The first connection attempt the next morning stalls and never returns. We think it is because this lone connection has timed out or become unresponsive.

Example:

const oracledb = require('oracledb');

const start = new Date();

async function createPool() {
    var connection;
    try {
        // Create the pool
        console.log('Creating the pool');
        await oracledb.createPool({
            user: 'user',
            password: 'mypass',
            connectString: 'myconnectstring',
            poolMin: 0,
            poolTimeout: 10
        });
        console.log('Pool created successfully');

        // Grab a single connection
        console.log('Getting a connection');
        connection = await oracledb.getConnection();
        console.log('Connection obtained');
    }
    catch (error) {
        console.error(error);
    }
    finally {
        if (connection) {
            try {
                // Release the connection back to the pool.
                console.log('Closing the connection');
                await connection.close();
                console.log('Connection closed');
            }
            catch (error) {
                console.error(error);
            }
        }

        // Start a timer to keep track of opened connections
        setInterval(() => {
            const pool = oracledb.getPool();
            const now = new Date();
            const diff = now.getTime() - start.getTime();
            const secondsPassedBy = Math.round(diff / 1000).toString();
            console.log(`${secondsPassedBy.padStart(4)}s connectionsOpen = ${pool.connectionsOpen}`);
        }, 5000);       
    }
}

createPool();

```console.log(process.version);
console.log(process.arch);
console.log(oracledb.versionString);
console.log(process.platform);
console.log(oracledb.oracleClientVersionString);
console.log(connection.oracleServerVersionString);

• Output
  v10.13.0
  x64
  3.0.1
  win32
  12.2.0.1.0
  12.1.0.2.0
  ```

[anthony-tuininga]

The session pool supports the concept of a "maximum lifetime session" after which the session will automatically be replaced (after it is released back to the pool). This is not currently exposed in node-oracledb, however. I've added the enhancement label accordingly!

[cjbj]

Connection pools won't shrink unless connections are being checked in or out. This lets pools reach a minimal stable size during active use. There is no background timer that kills idle connections. Note the best-practice recommendation to avoid connection storms is to keep poolMin=poolMax (and poolIncrement = 0). This is OK, of course, unless you want the DB resources to be freed for other users.

For background, node-oracledb pool connection management is mostly handled by the lower C 'Oracle Call Interface' session pool. Queuing is the exception, being handled in JavaScript.

None of this solves your base problem, which will need some further tracing.

[bnason]

Is there any way for me to manually kill the final connection?

For now, I've added a 60 second timer that simply calls SELECT 1 FROM DUAL to keep the connection alive. Hoping this will mitigate the issue for the time being.

Thanks!

[cjbj]

Leaving aside that these are band-aids over a real problem, you could simply restart the pool when connections in use is 0. Or use a standalone session to execute an ALTER SYSTEM KILL SESSION command on the final pooled connection. If you're going to execute a query to keep a connection alive, you could simply call connection.ping() instead.

What's your poolPingInterval ?

[bnason]

What's your poolPingInterval ?

10 minutes. Does this setting work the same way as if I did a connection.ping() every 10 minutes? Or does it require the pool being accessed to work?

Leaving aside that these are band-aids over a real problem, you could simply restart the pool when connections in use is 0. Or use a standalone session to execute an ALTER SYSTEM KILL SESSION command on the final pooled connection. If you're going to execute a query to keep a connection alive, you could simply call connection.ping() instead.

If the above is the same. I may just implement a timer to restart the pool when no activity has occurred for X amount of time since it seems that poolPingInterval does not fix my issue.

[cjbj]

With your versions, poolPingInterval is happening before your first connection in the morning if the connection has been idle for 10 minutes.

A connection.ping() is the same as your SELECT (though it doesn't do any SQL).

Can you describe the hang more precisely? Is it a pool.getConnection() that hangs? How long have you left it for? What are your production pool configuration values (maxPool etc)? What's your UV_THREADPOOL_SIZE - where is it set?

I presume you restart the app; how long does it take for the connection to be established after a restart?

Have you done any network tracing?

What's happening in your network layer? Do you have firewall timeouts?

Are you using DRCP?

Do you have a sqlnet.ora with things like SQLNET.OUTBOUND_CONNECT_TIMEOUT set?

Do you have a FAN enabled service - do you set events: true when you create the pool?

Is this all on Windows? The one machine or multiple machines?

[ah584d]

Hi @bnason & @cjbj ,
i have exactly the same issue described above, and i defined UV_THREADPOOL_SIZE to 128.
I also thought to destroy my pool after a while of inactivity, but sounds too much overkill.

@bnason which version of oracle driver do you use?

does the remaining opened connection is related to this?

“_The ping feature is a no-op when node-oracledb is linked with Oracle 12.2 client libraries (independent of the database version at the other end of the connection), since those libraries have an always-enabled, lightweight connection check that removes the need for node-oracledb to do its own ping. This adds even more certainty that a connection will be valid at time of first use after a getConnection() call._ ”

source:
https://blogs.oracle.com/opal/node-oracledb-112:-working-with-lobs-as-string-and-buffer-connection-pinging

[cjbj]

does the remaining opened connection is related to this?

I don't see it being related. The internal ping would only happen when your app calls pool.getConnection().

The document you quoted is no longer true in node-oracledb 3.0 - we re-enabled the internal heavy-weight ping, see the CHANGELOG (commit is here).

[ah584d]

"_The internal ping would only happen when your app calls pool.getConnection()_."
According to the CHANGELOG:

Additionally enable poolPingInterval functionality when using Oracle client libraries 12.2, or later, to aid silent pool connection re-establishment after connections exceed database session resource limits (e.g. ORA-02396), or are explicitly closed by DBAs (e.g. ORA-00028). (ODPI-C change).

What will happen if the connection has been closed by a firewall between client and server?
Does on the nest getConnection, ping functionality will be able to get a new valid connection?
For what i saw the code will be stuck on function getConnection, and no exception will be throws....blocking the main thread.

Bottom line, in may case poolPingInterval is not able to return me a valid connection.
Is there a chance to get a fix soon?
As well as, the fact that the number of connections never drops to 0 is also an issue, that you to explain why it occurs.

thanks

[cjbj]

The pool not returning to zero is explained in https://github.com/oracle/node-oracledb/issues/1012#issuecomment-442598217 This is a design 'feature' in current versions of OCI session pooling.

If a firewall has closed a connection, either the internal connection check (with 12.2+ client) or the poolPingInterval check will re-establish the connection.

Overall, unless we can reproduce your problem, we won't know what the cause is, or what the solution is.

Please check for DB server trace files, and consider enabling client tracing (see the Oracle ADR doc).

[cjbj]

@bnason @ah584d Any updates? Also see the questions in https://github.com/oracle/node-oracledb/issues/1012#issuecomment-442657790.

You could run a tool like pstack so we can see where the hang is.

Also, consider setting SQLNET.RECV_TIMEOUT and SQLNET.SEND_TIMEOUT as mentioned in Connections and High Availability documentation.

[danilohgds]

In our application, no connection may be used for a long period of time, for instance overnight. The first connection attempt the next morning stalls and never returns. We think it is because this lone connection has timed out or become unresponsive.

This strikes me as a similar issue I had in a previous project, have you checked for firewall rules from your server to the DB server? It is not uncommon to see firewalls breaking apart an inactive connection and make it seem as if your service is no longer returning, which in fact is just the firewall messing around with your network. If you have access you may check the sessions on the database side, this might also help.

Side note, I cannot stress enough how the min=max rule is important for stable connectivity. Give it a try, it will win you over.

Have some healthcheck (Kubernetes term, look it up) in place in your application to determine overall health status and restart the microservices in case needed. This removes the unnecessary "Select 1 from dual" band-aid you have in place, and instead just have the driver check for connectivity with connection.ping().

[cjbj]

@bnason @ah584d I just pushed some updates to the master branch on github. @anthony-tuininga fixed an ODPI-C buglet with pinging that he points out could be related to your problem. Try compiling the master branch and let us know.

[cjbj]

@bnason @ah584d any update on this?

[cjbj]

Closing - no activity.

[gdrbyKo1]

The application I'm currently working on needs to perform some DB operations only once a day, and then stays idle most of the time. I'm using a connection pool to run the queries, and it wasn't a pleasant surprise when I found out that node-oracledb doesn't ever terminate the idle connections. I guess I can't really complain, as this matches with what the docs say: "Idle connections are terminated only when the pool is accessed."
So if the daily routine spawned let's say 3 connections in a pool, then there will be 3 tcp connections waiting idle for 24 hours or so. This isn't really acceptable behavior for me, so I'm looking for a way to get connectionsOpen to 0 after a period of inactivity.
I've read the discussion here, and I'm still not sure how to handle this properly.

Would it make sense to periodically check pool.connectionsInUse, and terminate the pool when it's 0? Ideally I'd like to only terminate the pool when pool.connectionsInUse was 0 for some time, for example 60 seconds. But to implement that reliably, I'd need some sort of events, rather than polling that value every second or so.
I'd prefer to avoid having to monkey-patch the .close() method of a connection to keep track of the number of connections in use.
Any advice will be appreciated.

[cjbj]

@gdrbyKo1 polling and checking connectionsInUse should be pretty lightweight since it a client-side attribute check.

[gdrbyKo1]

@cjbj Indeed, but the concern here is that a connection could be checked out from the pool, then used to execute a query, and finally closed again in less than whatever the poll interval would be. This could yield false positive pool termination signals. The pool should only be closed after it's been unused for a specified time.

[cjbj]

The real question is why is it an issue if the connections stay open?

[gdrbyKo1]

Because my application isn't the only one which interfaces with the DB instance using a given user account, and the user has some limits on the maximum number of simultaneous connections. The configuration of the database instance lies outside of my control, so I'm really trying to handle this on the client side with node-oracledb instead. Simply put, if the pool was idle for some time, it should just terminate all connections to the DB, ie. return to the state it was when the pool was first returned by the createPool call.

[gdrbyKo1]

I think I've found a somewhat acceptable workaround for this issue.

const OracleDB = require('oracledb');

const sleep = (ms) => new Promise(resolve => setTimeout(resolve, ms));

const getPoolInfo = ({ connectionsInUse, connectionsOpen }) => ({ connectionsInUse, connectionsOpen });

const runQuery1 = async (pool) => {
    console.log('Before query 1:', getPoolInfo(pool));
    const conn = await pool.getConnection();
    await sleep(1000);
    await conn.close();
    console.log('After query 1:', getPoolInfo(pool));
}

const runQuery2 = async (pool) => {
    console.log('Before query 2:', getPoolInfo(pool));
    const conn = await pool.getConnection();
    await sleep(7000);
    await conn.close();
    console.log('After query 2:', getPoolInfo(pool));
}

async function foo() {
    const pool = await OracleDB.createPool({
        user: '...',
        password: '...',
        connectString: '...'
    });

    console.log('Fresh pool: ', getPoolInfo(pool));

    setInterval(async () => {
        console.log(`interval callback`, getPoolInfo(pool));
        if (pool.connectionsInUse > 0) {
            console.log('at least one connection is still in use');
            return;
        }
        if (pool.connectionsOpen < 1) {
            console.log('no connections in use, but also no open connections');
            return;
        }
        console.log('dropping some connection...');
        const conn = await pool.getConnection()
        await conn.close({ drop: true });
        console.log('dropped a connection', getPoolInfo(pool));
    }, 5000);

    await Promise.all([runQuery1(pool), runQuery2(pool)]);
}

foo();

Perhaps not the most elegant solution, but it gets the job done. This is similar in principle to what @stevemandl attempted back in 2016. I have no idea for how long the connPool.close method has supported the drop parameter, but it can be used to achieve what both of us had in mind, and that is to get the connectionsOpen value down to 0.

TLDR: you can get connectionOpen back to 0 with something similar to this:

setInterval(async () => {
    const conn = await pool.getConnection();
    await conn.close({ drop: true });
}, 60000);

This will eventually drop all the idle connections, because node-oracledb itself terminates idle connections when getConnection() is called.

[cjbj]

@gdrbyKo1 not so clearly mentioned in the current edition of the 20c new features guide is a change that will do what you want. We need to tweak ODPI-C pool usage to take advantage of the session pool change. So, when the new node-oracledb and new Oracle Client versions are both available (which I can't predict), then your workaround won't be necessary.