CODEWITHSUNDEEP
node.jsnode-oracledb
Nikolay Pashchenko
Nikolay Pashchenko

Reputation: 1

Performance issue nodejs connecting to Oracle Database by node-oracledb

I am facing the performance issue during load testing of the Rest API for Oracle Database developed by using Express and OracleDB nodejs modules. I see the decrease in performance during load testing during increasing number of the requests per seconds to the developed API.This issue is reproduced on stored procedure and standard select requests to the database. From database side I see the standard and stable response time for each of the requests. Requests are recieved by the database in the same time as they were initiated. From application(reponse) side it looks like responses being puted in some kind queue and being readed by nodejs(OracleDB module) in packages. With a standard response time in 0,5 seconds per requests, I might recieve 3-5 seconds response time on 10 request per second.

This issues is reproduced on different connection pool sizes(grater than number of requests per seconds). At the moment I am stuck with possible options that might lead to the issue. What might be the reason for such behaviour? Or what options for diagnostic or turning is available for oracleDB module for the nodejs?

some code bellow:

creating the connection:
const init = async () => {
  try {
    Logger.info(`oracle instant client address ${process.env.LD_LIBRARY_PATH}`);
    const pool = await oracledb.createPool(dbCredentials);
    Logger.info('db connections pool created');
    return pool;
  } catch (err) {
    Logger.error(`init() error: ${err.message}`);
  }
};

let pool = await init();

route:

    router.get('/test', async (req, res, next) => {
  try {
    const result = await testController(pool);
    sendResponse(res, 200, result);
  } catch (e) {
    sendErrResponse(res, 500, e.message);
  }
});

controller:

    const testController = async (pool) => {
  let connection;

  try {
    connection = await pool.getConnection();
  } catch (error) {
    return error;
  }

  try {
    let items = { items: [{ itemSKU: 'xxxxx', itemQTY: 1234 }, { itemSKU: 'yyyyy', itemQTY: 123}] };
    items = JSON.stringify(items);
    const { rows: data } = await connection.execute(
      'select shop_id, prod_id, qnt from TABLE(pkg_ecom.check(:items))',
      { items },
    );
    return data;
  } catch (error) {
    return error;
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
};

Upvotes: 0

Views: 1054

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10566

The common problem with scaling node-oracledb connection load is Node.js thread starvation. Increase the value of the environment variable UV_THREADPOOL_SIZE before your application starts. See the documentation Connections, Threads, and Parallelism. On Linux, your package.json file might have:

"scripts": {
    "start": "export UV_THREADPOOL_SIZE=10 && node index.js"
  },
. . .

You can monitor pool usage by setting the enableStatistics attribute during pool creation and then calling pool.getStatistics() or pool.logStatistics(), see Connection Pool Monitoring. Look out for too many getConnection() requests being queued.

You should also read the node-oracledb case study Always Use Connection Pools — and How.

Upvotes: 0

Related Questions