Tips for debugging/optimizing slow dashboard? (45 second response time)

[marckohlbrugge]

GoodJob's dashboard for one of my larger apps (with many background jobs) takes a long time to load. I just checked and it takes 45 seconds to load it right now.

I think it's worse depending on whether I'm filtering the jobs.

Performance of the background jobs seems fine. It's just that the dashboard seems to have very slow database queries.

• Is this a common issue?
• Are there ways to fix this?

[bensheldon]

I'm not surprised, and would need more details to really try to isolate it down 😄

I haven't done much optimization on the Dashboard Views; most of my focus has been on keeping job-execution performant.

• Is there a particular filter query that seems to be slow?
• To generate the filter options, GoodJob has to do some aggregate queries. Those could also possible be slow to query.
• I think generating the chart could also possibly be slow

Sorry, I realize that seems like a lot. But I mostly want to validate that yes, it could be slow, and also I'm very open to PRs if there are particular indexes that would help.

[marckohlbrugge]

Thanks, you not being surprised is actually reassuring. Suggests I might not be doing anything wrong 😂

The dashboard overall is slow. Even without filters. It could very well be the aggregate queries. I have about 3.5M (still preserved) jobs in the system.

I will reducing the preservation period to lower the job count. See if that helps.

PgHero suggested I added an index to finished_at on the good_jobs table. I noticed there already is one, but it has a WHERE. So perhaps it cannot be used in certain cases.

[marckohlbrugge]

I removed all jobs older than a day going from 3.5M to 300K (GoodJob.cleanup_preserved_jobs(older_than: 1.day) and the dashboard is considerably faster 👍

It would still be nice if the dashboard could handle higher counts, but for my case this will do.

If I run into this again, I'll consider adding some instrumentation to figure out which specific queries cause the slowdown and figure out a way to optimize them.

[stem]

I can confirm that this is also a pain for us.

We have between 0.5 and 1M job per day and we almost always have 30s timeouts on the dashboard, with or without filters.
We didn't had time to investigate it and went the same road as you : reduce the window for which we keep our jobs up until the point we completely remove job just after their execution.

I had plans to look into it at some point, but I didn't manage to find the time and can't see myself working on it for now 😞

[jesseduffield]

Also can confirm: I didn't have that many jobs but by only preserving completed jobs for 10 minutes it sped up the dashboard a lot

[ybart]

The queries that are taking times are the counts to populate queue and job_class filters. I tried to add an index for job_class, but after a certain number of rows it is useless, and it's hard to convince PostgreSQL to use it anyways (I had success once using random_page_access, but in my latest experiments, using the btree index on job_class is slower than seq scan).

So I guess we would need to pre-compute (or hide?) these values. I think it does not even really need to be accurate.

[hackling]

I have experienced this myself before.
I have purged all the finished jobs, and it would still be slow.

I found that logging in via PSQL and forcing a reindex on the table fixed all the load issues.

Might work for just me, but figured I would share anyway.