Node-oracledb: How to open pool connection in use more than 1

[xeleniumz]

Hi node-oracle community. I got a problem with node-oracle. When my application has many users active and connect to database for read and write it was blocking execute command between node.js application and oracle database. I was enable process.env.UV_THREADPOOL_SIZE = 800
how to open more connection in use or fix it ?

this is my logstat

Pool statistics:
...total up time (milliseconds): 21
...total connection requests: 1
...total requests enqueued: 0
...total requests dequeued: 0
...total requests failed: 0
...total request timeouts: 0
...max queue length: 0
...sum of time in queue (milliseconds): 0
...min time in queue (milliseconds): 0
...max time in queue (milliseconds): 0
...avg time in queue (milliseconds): 0
...pool connections in use: 1
...pool connections open: 1
Related pool attributes:
...poolAlias: undefined
...queueRequests: true
...queueTimeout (milliseconds): 60000
...poolMin: 2
...poolMax: 44
...poolIncrement: 5
...poolTimeout (seconds): 4
...poolPingInterval: 60
...stmtCacheSize: 30
Related environment variables:
...process.env.UV_THREADPOOL_SIZE: 800
INFO: Connections open: 1
INFO: Connections in use: 1

dbOracle.js

import oracledb from "oracledb";


oracledb.connectionClass = "HRPOOL";
oracledb.outFormat = oracledb.OBJECT;

const dbOracle = {
  init: async () => {

    return new Promise(resolve => {
        oracledb.createPool(
          {
            user: user,
            password: pwd,
            connectString: `(DESCRIPTION= 
                      (ADDRESS=
                          (PROTOCOL=TCP)
                          (HOST=10.0.0.254)
                          (PORT=1526)
                      )
                      (CONNECT_DATA= 
                        (SERVICE_NAME=odb3)
                        (SERVER=POOLED)
                      )
                  ) `,
            _enableStats: true,
            poolMax: 44,
            poolMin: 2,
            poolIncrement: 5,
            poolTimeout: 4
          },
          (err, pool) => {
            if (err) {
              console.log(
                "ERROR: ",
                new Date(),
                ": createPool() callback: " + err.message
              );
              return;
            }
            console.log(
              "INFO: Module getConnection() called - attempting to retrieve a connection using the node-oracledb driver"
            );
            pool.getConnection((err, connection) => {
              pool._logStats();
              if (err) {
                console.log("ERROR: Cannot get a connection: ", err);
                return resolve(err);
              }
              if (typeof pool !== "undefined") {
                console.log(
                  "INFO: Connections open: " + pool.connectionsOpen
                );
                console.log(
                  "INFO: Connections in use: " + pool.connectionsInUse
                );
              }
              /* 
              var sql = "SELECT CURRENT_DATE FROM DUAL";
              connection.execute(sql, (err, r) => {
                console.log(r.rows);
              }); */
              return resolve(connection);
            });
          }
        );
    });
  }
}
export default dbOracle

report.js

import db from "./dbOracle";   

const report = {

    testResult:async (req,res) => { 
        var query = `
                Select
                        d.psname1,
                        c.student_name1,
                        c.student_name2,
                        c.personal_id,
                        (select sum(a.score_item)
                        from  dee6014 a
                        where a.start_use  = c.start_use
                        and   a.subject_id = 'F'
                        and   a.class_id   = c.class_id
                        and   a.acdm_id    = c.acdm_id
                        and   a.student_id = c.student_id)score_f,
                        (select sum(a.score_item)
                        from  dee6014 a
                        where a.start_use  = c.start_use
                        and   a.subject_id = 'O'
                        and   a.class_id   = c.class_id
                        and   a.acdm_id    = c.acdm_id
                        and   a.student_id = c.student_id)score_o,
                        (select sum(a.score_item)
                        from  dee6014 a
                        where a.start_use  = c.start_use
                        and   a.subject_id = 'I'
                        and   a.class_id   = c.class_id
                        and   a.acdm_id    = c.acdm_id
                        and   a.student_id = c.student_id)score_i,
                        (select sum(a.score_item)
                        from  dee6014 a
                        where a.start_use  = c.start_use
                        and   a.subject_id = 'C'
                        and   a.class_id   = c.class_id
                        and   a.acdm_id    = c.acdm_id
                        and   a.student_id = c.student_id)score_c,
                        (select sum(a.score_item)
                        from  dee6014 a
                        where a.start_use  = c.start_use
                        and   a.subject_id = 'L'
                        and   a.class_id   = c.class_id
                        and   a.acdm_id    = c.acdm_id
                        and   a.student_id = c.student_id)score_l
                from dee3020 c,
                    dee0020 d  
                where  
                    c.start_use  = '${req.query.YEAR_SEMESTER}'       
                    and   c.acdm_id    = '${req.query.ACDM_ID}'    
                    and   c.class_id   = '${req.query.CLASS_ID}'              
                    and   c.room_id    =   ${req.query.ROOM_ID} 
                    and   d.pname_id   = c.pname_id
                order by c.student_name1,c.student_name2,c.personal_id  
        `;

        var _db = await db.init();
        return new Promise(resolve => {
          _db.execute(query, async (err, r) => {
            await _db.release(err => {
              if (err) console.log(err);
            });
            if (err) console.log(err);
            return resolve(r);
          });
        });

    }

}

