Newly-added sled wasn't chosen for instance allocation (for a while?)

During today's demos, I attempted to show adding a sled to madrid and seeing newly-started instances running on it. Before the demo, I started 10 instances (with only 3 sleds available), and the distribution was:

   1 BRM42220046
   5 BRM42220081
   4 BRM44220001

During the demo, I started 30 instances, and the distribution was:

  10  BRM42220046
  10  BRM42220081
   9  BRM44220001
   0  BRM42220004 (added sled)

Each of these instances wanted 8 CPUs, so at this point the CPU resource usage was:

  11 instances =  88 CPUs  BRM42220046
  15 instances = 120 CPUs  BRM42220081
  13 instances = 104 CPUs  BRM44220001

We started a call to debug why the new sled wasn't receiving instances. Without making any changes to madrid, I started 20 more instances, and they were allocated to these sleds in this order:

BRM42220081 (new total = 16)
BRM42220004 (new total =  1)
BRM42220046 (new total = 12)
BRM42220004 (new total =  2)
BRM42220046 (new total = 13)
BRM42220046 (new total = 14)
BRM42220046 (new total = 15)
BRM42220004 (new total =  3)
BRM42220004 (new total =  4)
BRM42220004 (new total =  5)
BRM44220001 (new total = 14)
BRM42220004 (new total =  6)
BRM42220004 (new total =  7)
BRM42220046 (new total = 15)
BRM42220004 (new total =  8)
BRM42220004 (new total =  9)
BRM44220001 (new total = 15)
BRM44220001 (new total = 16)
BRM42220004 (new total = 10)
BRM42220004 (new total = 11)

Once each sled hits 16, we expectedly don't choose it again, as that maxes out its CPUs at 128. Purely eyeballing, this final list of 20 looks believable if we're choosing randomly, but getting 0 out of the first 30 on the new sled is exceedingly unlikely (~0.02%, assuming a 25% chance on each instance).

The exact query we're using to select a sled for one of the above instances is:

SELECT "sled"."id" FROM ("sled" LEFT OUTER JOIN "sled_resource" ON ("sled_resource"."sled_id" = "sled"."id")) WHERE ((("sled"."time_deleted" IS NULL) AND ("sled"."sled_policy" = 'in_service')) AND ("sled"."sled_state" = 'active')) GROUP BY "sled"."id" HAVING ((((COALESCE(SUM(CAST(hardware_threads as INT8)), 0) + 8) <= "sled"."usable_hardware_threads") AND ((COALESCE(SUM(CAST(rss_ram as INT8)), 0) + 0) <= "sled"."usable_physical_ram")) AND ((COALESCE(SUM(CAST(reservoir_ram as INT8)), 0) + 17179869184) <= "sled"."reservoir_size")) ORDER BY random() LIMIT 1

If we remove the LIMIT 1 and run the query using CRDB's AS OF date set to just after the new sled was added, we see all four were available. Our best guesses at the moment are:

• We just got extremely unlucky.
• There is something suspect with ORDER BY random() that caused us to be unlucky.
• We hit something like https://github.com/oxidecomputer/async-bb8-diesel/issues/47, and the Nexus(s) choosing sled placement were seeing stale CRDB data. We checked for and saw no open transactions, so if this was happening we don't have any postmortem evidence of it.

Start by reproducing the sled-selection SQL and compare the normal query with the CRDB AS OF query taken just after the new sled was added. Inspect the possible ORDER BY random() behavior and async-bb8-diesel issue 47; done means identifying the cause of the missing allocations and documenting or addressing the resulting behavior.