Page MenuHomePhabricator

Reverse meaning of task priority column
ClosedPublic

Authored by epriestley on Nov 23 2014, 7:34 PM.
Tags
None
Referenced Files
Unknown Object (File)
Fri, Mar 22, 3:57 AM
Unknown Object (File)
Feb 8 2024, 1:41 PM
Unknown Object (File)
Feb 5 2024, 6:53 PM
Unknown Object (File)
Feb 3 2024, 1:24 AM
Unknown Object (File)
Jan 10 2024, 9:31 AM
Unknown Object (File)
Jan 10 2024, 9:31 AM
Unknown Object (File)
Jan 10 2024, 9:30 AM
Unknown Object (File)
Jan 5 2024, 4:01 PM

Details

Summary

Ref T6615. Mixing ASC and DESC ordering on a multipart key makes it dramatically less effective (or perhaps totally ineffective).

Reverse the meaning of the priority column so it goes in the same direction as the id column (both ascending, lower values execute sooner).

Test Plan
  • Queued 1.2M tasks with bin/worker flood.
  • Processed ~1 task/second with bin/phd debug taskmaster before patch.
  • Applied patch, took ~5 seconds for ~1.2M rows.
  • Processed ~100-200 tasks/second with bin/phd debug taskmaster after patch.
  • "Next in Queue" query on daemon page dropped from 1.5s to <1ms.

Diff Detail

Repository
rP Phabricator
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Event Timeline

epriestley retitled this revision from to Reverse meaning of task priority column.
epriestley updated this object.
epriestley edited the test plan for this revision. (Show Details)
epriestley added a reviewer: btrahan.

mysql query performance is dark magic.

Yeah -- my mental model of indexes is that MySQL just builds some kind of fancy magical tree inside itself, so it followed that after it walked down the "leaseOwner = ?" part of the tree to get to the "leaseOwner = null" node it would be looking at a "priority = ?" subtree, and it could arbitrarily start at the left or right side to do the order by bit, and once it had walked down that subtree to the left-most or right-most node it would have the same opportunity to start at the left or right side of the "id = ?" sub-sub-tree at each priority node.

Clearly that's not what it actually does given that priority DESC, id ASC is >1500x slower than priority ASC, id ASC with 1.2M rows.

iiam

btrahan edited edge metadata.

My mental model is "you think you are in control, but who is in control of chaos? Better run EXPLAIN again."

This revision is now accepted and ready to land.Nov 24 2014, 6:31 PM
This revision was automatically updated to reflect the committed changes.