Node.js Oracle Database Connection Pool Size

Question

I implemented a connection pool (poolMin=poolMax=10) with node-oracledb and i saw a difference of up to 100 times especially in case of few users like 10. Really impressive. I also increased UV_THREADPOOL_SIZE like 4 + poolMax. At this point I could not understand somethings.

process.env.UV_THREADPOOL_SIZE = 4 + config.pool.poolMax // Default + Max

NodeJs works as Single Thread (with additional 4 threads those none of them are used for network I/O). So when i use a pool with 10 connections, can Single Thread use all of these connections? Or isn't it Single Thread with these settings anymore? Because i added 10 more to UV_THREADPOOL_SIZE. I would be grateful to anyone who explained this matter.

Btw, I wonder if using a fixed number pool like 10 would cause a problem in case of too many users? For example, if the number of instant users is 500, we can reach 5000 instant users on certain days of the year. Do I need to make a special setting (e.g. pool size 100) for those days or will the default be enough?

Thanks in advace.

question from:https://stackoverflow.com/questions/65878589/node-js-oracle-database-connection-pool-size

Answer 1

When you do something like connection.execute(), that work will handled by a worker thread until the call completes. And each underlaying Oracle connection can only ever do one 'thing' (like execute, or fetch LOB data) at a time. This is why you want the number of worker threads to be at least as big as the number of connections in the connection pool. This allows connections to do their thing without blocking any other connection. And this is why Promise.all() using a single connection is bad - because each of the 'parallel' operations can use a thread but will be blocked waiting for prior work on the connection to complete. Work on a single connection can only ever be serial, not parallel. Using Promise.all() where each unit of work has its own connection is OK, but is rare.

Check the node-oracledb documentation Connections, Threads, and Parallelism and Connection Pool Sizing.

Node-oracledb will queue connection pool requests (e.g. pool.getConnection()) if every connection in the pool is already in use. This provides some resiliency under connection spikes. There are some limits to help real storms: queueMax and queueTimeout. Yes, at peak periods you might need to increase the poolMax value. You can check the pool statistics to see pool behavior. You don't want to make the pool too big - see the doc.

Side note: process.env.UV_THREADPOOL_SIZE doesn't have an effect in Node.js on Windows; the UV_THREADPOOL_SIZE variable must be set before Node.js is started.