Experiencing a lot of database overhead with forked jobs

[SimantovYousoufov]

We're running into an interesting situation when using Resque while trying to process a queue of millions of jobs. I'm pretty confident this is due to Rails or something within Rails re-initializing with every fork, but it's unclear what there is to be done about it.

Our environment:

• resque 2.5.0
• rails 7.0.4.3
• INTERVAL=0.1
• FORK_PER_JOB not set
• Running on AWS ECS, PostgreSQL

The symptoms:

• Performance metrics measure jobs we expect to take around 20-50ms as taking 500-1300ms and 250-500ms of dead time before it begins work
• Database CPU spikes and remains pinned and we see the following Rails DB connection init queries run a few hundred times/sec (indicating they run with every fork):

SELECT a.attname
  FROM (
         SELECT indrelid, indkey, generate_subscripts(indkey, ?) idx
           FROM pg_index
          WHERE indrelid = ?::regclass
            AND indisprimary
       ) i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[i.idx]
 ORDER BY i.idx

and 

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
       pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
       c.collname, col_description(a.attrelid, a.attnum) AS comment,
       ? as attgenerated
  FROM pg_attribute a
  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
  LEFT JOIN pg_type t ON a.atttypid = t.oid
  LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
 WHERE a.attrelid = ?::regclass

We've been able to get around this by setting FORK_PER_JOB=false and avoiding forking which lets us get back to the performance we're expecting. However, I'm wondering what can be done about this so we can get back to resque's recommendation of forking for each job and gain that safety back. More than happy to dive into any details/provide any more info.

[travisbell]

Hi @SimantovYousoufov, yup, I have seen this too. There can be a fair bit of overhead creating new connections, etc. on every fork. To get around this we started using resque-multi-job-forks which lets us not have to fork for every job. Once enabled, we saw a massive performance increase with the job execution time and also a noticeable load decrease on our db.

Have you tried using that plugin?

[PatrickTulskie]

Yeah Rails does automatically reconnect after forking now. I don't think Resque can actually do anything to solve this other than forking less often. You can potentially try to configure Rails to not do this, but I'm not sure if that's an out of the box configuration option or if that's something you need to roll yourself. Ideally you'd only do that on resque jobs and not with your web server.

[travisbell]

Ya, and for good reason. There can a lot of oddness with trying to re-use connections across new forks.

Agreed with @PatrickTulskie, not sure there's much you can really do other than either not fork, or if you still want some of the benefits of forking, use the plugin I mentioned above.