Worker task table has some remaining awkward keys
Open, LowPublic

Description

See IRC.

  • With 1M tasks in queue, the query to pull a task off the top of the stack turns into a garbage mess full of tablescans.
  • Some of the keying assumes more failed tasks than queued tasks. This is technically true on a normal install (perhaps dozens of failed tasks, ~0 queued tasks) but the scale is irrelevant. All realistic installs with more than 100K rows should have 99% of them in queue.
  • The keys on this table are also a mess.
epriestley updated the task description. (Show Details)
epriestley raised the priority of this task from to High.
epriestley claimed this task.

All realistic installs with more than 100K rows should have 99% of them in queue.

We also don't even use this key when selecting already-failed tasks.

D10895 appears to be a sufficient fix. I want to adjust some other keys on this table eventually so I'm going to leave this open, but I'll downgrade the priority once the dust settles.

Just to confirm I'm not crazy, here's some supporting documentation for "mixing ASC + DESC makes the key unusable":

http://explainextended.com/2010/11/02/mixed-ascdesc-sorting-in-mysql/

Here's a blog post covering exactly this problem (priority column + id column in a queue) and arriving at the same conclusion and solution:

http://beerpla.net/2009/03/18/mysql-indexing-considerations-of-implementing-a-priority-field-in-your-application/

epriestley renamed this task from Selection query on task table is garbage with many unlocked tasks to Worker task table has some remaining awkward keys.Nov 24 2014, 8:16 PM
epriestley lowered the priority of this task from High to Low.

Let us know if you're still seeing issues with large queues after those patches, but I think the meat of this issue is fixed.

epriestley moved this task from Backlog to Availability on the Daemons board.Apr 8 2016, 9:45 PM
epriestley moved this task from Availability to vNext on the Daemons board.Feb 21 2017, 12:37 AM