I noticed that performance on my db machine has gone down since switching over to delayed indexing. After taking a look at the mysql slow query logs, I found a few interesting things:
1. The delayed_job query is running every second. Is there a way to reduce the frequency? 2. It seems that the run_at <= now() prevents any indexes to have an effect, as it results in a full table scan (the size of my table is 174307) I'm wondering if its possible to clean up this query a little bit to help take advantage of indexes, but I dont know where to look. Here is an excerpt for the slow query log: # Time: 100810 12:07:13 # Query_time: 0.833234 Lock_time: 0.000047 Rows_sent: 3 Rows_examined: 174307 SET timestamp=1281467233; SELECT * FROM `delayed_jobs` WHERE (`delayed_jobs`.`deleted` = 0) AND ((run_at <= '2010-08-10 19:07:12' AND (locked_at IS NULL OR locked_at < '2010-08-10 15:07:12') OR locked_by = 'host:master1 pid:20024') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5; # Time: 100810 12:07:14 # Query_time: 0.807479 Lock_time: 0.000055 Rows_sent: 2 Rows_examined: 174307 SET timestamp=1281467234; SELECT * FROM `delayed_jobs` WHERE (`delayed_jobs`.`deleted` = 0) AND ((run_at <= '2010-08-10 19:07:13' AND (locked_at IS NULL OR locked_at < '2010-08-10 15:07:13') OR locked_by = 'host:master1 pid:20024') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5; # Time: 100810 12:07:15 # Query_time: 1.008380 Lock_time: 0.000057 Rows_sent: 1 Rows_examined: 174307 SET timestamp=1281467235; SELECT * FROM `delayed_jobs` WHERE (`delayed_jobs`.`deleted` = 0) AND ((run_at <= '2010-08-10 19:07:14' AND (locked_at IS NULL OR locked_at < '2010-08-10 15:07:14') OR locked_by = 'host:master1 pid:20024') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5; # Time: 100810 12:07:16 # Query_time: 0.795291 Lock_time: 0.000058 Rows_sent: 0 Rows_examined: 174307 SET timestamp=1281467236; SELECT * FROM `delayed_jobs` WHERE (`delayed_jobs`.`deleted` = 0) AND ((run_at <= '2010-08-10 19:07:15' AND (locked_at IS NULL OR locked_at < '2010-08-10 15:07:15') OR locked_by = 'host:master1 pid:20024') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5; # Time: 100810 12:07:17 # Query_time: 0.799123 Lock_time: 0.000135 Rows_sent: 0 Rows_examined: 174307 SET timestamp=1281467237; SELECT * FROM `delayed_jobs` WHERE (`delayed_jobs`.`deleted` = 0) AND ((run_at <= '2010-08-10 19:07:16' AND (locked_at IS NULL OR locked_at < '2010-08-10 15:07:16') OR locked_by = 'host:master1 pid:20024') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5; -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