export default report;

system detail
node version 8.9.4
oracle driver instantclient-basic-linux.x64-12.2.0.1.0
OS debian 8.2 x64

P.S sorry for my bad english

[dmcghan]

@xeleniumz This code is a hot mess! :)

The biggest mistake you are making is that you're using template strings to build up your SQL statement. This will open you up to SQL injection. You should be using bind variables instead. See this for details.

Next, there's a unique mix of promises and async/await here. Keep in mind that the driver's APIs only return promises when callbacks are not passed in. Also, when you create your own promise, you need to accept both resolve and reject, not just resolve - you have to handle errors and reject your promises. Read this on promises and this on async/await.

Here's an example of what your testResult function should look like if using async/await.

import db from "./dbOracle";   

const report = {

    testResult:async (req,res) => {
        try {
            var query = `
                    Select
                            d.psname1,
                            c.student_name1,
                            c.student_name2,
                            c.personal_id,
                            (select sum(a.score_item)
                            from  dee6014 a
                            where a.start_use  = c.start_use
                            and   a.subject_id = 'F'
                            and   a.class_id   = c.class_id
                            and   a.acdm_id    = c.acdm_id
                            and   a.student_id = c.student_id)score_f,
                            (select sum(a.score_item)
                            from  dee6014 a
                            where a.start_use  = c.start_use
                            and   a.subject_id = 'O'
                            and   a.class_id   = c.class_id
                            and   a.acdm_id    = c.acdm_id
                            and   a.student_id = c.student_id)score_o,
                            (select sum(a.score_item)
                            from  dee6014 a
                            where a.start_use  = c.start_use
                            and   a.subject_id = 'I'
                            and   a.class_id   = c.class_id
                            and   a.acdm_id    = c.acdm_id
                            and   a.student_id = c.student_id)score_i,
                            (select sum(a.score_item)
                            from  dee6014 a
                            where a.start_use  = c.start_use
                            and   a.subject_id = 'C'
                            and   a.class_id   = c.class_id
                            and   a.acdm_id    = c.acdm_id
                            and   a.student_id = c.student_id)score_c,
                            (select sum(a.score_item)
                            from  dee6014 a
                            where a.start_use  = c.start_use
                            and   a.subject_id = 'L'
                            and   a.class_id   = c.class_id
                            and   a.acdm_id    = c.acdm_id
                            and   a.student_id = c.student_id)score_l
                    from dee3020 c,
                        dee0020 d  
                    where  
                        c.start_use  = :start_use       
                        and   c.acdm_id    = :acdm_id   
                        and   c.class_id   = :class_id              
                        and   c.room_id    = :room_id 
                        and   d.pname_id   = c.pname_id
                    order by c.student_name1,c.student_name2,c.personal_id  
            `;

            var binds = {
              start_use: req.query.YEAR_SEMESTER,
              acdm_id: req.query.ACDM_ID,
              class_id: req.query.CLASS_ID,
              room_id: req.query.ROOM_ID
            };

            var conn = await db.init();
            var result = await conn.execute(query, binds);

            return result; // Though return is here, finally will still run
        } catch (err) {
            throw err; // Let this bubble out after finally
        } finally {
            if (conn) { // If conn assignment worked, need to close
                try {
                    await conn.close();
                } catch (e) {
                    console.log(e); // want to know, but transaction already complete
                }
            }
        }
    }

}

export default report;

You might want to make similar changes to you db init code.

I was enable process.env.UV_THREADPOOL_SIZE = 800

UV_THREADPOOL_SIZE has a max size of 128. You don't need to go that high, but I'm not sure what Node.js does when you exceed the max. Start by matching your poolMax and adjust from there.

How to open pool connection in use more than 1

Now to your actual question... The reason you don't have more than one connection is that you're calling init which creates a pool and returns a connection from it. The next time you call init it creates a new pool and returns a connection from it.

Usually, what you want to do is create a connection pool when your app first starts, then just pull connections from the single pool. There's a built-in pool cache you can use to get the connections.

[xeleniumz]

Hi @dmcghan .
Thank you for your advice. After I struggle with this problem about 2 weeks.I was rewrite my code with async await following your example and remove unnecessary promise code. this is the change list that I already do for my node.js application and it's work !!

1. change process.env.UV_THREADPOOL_SIZE = 800 to process.env.UV_THREADPOOL_SIZE = 128
2. remove promise code that mix with async await and rewrite code use try cacth with async / await for check errors and use bind variables instead sql parameter in teamplate strings
3. When initialize the project I was create pool connection in the first time after then I use connection pool to connect to the oracle database.

Now my node.js application can handles many user request and this is how I solved this problem. @dmcghan thank you very much.