river: Bad JobSchedule query performance with lots of scheduled jobs
Hey there, thanks for the project in general! Really nice and lightweight to use 👍
We’re currently using it in production and running ~45k jobs per day, so not too big. However, all of our jobs are basically scheduled (we get an external action with what to execute and it gets distributed over the day).
Looking into DB monitoring we actually see that the JobSchedule query has very bad performance and causes significant load on our DB. Given that the scheduler runs every 5s an average latency of 2.5s per call is concerning us. Is that something you could look into?
Here’s a screenshot from AWS RDS performance insights:
Let me know if I can provide any additional details.
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 26 (12 by maintainers)
I did a bit of investigation to see if I can find anything; I’m by no means a DB expert so what I was looking at might also be irrelevant.
Lock contention
I was looking at this: https://wiki.postgresql.org/wiki/Lock_Monitoring to run some queries.
pg_locks: No results for that query. pg_stat_activity: Also did not find any results (I ran it a couple of times over a 5s interval).
Table statistics
Experiments
Using SKIP LOCKED
Manually running the same query but with
FOR UPDATE SKIP LOCKEDproduces this plan and just takes ~25ms:Different Index
I’ve manually added the following index to fully tailor the index:
The problem remains the same that the lock takes a very long time.
TL;DR
The only thing that really seems to “fix” the issue is to use
SKIP LOCKED. I’m not sure if this will have any other side effects but I’m not sure what else to try. Since we plan to 3-4x the number of scheduled jobs in the coming weeks this is definitely a big concern for us right now.Thanks for looking into that, too, @brandur. I’ll have a look tomorrow during peak times 👍