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:

Image

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)

Most upvoted comments

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

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'river_job';
Image
SELECT pg_size_pretty(pg_total_relation_size('river_job')) as total_size, pg_size_pretty(pg_relation_size('river_job')) as table_size, pg_size_pretty(pg_indexes_size('river_job')) as index_size;
Image

Experiments

Using SKIP LOCKED

Manually running the same query but with FOR UPDATE SKIP LOCKED produces this plan and just takes ~25ms:

Nested Loop  (cost=98244.26..127501.23 rows=3960 width=1048) (actual time=24.210..24.317 rows=2 loops=1)
  CTE jobs_to_schedule
    ->  Limit  (cost=97254.67..97255.92 rows=100 width=31) (actual time=23.818..23.854 rows=2 loops=1)
          ->  LockRows  (cost=97254.67..97790.41 rows=42859 width=31) (actual time=23.817..23.852 rows=2 loops=1)
                ->  Sort  (cost=97254.67..97361.82 rows=42859 width=31) (actual time=23.680..23.682 rows=10 loops=1)
                      Sort Key: river_job_1.priority, river_job_1.scheduled_at, river_job_1.id
                      Sort Method: quicksort  Memory: 25kB
                      ->  Bitmap Heap Scan on river_job river_job_1  (cost=2540.05..95616.63 rows=42859 width=31) (actual time=21.255..23.665 rows=10 loops=1)
"                            Recheck Cond: ((state = ANY ('{retryable,scheduled}'::river_job_state[])) AND (priority >= 0) AND (scheduled_at <= (now() + '00:00:05'::interval)))"
                            Heap Blocks: exact=2036
                            ->  Bitmap Index Scan on river_job_prioritized_fetching_index  (cost=0.00..2529.34 rows=42859 width=0) (actual time=20.908..20.908 rows=2338 loops=1)
"                                  Index Cond: ((state = ANY ('{retryable,scheduled}'::river_job_state[])) AND (priority >= 0) AND (scheduled_at <= (now() + '00:00:05'::interval)))"
  CTE jobs_with_rownum
    ->  WindowAgg  (cost=5.32..7.82 rows=100 width=67) (actual time=23.876..23.881 rows=2 loops=1)
          ->  Sort  (cost=5.32..5.57 rows=100 width=59) (actual time=23.868..23.869 rows=2 loops=1)
                Sort Key: jobs_to_schedule.unique_key, jobs_to_schedule.priority, jobs_to_schedule.scheduled_at, jobs_to_schedule.id
                Sort Method: quicksort  Memory: 25kB
                ->  CTE Scan on jobs_to_schedule  (cost=0.00..2.00 rows=100 width=59) (actual time=23.822..23.857 rows=2 loops=1)
  CTE updated_jobs
    ->  Update on river_job river_job_2  (cost=0.71..980.09 rows=3960 width=201) (actual time=24.199..24.296 rows=2 loops=1)
          ->  Merge Left Join  (cost=0.71..980.09 rows=3960 width=201) (actual time=23.912..23.929 rows=2 loops=1)
                Merge Cond: (job.unique_key = river_job_3.unique_key)
                ->  Nested Loop  (cost=0.43..846.50 rows=100 width=239) (actual time=23.906..23.921 rows=2 loops=1)
                      ->  CTE Scan on jobs_with_rownum job  (cost=0.00..2.00 rows=100 width=120) (actual time=23.883..23.891 rows=2 loops=1)
                      ->  Index Scan using river_job_pkey on river_job river_job_2  (cost=0.43..8.45 rows=1 width=135) (actual time=0.012..0.012 rows=1 loops=2)
                            Index Cond: (id = job.id)
                ->  Materialize  (cost=0.28..14.98 rows=178 width=71) (never executed)
                      ->  Merge Join  (cost=0.28..14.54 rows=178 width=71) (never executed)
                            Merge Cond: (river_job_3.unique_key = jobs_with_rownum.unique_key)
                            Join Filter: (river_job_3.id <> jobs_with_rownum.id)
                            ->  Index Scan using river_job_unique_idx on river_job river_job_3  (cost=0.28..9.40 rows=8 width=15) (never executed)
                                  Index Cond: (unique_key IS NOT NULL)
"                                  Filter: ((unique_states IS NOT NULL) AND (CASE state WHEN 'available'::river_job_state THEN get_bit(unique_states, 7) WHEN 'cancelled'::river_job_state THEN get_bit(unique_states, 6) WHEN 'completed'::river_job_state THEN get_bit(unique_states, 5) WHEN 'discarded'::river_job_state THEN get_bit(unique_states, 4) WHEN 'pending'::river_job_state THEN get_bit(unique_states, 3) WHEN 'retryable'::river_job_state THEN get_bit(unique_states, 2) WHEN 'running'::river_job_state THEN get_bit(unique_states, 1) WHEN 'scheduled'::river_job_state THEN get_bit(unique_states, 0) ELSE 0 END = 1))"
                            ->  Materialize  (cost=0.00..2.25 rows=100 width=104) (never executed)
                                  ->  CTE Scan on jobs_with_rownum  (cost=0.00..2.00 rows=100 width=104) (never executed)
  ->  CTE Scan on updated_jobs  (cost=0.00..79.20 rows=3960 width=9) (actual time=24.201..24.297 rows=2 loops=1)
  ->  Index Scan using river_job_pkey on river_job  (cost=0.43..7.37 rows=1 width=1047) (actual time=0.004..0.004 rows=1 loops=2)
        Index Cond: (id = updated_jobs.id)
Planning Time: 1.268 ms
Execution Time: 24.455 ms

Different Index

I’ve manually added the following index to fully tailor the index:

CREATE INDEX river_job_scheduler_index ON river_job (state, priority, scheduled_at, id) WHERE state IN ('retryable', 'scheduled') AND priority >= 0;

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 👍