Improving the performance of jobrunr_jobs_stats
view
#834
MikeyBower93
started this conversation in
Ideas
Replies: 2 comments 2 replies
-
Hi @MikeyBower93 , yes - we're aware of this and already tried to tune this query as much as possible (indices, ... ). Two options that I can give currently:
An item exists on our JobRunr Pro backlog to improve this using counters (it has not yet been done as I'm a bit scared of having data that is out-of-sync). This improvement is planned for early next year. |
Beta Was this translation helpful? Give feedback.
1 reply
-
Ronald, is it safe to reduce rateLimit of this query from 1 request per second to 1 request per 10 seconds? |
Beta Was this translation helpful? Give feedback.
1 reply
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hey team!
I've been inspecting some of the JobRunr queries on our database to analyse where performance could be improved and noticed this query
Looking into this, this query is executing frequently because we added a
JobStatsChangeListener
to our code so we could report job successes/failures to Datadog and create graphs to explain our performance. However I can see the query for this is on average 4 times slower than the rest of JobRunr's database queries. On our database its performing with a P50 latency of 91ms, and P99 latency of 450ms, which comparative to other queries seems quite slow.Looking into this, it seems
jobrunr_jobs_stats
is a view that runs aggregations on thejobrunr_jobs
table, and whilst it does use indexes (index only scans, to get this data, my understanding is it still has to some quite heavy performing to get the counts as it needs to scan all the leaf nodes in the index, which for deleted jobs is often a lot and is not very selective.Here is the execution plan
We can see that its doing a index only scan, but still has a cost of 81,000 which is seemingly quite high, due to nature of a count on a non selective index condition.
I wondered if some considerations could be made to improving this? Perhaps you could maintain a table that stores these raw numbers that isn't a view, and use some triggers to increment/decrement when particular events happen?
For context, this runs a service that has processed 40 million jobs and counting.
Thanks!
Mike
Beta Was this translation helpful? Give feedback.
All reactions