Nodejs pm2 cluster shared pool

[shakogegia]

Hi, I have nodejs server clustered with pm2 (8 instances), I want to share connection pool with instances so each individual instant won't create indipendent pool...
How can i manage these? what do i need for this?

[dmcghan]

@shakogegia That sounds tricky. You could setup some kind of "DB instance" which all of the regular instances use to communicate with the database, but that sounds like overkill.

Why not just divide the total number of pooled connections you want by the number of Node.js instances you have? So if you want 80 connections max and you have 8 instances, set each instance up so that it creates a pool with 10 connections max.

Would that work?

[shakogegia]

@dmcghan Yes it would work, but I have 3 server each clustered with pm2 and summary i have 24 instances, and i want to have connection pool be shared on every instance

[dmcghan]

That's just more math, right? ;)

I see where you're going with this, I do. Especially as the number of servers continues to increase...

But the concept of "sharing" between the Node.js instance implies some kind of shared state. This increases complexity and necessitates some kind of independent broker/queue which would result in a single point of failure, no?

You might want to look into database resident connection pooling (DRCP). This puts the responsibility of the broker on Oracle Database. Here's a link to a slide I recently saw that illustrates the concept really well:
https://twitter.com/bullened/status/805830162721947648

[dmcghan]

@shakogegia I just tweeted a pointer back to this thread. I included some slides from a talk @cjbj did at #oow that may help get you going:
https://twitter.com/dmcghan/status/811995029166231552

[shakogegia]

@dmcghan Thanks for reply, DRCP Sounds interesting, I've not heard about it before, I will try to implement it. And at least i'll divide connection for instances

I was testing connection pool and what I saw was, that on heavy load nodejs opens connections on all instances, and open connections are closed after next connect, right? on low load (for example night), there is working practically just 1-3 instance of 24, and connections opened by them won't close before to tomorrow on heavy load when all instant starts working, right?

Sorry for my bad english...

[shakogegia]

For now my app opens and closes connection to db on every signle request, but i am working to implement connection pooling which is extremely faster on high concurent connections as apache benchmark shows to me...

P.S @dmcghan I read your blog and I can say that its very helpful for begginers and not only... thanks for your blog too...

[dmcghan]

@shakogegia 👍

Don't divide too much. For example, you don't want just 2 or 3 connections available to a single instance as that could cause issues with complex transactions. See #395 for details.

Be sure to set your UV_THREADPOOL_SIZE to accommodate the number of connections in the pool. Also, use autoCommit on the last statement of a transaction to avoid an unnecessary round trip at the end of a transaction with commit.

on low load (for example night), there is working practically just 1-3 instance of 24, and connections opened by them won't close before to tomorrow on heavy load when all instant starts working, right?

The behavior of the pool will vary depending on the parameters used when creating it. When working with a pool, connection.close() can be a little misleading as the connection is simply released back to the pool so it can be used for another transaction.

The poolMin, poolMax, and poolTimeout settings, combined with actually pool usage, will generally determine how many open connections there are in a pool. There may be some nuances there that @cjbj can shine some light on.

For now my app opens and closes connection to db on every signle request, but i am working to implement connection pooling which is extremely faster on high concurent connections as apache benchmark shows to me...

Yes, connection pooling will result in much better performance!

[shakogegia]

@dmcghan thanks for reply.

I'll try to divide connections per instance maybe poolMax: 10-15 and poolMin: 1.
UV_THREADPOOL_SIZE default value is 4, and i think its enough for me because of type of my app, there is just inserts, updates and deletes. In this project i always use autoCommit: true except one method -registration. this is the "biggest" method on this project, It does multiple checks on db and i use transaction only in this method.

I'll publish code with connection pooling and see what happens and how open connections would be in real...

[dmcghan]

@shakogegia We recommend setting the UV_THREADPOOL_SIZE to the same value as your poolMax as a starting point for tuning. If your poolMax is 10, try setting the UV_TREADPOOL_SIZE to 10. Then try the default. Then try 15 or other values. Do some stress testing and let us know what the optimal setting was.

this is the "biggest" method on this project, It does multiple checks on db and i use transaction only in this method.

The transaction is fine, but with the last execute statement of the transaction, use autoCommit: true to avoid an unnecessary round trip and decrease the likelihood of a deadlock.

[cjbj]

@shakogegia

I was testing connection pool and what I saw was, that on heavy load nodejs opens connections on all instances, and open connections are closed after next connect, right? on low load (for example night), there is working practically just 1-3 instance of 24, and connections opened by them won't close before to tomorrow on heavy load when all instant starts working, right?

Yes, they'll be closed only on node-oracledb pool access. Do you have issues with the server processes (used by connections) being open on the database? Are you sure? Does it matter if they are left open overnight? At the bottom line, you will need to have enough connections opened to the database to do the work of your peak load (unless you throttle, e.g with a network solution, or with node-oracledb's pool queue.) There are solutions such as pinging the app to trigger a simple DB request each couple of hours at off-peak times.

DRCP is great if you have DB server memory issues. (See the doc for the other pre-requisites). There might be other edge cases where DRCP will help, e.g. by having pre-spawned server process on the DB. Otherwise not sharing connections (and not having the overhead of another pool) is always going to be faster.

To make pooling (node-oracledb & DRCP) effective, apps need 'think time' between doing DB work. They will also need to release connections back to the node-oracledb connection pool when not need.

You will want to increase UV_THREADPOOL_SIZE when you increase poolMax, as @dmcghan mentioned, otherwise threads may deadlock.

[cjbj]

@shakogegia PS for best high availability, use node-oracledb 1.12, which has the pool pinging feature

[shakogegia]

@cjbj I'll consider

[cjbj]

Closing.

[calebeaires]

Hi. Although this issue is closed and is fully discussed I am on a big problem on how to make it work. I use TypeORM to make OrackeDB connection, may you help me to find the best setup to keep connection on when using PM2 cluster

import { createConnection } from 'typeorm';

const connection = createConnection({
                        name,
                        type: credentials.client,
                        host: credentials.host,
                        port: Number(credentials.port),
                        username: credentials.user,
                        password: credentials.password,
                        database: credentials.database,
                        sid: credentials.sid,
                        logger: true,
                        extra: {
                            poolMax: 4,
                            poolMin: 0,
                            poolIncrement: 1,
                            poolTimeout: 0,
                        }
})

On TypeORM, every extra properties is used to make custom settings connection, those that is original to the dialet.

Ps.: I have tried to make that twitt suggestion, but had no success on it.

[cjbj]

@calebeaires I suggest you open a new issue. Give the error you are getting and (redacted) samples of values you are passing. If the issue is possibly your connection string, put some extra console.logs() in oracledb/lib/oracledb.js and check what is being passed. From experience with other driver wrappers, I would guess you don't need both database and sid.

[calebeaires]

I track the error from PM2 log folder like you have said. It seems that when we active PM2 cluster settings, the export ...oracle folder lib setup get lost for some reason.

What I did to make it work: i have setup the ldconfig config suggested here

For notes:

1. OracleDB connection stop with PM2 cluster settings.
2. How to fix: make sure your app will find the oracle lib folder.
3. Track PM2 on folder: ~/.pm2/logs/main-out-0.log
4. Use ldconfig setup
5. The error (im my case):

Error: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help
Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html
You must have 64-bit Oracle client libraries in LD_LIBRARY_PATH, or configured with ldconfig.
If you do not have Oracle Database on this computer, then install the Instant Client Basic or Basic Light package from 
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html