X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

node-oracledb 1.7.0 has a connection pool queue (Node.js add-on for Oracle Database)

Christopher Jones
Senior Principal Product Manager

Node-oracledb 1.7.0, the Node.js add-on for Oracle Database, is on NPM.

Top features: a new connection pool queue to make apps more resilient, and "Bind by position" syntax for PL/SQL Index-by array binds.

This release has a couple of interesting changes as well as some small bind fixes. A few reported build warnings with some compilers were also squashed.

Extended PL/SQL Index-by Array Bind Syntax

To start with, a followup PR from @doberkofler completes his PL/SQL Index-by array binding support project. In node-oracledb 1.7 he has added "bind by position" syntax to the already existing "bind by name" support. Thanks Dieter! The "bind by position" syntax looks like:

connection.execute(
"BEGIN mypkg.myinproc(:id, :vals); END;",
[
1234,
{ type: oracledb.NUMBER,
dir: oracledb.BIND_IN,
val: [1, 2, 23, 4, 10]
}
],
function (err) { . . . });

Personally I'd recommend using bind by name for clarity, but this PR makes the feature congruent with binding scalar values, which is always a good thing.

Documentation is at PL/SQL Collection Associative Array (Index-by) Bind Parameters.

New Transparent JavaScript Wrapper for Existing Classes

The other major change in 1.7 is a new JavaScript wrapper over the current node-oracledb C++ API implementation, courtesy of some community discussion and the direction that users seemed to have been heading in: creating similar wrappers. It was also the result of some 'above and beyond' overtime from Dan McGhan who did the project. This wrapper should be transparent to most users. It gives a framework that will make it easier to extend node-oracledb in a consistent way and also let developers who know JavaScript better than C++ contribute to node-oracledb.

New Connection Pool Queue Enabled by Default

The layer has let Dan add his first new user feature: a request queue for connection pooling. It is enabled by a new Boolean pool attribute queueRequests. If a pool.getConnection() request is made but there are no free connections (aka sessions) in the pool, the request will now be queued until an in-use connection is released back to the pool. At this time the first request in the queue will be dequeued, and the underlying C++ implementation of pool.getConnection() will be called to return the now available connection to the waiting requester.

A second new pool attribute queueTimeout uses setTimeout to automatically dequeue and return an error for any request that has been waiting in the queue too long. The default value is 60000 milliseconds, i.e. 60 seconds. In normal cases, when requests are dequeued because a connection does become available, the timer is stopped before the underlying C++ layer gets called to return the connection.

The pool queue is enabled by default. If it is turned off, you get pre-1.7 behavior. For example if more requests are concurrently thrown at an app than the poolMax value, then some of the pool.getConnection() calls would likely return an error ORA-24418: Cannot open further sessions. When enabled, the new queue nicely stops this error occurring and lets apps be more resilient.

The pool option attribute _enableStats turns on lightweight gathering of basic pool and queue statistics. It is false by default. If it is enabled, applications can output stats to the console by calling pool._logStats() whenever needed. I think it will be wise to monitor the queue statistics to make sure your pool configuration is suitable for the load. You don't want the queue to be an invisible bottle neck when too many pool.getConnection() requests end up in the queue for too long. Statistics and the API may change in future, so the attribute and method have an underscore prefix to indicate they are internal.

Connection Queue Example

To look at an example, I used ab to throw some load at an app based on examples/webapp.js I used a load concurrency of 25 parallel requests. The pool had a maximum of 20 sessions in its pool. The extra load was nicely handled by the connection queue without the application experiencing any connection failures.

I'd modified the app to check for a particular URL and dump statistics on request:

    . . .
var hs = http.createServer (
function(request, response)
{
var urlparts = request.url.split("/");
var arg = urlparts[1];
if (arg === 'stats') {
pool._logStats();
}
. . .

Here is snapshot of the output from _logStats() at one point during the test:

Pool statistics:
...total connection requests: 26624147
...total requests enqueued: 5821874
...total requests dequeued: 5821874
...total requests failed: 0
...total request timeouts: 0
...max queue length: 6
...sum of time in queue (milliseconds): 13920717
...min time in queue (milliseconds): 0
...max time in queue (milliseconds): 1506
...avg time in queue (milliseconds): 2
...pool connections in use: 12
...pool connections open: 20
Related pool attributes:
...queueRequests: true
...queueTimeout (milliseconds): 0
...poolMin: 10
...poolMax: 20
...poolIncrement: 10
...poolTimeout: 0
...stmtCacheSize: 30
Related environment variables:
...process.env.UV_THREADPOOL_SIZE: undefined

The connection pool was semi-arbitrarily configured for testing. It started out with 10 sessions open (poolMin) and as soon as they were in use, the pool would have grown by another 10 sessions (poolIncrement) to the maximum of 20 (poolMax).

What the stats show is that not all pool.getConnection() requests could get a pooled connection immediately. About 20% of requests ended up waiting in the queue. The connection pool poolMax is smaller than optimal for this load.

The queue was never large; it never had more than 6 requests in it. This is within expectations since there are at least 5 more concurrent requests at a time than there are connections available in the pool.

If this were a real app, I might decide to increase poolMax so no pool.getConnection() call ever waited. (I might also want to set poolTimeout so that when the pool was quiet, it could shrink, freeing up DB resources.) However the average wait time of 2 milliseconds is small. If I don't have DB resources to handle the extra sessions from a bigger pool, I might decide that a 2 millisecond wait is OK and that the pool size is fine.

At least one connection spent 1.5 seconds in the queue. Since I know my test infrastructure I'm guessing this was when the pool ramped up in size and my small, loaded DB took some time to create the second set of 10 sessions. Maybe I should experiment with a smaller poolIncrement or bigger poolMin?

Another important variable shown in the stats is UV_THREADPOOL_SIZE. I'd not set it so there were the default four worker threads in the Node process. Blindly increasing poolMax may not always help throughput. If DB operations take some time, you might find all threads get blocked waiting for their respective DB response. Increasing UV_THREADPOOL_SIZE may help improve application throughput.

The best settings for pool configuration, UV_THREADPOOL_SIZE, and any DRCP pool size will depend on your application and environment.

Connection Pool Queue Statistics

The table below shows the node-oracledb 1.7 pool statistics descriptions. These stats and the APIs to enable and log them may change in future versions of node-oracledb. I look forward to getting some PRs, for example to add a standard logging capability which the stats generation can be part of.

Connection Pool MetricDescription
total connection requests

Number of pool.getConnection() calls made for this pool

total requests enqueued

Number of connections that couldn't be satisfied because every session in the the pool was already being used, and so they had to be queued waiting for a session to be returned to the pool

total requests dequeued

Number of connection requests that were removed from the queue when a free connection has become available. This is triggered when the app has finished with a connection and calls release() to return it to the queue

total requests failed

Number of connection calls that invoked the underlying C++ pool.getConnection() callback with an error state. Does not include queue request timeout errors.

total request timeouts

Number of connection requests that were waiting in the queue but exceeded the queueTimeout setting. The timeout is triggered with a JavaScript setTimeout call

max queue length

Maximum number of connection requests that were ever waiting at one time

sum of time in queue

Total sum of time that connection requests have been waiting in the queue

min time in queue

Smallest amount of time that any request waited in the queue

max time in queue

Longest amount of time that any request waited in the queue

avg time in queue

Derived from the sum of time value divided by the number of requests enqueued

pool connections in use

A metric returned by the underlying Oracle C client session pool implementation. It is the number of currently active connections in the connection pool

pool connections open

Also returned by the underlying library. It shows the number of currently open sessions in the underlying connection pool

Note that the sum of time in queue, the min time in queue and the max time in queue values are calculated when queued requests are removed from the queue, so they don't record the amount of time for requests still waiting in the queue.

Resources

Issues and questions about node-oracledb can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Join the discussion

Comments ( 2 )
  • guest Tuesday, March 1, 2016

    Hi - I'm Ash and I work for a company called Capita in the Customer Management division.
    Our team build concept real-time applications for dozens of international blue-chip clients, and our approach consists of creating modular applications with Express JS and node-oracledb.

    We take advantage of the node-oracledb's pool functionality and Oracle's DRCP. This approach has proven to be super reliable for us, although to echo the node-oracledb documentation - please don't forget to release your connections when you're done with them (or if you catch an error!) - or you will run into problems as your app grows.

    We've written a database wrapper which we're happy to share - just give me a shout and I'll send it to you.

    Prior to node-oracledb, we were using the joe-ferner node-oracle module. We decided to switch to node-oracledb (pre-release version 0.4.2) when the node-oracle module was abandoned.

    The team at Oracle have been truly outstanding - and Chris Jones regularly keeps in touch with us personally, letting us know when node-oracledb has been updated, while providing world-class support when we need it.

    Every release brings out new features, and the module just gets better and better.
    We really enjoy working with the team at Oracle, and the node-oracledb module.

    If any of the guys at Oracle are in the UK (Leeds area), please let us know and we'll take you out for lunch!

  • Christopher Jones Thursday, March 3, 2016

    Thank you!

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.