locked_by needs an index #21

Open
needfeed opened this Issue Jul 9, 2012 · 1 comment

Comments

Projects
None yet
2 participants

needfeed commented Jul 9, 2012

Using delayed_job 3.0.1 and delayed_job_active_record 0.3.2 we are seeing a lot of time spent in:

      update_all("locked_by = null, locked_at = null", ["locked_by = ?", worker_name])

(lib/delayed/backend/active_record.rb line 44).

Adding an index to locked_by seems necessary.

Our delayed_jobs table has 2.3 million rows (due to extensive use of run_at), but even at thousands of rows, this would appear to be a significant performance issue.

palexvs commented Aug 9, 2013

delayed_job_active_record v0.4.4
MySQL 5.5
90K jobs in table
Job spends ~1-3 seconds to lock record

#Query_time: 2.187221  Lock_time: 2.000912 Rows_sent: 0  Rows_examined: 89623
UPDATE `delayed_jobs` SET `locked_at` = '2013-08-09 15:28:17', `locked_by` = 'delayed_job.0 host:worker1.com pid:26786' WHERE ((run_at <= '2013-08-09 15:28:17' AND (locked_at IS NULL OR locked_at < '2013-08-09 11:28:17') OR locked_by = 'delayed_job.0 host:worker1.com pid:26786') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1;

Is there any solution to speed up?

Thank you

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment