Connection Request Queuing with the Node.js Driver

By September 29, 2015 Uncategorized

I recently made an assumption about how requests for connections were handled by the Node.js driver. You know what they say about assumptions, right? ๐Ÿ˜€ In the Node.js driver, developers can create connection pools. A connection pool has settings, such as the minimum and maximum number of connections, and methods that allow you to get connections and release them.

Here’s an overly simplified example:

I assumed that when I invoked the getConnection method of a pool object (line 16 above) that my callback function wouldn’t be invoked until the connection was obtained from the pool. Boy was I wrong! The current behavior of the driver is to attempt to get a connection. If a connection is obtained from the pool then the callback function is invoked and passed the connection object as one would expect. However, if the number of connections in use is equal to the poolMax then the callback function is invoked and passed an error object instead with an error message that reads “ORA-24418: Cannot open further sessions”.

Why is this the default behavior? Well, the only way for the driver to queue the request is to lock the thread and wait. While that would not lock the main event loop, just one of the background threads used for IO, it could still create a serious point of contention. With just 2 long running queries you could lock all (the default is 4) of the background threads – 2 executing and 2 waiting. If you haven’t yet read it, check out this post on how those background threads work: http://www.future-processing.pl/blog/on-problems-with-threads-in-node-js/

Currently, it’s up to developers using the driver to implement some kind of queuing in their applications to prevent these errors from occurring. There is some discussion going on right now as to whether or not the driver should offer some options in this area. What do you think? Should the driver allow developers to specify if they want it to lock threads and queue requests? Checkout these related discussions and leave some comments for the folks doing the development – your opinion counts!

Connection Request Queuing

While exploring this issue I went ahead and updated Orawrap to handle connection request queuing. Now with Orawrap, when a pool is created it is assigned a queue object using the async library’s queue implementation. The queue concurrency is derived from the poolMax setting and everything else is handled automatically. I’ve had great success with the limited load testing I’ve done but I’d love it if some others could kick the tires and provide some feedback.

I’ve been using Apache JMeter for my load testing. Here’s how things worked before connection request queueing with a light load (simulating 5 users) and the poolMax set to 10:
light-load-no-queuing

As you can see via the status column there are a lot of requests that failed due to “Error: ORA-24418: Cannot open further sessions.” errors.

Now lets have a look at what we get with the updated version of Orawrap:
light-load-with-queuing

All green! I’ve tested with much larger user pools on modest hardware and had consistent results.

UV_THREADPOOL_SIZE

One other thing to keep in mind… If your poolMax is going to be over 4 then you should consider increasing the size of the UV_THREADPOOL_SIZE parameter. The right setting there would depend on a number of factors including the load you’re expecting, the available resources (notably the number of cores), etc. Checkout the article mentioned above on background threads to see how this parameter can be set.

Here are the results of a different test where the query takes 10 seconds to complete (simulated via DBMS_LOCK.SLEEP). The delay helps exaggerate an issue that you may not have noticed in the first examples:
20-users-with-default-uv-threadpool-size

Do you see how all of the requests started at the same time but finish in batches of 4 about 10 seconds apart? This is because the default UV_THREADPOOL_SIZE of 4 isn’t allowing me to take full advantage of all 10 connections in my pool at the same time.

Here’s what we see after I increase the UV_THREADPOOL_SIZE to 10 before starting the app:
20-users-with-uv-threadpool-size-10

As you can see now we are processing requests in batches of 10. Much better!

In general, I’d recommend setting the UV_THREADPOOL_SIZE slightly higher than the poolMax so that you’re able to fully leverage the connections in the connection pool while still allowing some additional threads to handle other IO. But again, you’ll need to consider your system to find the magic number.

6 Comments

  • Francis Kim says:

    Thanks for the write up, I’m sure this newly gained knowledge will have some use in the future ๐Ÿ™‚

  • Kirill says:

    Hi Dan. I tried to switch from the oracledb to your module and then test it with more or less real loading in jMeter like 100 users requesting 10 times with one second the url with the pool and the results were the same as with oracledb about 80 – 90% of requests failed with “ORA-24418: Cannot open further sessions.” on OracleXE and maxPool set to 10. Is there anything that I’m missing?

    • danmcghan says:

      Hi Kirill,

      Would you mind sending me the code for you tests to review?

      Regards,
      Dan

      • Kirill says:

        Hi Dan,

        Thank you for your help. Simplified code example can be found here: https://github.com/kirreal/oraclejs/blob/master/oracle.js.

        • danmcghan says:

          Hi Kirill,

          Thanks for your example. The reason your solution isn’t working is because it’s not really using Orawrap correctly. For example, you call getConnection right off the pool. With Orawrap, you should call the getConnection off the base class (Orawrap). Really, that’s the “secret sauce” for the connection request queuing. We can’t call getConnect off the base class because the underlying implementation (written in C) doesn’t queue. Orawrap’s getConnection method is what does the queueing.

          Orawrap also has a releaseConnection method which works with the Queue so make sure to use that too.

          Simply put, Orawrap is a wrapper! ๐Ÿ™‚ You need to use its methods to get the desired effect… Frankly, I see this as my fault due to poor documentation. Sorry for the confusion! Let me know if you need more details.

          Regards,
          Dan

          • Kirill says:

            Hi Dan,

            Thank you so much for you help and for your plugin, everything works fine for now.

            P.S. Developers are often late with the documentation.

Leave a Reply