High Database CPU when processing backlog about pg-boss HOT 9 OPEN

How many records were created in the job table when the util was high?

from pg-boss.

Hi, @timgit recently the new producer is constantly sending approximately 30k jobs per hour but the current workers can not keep up and only be able to process half of it per hour. Totaling at 80% CPU there were approximately 1M jobs ~500k completed ~500k created

If I scale the worker number to be able to consume more messages the RDS CPU is getting consumed much faster (which makes sense) but with this workload is getting depleted immediately if I double the containers from 4 to 8

Currently, we have a cron-job that cancels all the jobs when the CPU is getting too high and we will replay them at a later date.

Once the jobs get canceled the CPU drops significantly.

We are running on a db.tg4.small RDS instance and we plan to upgrade to a bigger instance to keep up with the job processing, but wondering if this issue might be coming back even with a bigger instance

from pg-boss.

@timgit After further investigation it seems that the issue is caused by the sorting on the nextJob
https://github.com/timgit/pg-boss/blob/master/src/plans.js#L379

Since there is no compound index on these columns when the table gets big, Postgres would start doing a disk merge sorting, causing high CPU usage as the table gets bigger.

I'm wondering if adding an index on the sorted columns could help speeding up things and reduce the load on Postgres when sorting

from pg-boss.

@timgit we have upgraded the RDS instance to a db.t4g.xlarge and unfortunately, this issue is still happening, is there anything we can do maybe as mentioned above, adding an index to the sort columns to reduce the disk sorting operation?

from pg-boss.

I have tried several times to build the best index for job fetching, but apparently this work will be ongoing. 😄 If you're up for it, can you experiment with your job table to find out what index best helps your use case?

from pg-boss.

hey @fenos . Did you manage to find a solution for this? We are kinda stuck in this situation and our DB CPU user keeps on increasing.

from pg-boss.

Yes, the "solution" is to have the least active jobs as possible as well as setting:

• teamSize: 200 (this option is very very important as it will reduce the amount of queries issued to the database)
• teamConcurrency: depends on your resource this will allow to process more jobs concurrently

from pg-boss.

@fenos had you tried to add an index ? if so, what was it and the result ?

from pg-boss.

I'm experiencing the same issue. CPU usage is periodically growing and growing. I have only one worker with no concurrency. Very low traffic (one job every N minutes). Job execution time is roughly a couple of seconds.
What I noticed, however, is that after restarting the worker CPU usage dropped to almost 0%. It's still growing back to ~75%.

from pg-boss.