BullMQ Sandboxed Processors and Database Connection

[angelocala94]

Hi,

I've implemented an application using BullMQ and its sandboxed processors. My job setup is as follows:

• Each job involves interacting with an SQL database using Prisma.
• The same query is executed many times per second in individual jobs.

Given my setup, I am concerned about the number of database connections being opened due to the potential creation of new Prisma Clients in each child process.

Questions:

1. Process Reuse: Are child processes reused for handling multiple jobs, or is a new child process created for each job?
2. Database Connection Persistence: If processes are reused, does this imply that the same database connection (managed by Prisma) is also reused across multiple jobs within the same child process?

I want to ensure that I don't exceed the connection limits of my db connection pool.
Any guidance or best practices on this will be much appreciated.

Thanks!

[manast]

The processes are reused, but your case seems to be pretty much IO-bound so I would try to increase the concurrency a lot instead of using sandboxed processors, as you would get more output and can better control the connections to Postgres

[angelocala94]

Yes, it's a kind of performance tracker for advertising. What the various jobs mostly do is call some APIs, and insert/modify data using Prisma with Postgres. Some jobs could last for minutes due to the volume of data to update in the database.
The most frequent job is the one that saves events that occurred on the page (every click, and other custom events), so this gets executed hundreds of thousands of times a day.

So, you suggest disabling the sandboxed processors and increasing the concurrency? When is it better to use sandboxed processors usually, for CPU-intensive operations that can become blocking for the CPU? For everything else, do you recommend not using them?

Regarding the concurrency values: What could be an appropriate value? We have a fairly large server, 256GB RAM / Xeon 6 core 12 thread.

Many thanks.

[manast]

If you want to exploit the cores in your machine then the best is to use pm2 and spawn several workers, and then crank up the concurrency per worker. In your case if you have different types of jobs, ones that are very fast and others that are very slow, I recommend you to use different queues, so that the slow ones do not clog the fastest ones. Here you can read more about parallelism and concurrency: https://docs.bullmq.io/guide/parallelism-and-concurrency

[angelocala94]

Perfect, thanks @manast!