Big delay to get the pool connection after a few minutes of inactivity.

[leonardsysout]

I'm using pool to improve connection times from database.

I'm trying to use node-oracledb, but doesn't work right because when my application is more then 10 minutes of inactivity, next pool getConnection() delay about 15 minutes to return. After this, comes to work correctly, with good times to get connection.

I searched and found something about proccess starvation, and It was suggested to increase value of the environment variable UV_THREADPOOL_SIZE but without success.

My pool configuration:

Pool attributes:
...connectString: ...
...edition: 
...events: false
...externalAuth: false
...homogeneous: true
...poolAlias: pool
...poolIncrement: 1
...poolMax: 10
...poolMaxPerShard: 0
...poolMin: 2
...poolPingInterval (seconds): 60
...poolTimeout (seconds): 120
...queueMax: 500
...queueTimeout (milliseconds): 5
...sessionCallback: undefined
...sodaMetaDataCache: false
...stmtCacheSize: 30
...user: SUPERA
Related environment variables:
...UV_THREADPOOL_SIZE: 32

Environment:

• Ubuntu 20.04
• node-oracledb: 6.3.0
• Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Someone can help me with this?

[sudarshan12s]

UV_THREADPOOL_SIZE is applicable to thick mode ( explicitly using initOracleClient()) . Can you share high level steps on how pool connections are released back to pool and share the pool statistics. The inactivity (poolTimeout) is used to clean up only the free connections in pool.

[sreguna]

@leonardsysout This could be due to a firewall dropping packets for idle connections. Can you enable TCP Keepalives by setting expireTime(https://node-oracledb.readthedocs.io/en/latest/api_manual/oracledb.html) A good value would be to set it to 1(1 minute).

[leonardsysout]

UV_THREADPOOL_SIZE is applicable to thick mode ( explicitly using initOracleClient()) . Can you share high level steps on how pool connections are released back to pool and share the pool statistics. The inactivity (poolTimeout) is used to clean up only the free connections in pool.

I'm using Thick mode. Pool config:

config.poolAlias = 'pool'; config.poolMin = 2; config.poolMax = 10; config.poolTimeout = 120;

Always, when after execute a query, i close the connection:

        pool.logStatistics();
    let connection;

    try {

        connection = await oracledb.getConnection('pool');
        const result = await connection.execute(sql);

        return result?.rows;

    } catch (e) {

        messages.error('Falha ao executar query: ' + e);
        throw `${e}`;

    } finally {

        if (connection) {
            try {

                await connection.close();
                messages.debug('Conexão fechada!');

            } catch (err) {
                messages.error('Falha ao fechar conexão com o banco de dados: ' + err);
            }
        }

    }

[leonardsysout]

@leonardsysout This could be due to a firewall dropping packets for idle connections. Can you enable TCP Keepalives by setting expireTime(https://node-oracledb.readthedocs.io/en/latest/api_manual/oracledb.html) A good value would be to set it to 1(1 minute).

You were spot on with your guidance. Apparently solved the problem.

Thank you very much!

[sharadraju]

Thanks @leonardsysout for confirming and thanks @sreguna for the solution. Closing the issue.