Pauses via queues/class name causes DB to spike to 100% and dashboard to crash

[salmonsteak1]

Hey @bensheldon , thanks for working on the pause feature! I was able to test it and it seems like there will be a spike in the CPU for the DB. I believe this is the query in question:

SELECT
  "good_jobs"."id",
  "good_jobs"."queue_name",
  "good_jobs"."priority",
  "good_jobs"."serialized_params",
  "good_jobs"."scheduled_at",
  "good_jobs"."performed_at",
  "good_jobs"."finished_at",
  "good_jobs"."error",
  "good_jobs"."created_at",
  "good_jobs"."updated_at",
  "good_jobs"."active_job_id",
  "good_jobs"."concurrency_key",
  "good_jobs"."cron_key",
  "good_jobs"."cron_at",
  "good_jobs"."batch_id",
  "good_jobs"."batch_callback_id",
  "good_jobs"."executions_count",
  "good_jobs"."job_class",
  "good_jobs"."error_event",
  "good_jobs"."labels",
  "good_jobs"."locked_by_id",
  "good_jobs"."locked_at"
FROM
  "good_jobs"
WHERE
  "good_jobs"."id" IN (
  WITH
    "rows" AS MATERIALIZED (
    SELECT
      "good_jobs"."id"
    FROM
      "good_jobs"
    WHERE
      "good_jobs"."finished_at" IS NULL
      AND "good_jobs"."scheduled_at" <= $1
      AND "good_jobs"."queue_name" NOT IN (
      SELECT
        jsonb_array_elements_text(value->$2)
      FROM
        "good_job_settings"
      WHERE
        "good_job_settings"."key" = $3)
      AND "good_jobs"."job_class" NOT IN (
      SELECT
        jsonb_array_elements_text(value->$4)
      FROM
        "good_job_settings"
      WHERE
        "good_job_settings"."key" = $5)
      AND NOT (COALESCE("good_jobs"."labels" && ARRAY((
            SELECT
              jsonb_array_elements_text(value->$6)
            FROM
              "good_job_settings"
            WHERE
              "good_job_settings"."key" = $7)), $8))
    ORDER BY
      priority ASC
    NULLS LAST
      ,
      "good_jobs"."created_at" ASC
    LIMIT
      $9)
  SELECT
    "rows"."id"
  FROM
    "rows"
  WHERE
    pg_try_advisory_lock(($10 || SUBSTR(MD5($11 || $12 || "rows"."id"::text), $13, $14))::bit(64)::bigint)
  LIMIT
    $15)
ORDER BY
  priority ASC
NULLS LAST
  ,
  "good_jobs"."created_at" ASC

Unfortunately, I wasn't able to get a query plan sample (I think it has got to do with all the CPU being consumed by this query), but these are all the information I have pertaining to each query (one during the "normal", non paused scenario, and the other during the paused scenario).

"Normal", non paused scenario

Paused scenario

I hope this helps! Please let me know if you'd like me to try to tune any settings on my end. Oh I think its worth noting that I disabled LISTEN/NOTIFY, I will try again with it enabled

[bensheldon]

That's not good! How many jobs are queuing up when you pause?

When pausing jobs, the database will still potentially scan those rows and filter out the values, so I imagine that's what is happening.

I don't imagine that enabling/disabling LISTEN/NOTIFY will have any effect. Right now, pausing's implementation only has an effect on the execution side, not the enqueue... though that would be interesting to also not NOTIFY if the job is paused (though would require another database query during enqueue)