Performance issues when setting scheduled/retryable -> available

Special Sections Oban
,
1

Hi!

We’ve been doing some analyzing of the oban queries hitting our postgres database. We’ve found that every aproximately every 5s an UPDATE is done to set ‘scheduled’/‘retryable’ jobs to ‘available’. However, the query:

  UPDATE
  "public"."oban_jobs" AS o0
SET
  "state" = $1
FROM (
  SELECT
    so0."id" AS "id",
    so0."state" AS "state"
  FROM
    "public"."oban_jobs" AS so0
  WHERE
    (so0."state" IN ($4,
        $5))
    AND (NOT (so0."queue" IS NULL))
    AND (so0."scheduled_at" <= $2)
  LIMIT
    $3) AS s1
WHERE
  (o0."id" = s1."id") RETURNING o0."id",
  o0."queue",
  s1."state"

takes, on avarage, 4 seconds to execute. Somehow, no index seems to be used in the subquery (which seems to be the culprint here). However, just executing the subquery successfully uses oban_jobs_state_queue_priority_scheduled_at_id_index.

We’re not that time dependant that a few seconds really matters for us, but we were curious to see if this is a know issue, or something that we’re doing wrong.

Screenshot 2024-08-21 at 15.47.40
Screenshot 2024-08-21 at 15.47.402500×576 67 KB

Screenshot 2024-08-21 at 15.47.21
Screenshot 2024-08-21 at 15.47.211200×530 30.7 KB

Thank you!

1 Like
2

Hello!

  1. Would you provide some info about the environment?
  2. Which version of Oban are you using?
  3. How many jobs are in the db?
  4. Are you using many scheduled and retryable jobs?
  5. What’s the size of your db?

It shouldn’t take 4secs. It would help to know more.

3

Hi! Sure.

  1. We’re running everything in GCP, so PostgreSQL 15.7 in Cloud SQL
vCPUs
    2 
Memory
    7.5 GB 
SSD storage
    20 GB

On the elixir side we’re running Cloud Run, elixir 1.17, erlang 27.0.1,

  1. Oban 2.17.

scheduled: 7_692_357 
executing: 6_815
retryable: 25
completed: 3_809_248
discarded: 20
  1. Yes, especially the scheduled jobs table is big
  2. Around 12 Gb, Oban things is the only thing we store there. This is the size of our indices.
oban_jobs_pkey: 604 MB
oban_jobs_state_queue_priority_scheduled_at_id_index: 1591 MB
oban_jobs_args_index: 5717 MB
oban_jobs_meta_index: 40 MB

Thank you!

4

This helps!

One last question and two suggestions:

Do you always have this amount of scheduled jobs?

Looks like the args index is really large.

Two things can help:

  1. Consider putting less in there.
  2. You may want to run the Reindexer: Oban.Plugins.Reindexer — Oban v2.17.0
2 Likes
5

Yes. We will have a lot of scheduled jobs.

Yeah. It seems the reindexer might work. We seem to be able to shrink the index to around 1 GB. Does that sound more reasonable?

6

The reindexer reduced the size of our index to around 1 GB. But the performance issues remain. We currently have a rentention of one week. Is it recommended to have a low retention period? Or would that not impact the performance in any meaningful way?

7

About 4 hours after reindexing, the query planner seem to have changed. The query is now using the right index and the queries are visibly faster. Thank you!

Edit: I think I’ll start a new thread for this specific problem.

8

Sorry that it took a bit to respond! We were at ElxirConf in Orlando. :confetti_ball:

A lower retention period can really help performance. It comes down to how many jobs you’re running, more than the total time.

You are most welcome. Great to hear that it worked, that’s marvelous!

New indexes won’t impact the planner until the next vacuum analyze. Ecto also caches prepared statements, and those won’t change until the connections that own them are closed (or the app restarts).

Watching for this^.

2 Likes