Optimize performance relevant backend methods

[mojolicious]

Optimize performance relevant backend methods

[kraih]

We already have a benchmark script that can be adapted for this. Filling the queue with a million jobs is pretty slow though.

[kraih]

I've brought back the GIN index after benchmarking a bit. There is a huge performance gain for job_info and maybe a tiny one for repair. dcc95da

[kraih]

It appears our main bottleneck now is one query in repair.

  # Jobs with missing parents (can't be retried)
  $db->query(
    "update minion_jobs as j
     set finished = now(), result = to_json('Parent went away'::text),
       state = 'failed'
     where parents <> '{}' and cardinality(parents) <> (
       select count(*) from minion_jobs where id = any (j.parents)
     ) and state = 'inactive'"
  );

[kraih]

This is what the query plan looks like currently.

                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on minion_jobs j  (cost=0.29..1987548.66 rows=41838 width=187) (actual time=267.343..267.343 rows=0 loops=1)
   ->  Index Scan using minion_jobs_state_priority_id_idx on minion_jobs j  (cost=0.29..1987548.66 rows=41838 width=187) (actual time=267.341..267.341 rows=0 loops=1)
         Index Cond: (state = 'inactive'::minion_state)
         Filter: ((parents <> '{}'::bigint[]) AND (cardinality(parents) <> (SubPlan 1)))
         Rows Removed by Filter: 42005
         SubPlan 1
           ->  Aggregate  (cost=47.10..47.11 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=42005)
                 ->  Index Only Scan using minion_jobs_pkey on minion_jobs  (cost=0.29..47.08 rows=10 width=0) (actual time=0.001..0.005 rows=5 loops=42005)
                       Index Cond: (id = ANY (j.parents))
                       Heap Fetches: 210025
 Planning time: 0.318 ms
 Execution time: 267.425 ms
(12 rows)

[kraih]

Btw. i do all profiling with our benchmark script, so my results can be reproduced.

[kraih]

Perhaps we could just get rid of the UPDATE query in repair, and modify our dequeue query to also accept jobs where one or more parents are missing.

  return $self->pg->db->query(
    "update minion_jobs
     set started = now(), state = 'active', worker = ?
     where id = (
       select id from minion_jobs as j
       where delayed <= now() and (parents = '{}' or cardinality(parents) = (
         select count(*) from minion_jobs
         where id = any(j.parents) and state = 'finished'
       )) and queue = any(?) and state = 'inactive' and task = any(?)
       order by priority desc, id
       limit 1
       for update skip locked
     )
     returning id, args, retries, task", $id,
    $options->{queues} || ['default'], [keys %{$self->minion->tasks}]
  )->expand->hash;

That would mean a dependency is considered resolved if the parent job is missing or in the state finished.

(parents = '{}' or cardinality(parents) = (
  select count(*) from minion_jobs
  where id = any(j.parents) and state = 'finished'
))

Would be interesting to see if this subquery could be modified to do that efficiently.

[kraih]

Ok, repair is now two orders of magnitude faster. 😄 5d069b1

[kraih]

I've been trying to replicate my job_info results from yesterday but am failing. The query still looks much more efficient in EXPLAIN ANALYZE, but with our benchmark script the old query is actually faster...

test=# explain analyze select id, args, attempts, array(select id from minion_jobs where j.id = any(parents)) as children, extract(epoch from created) as created, extract(epoch from delayed) as delayed, extract(epoch from finished) as finished, parents, priority, queue, result, extract(epoch from retried) as retried, retries, extract(epoch from started) as started, state, task, worker from minion_jobs as j where id = 10000;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using minion_jobs_pkey on minion_jobs j  (cost=0.29..563.43 rows=1 width=190) (actual time=4.070..4.073 rows=1 loops=1)
   Index Cond: (id = 10000)
   SubPlan 1
     ->  Seq Scan on minion_jobs  (cost=0.00..555.11 rows=489 width=8) (actual time=4.025..4.025 rows=0 loops=1)
           Filter: (j.id = ANY (parents))
           Rows Removed by Filter: 10005
 Planning time: 0.532 ms
 Execution time: 4.121 ms
(8 rows)

test=# explain analyze select id, args, attempts, array(select id from minion_jobs where parents @> ARRAY[j.id]) as children, extract(epoch from created) as created, extract(epoch from delayed) as delayed, extract(epoch from finished) as finished, parents, priority, queue, result, extract(epoch from retried) as retried, retries, extract(epoch from started) as started, state, task, worker from minion_jobs as j where id = 10000;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using minion_jobs_pkey on minion_jobs j  (cost=0.29..168.12 rows=1 width=190) (actual time=0.099..0.101 rows=1 loops=1)
   Index Cond: (id = 10000)
   SubPlan 1
     ->  Bitmap Heap Scan on minion_jobs  (cost=24.39..159.80 rows=50 width=8) (actual time=0.057..0.057 rows=0 loops=1)
           Recheck Cond: (parents @> ARRAY[j.id])
           ->  Bitmap Index Scan on minion_jobs_parents_idx1  (cost=0.00..24.38 rows=50 width=0) (actual time=0.039..0.039 rows=0 loops=1)
                 Index Cond: (parents @> ARRAY[j.id])
 Planning time: 0.284 ms
 Execution time: 0.178 ms
(9 rows)

[kraih]

Think we are in a pretty good spot now overall. Been running benchmarks with a million jobs in the queue and everything feels snappy. Closest to feeling like a bottleneck now would be stats and maybe enqueue, but not enough to make either a priority.