This problem fixed itself for now. We're not really sure what the precipitating factor was but I have my suspicions around what happened and plans to address it.
With the exception of suggested edits and the triage queue, review tasks don't instantly populate the queue when their criteria are met. This is a design as old as review queues and is probably driven by the fact that most tasks don't land in the queues until 15 minutes after they otherwise qualify.
Every 5 minutes, every site runs a job that:
- Finds every post that should be in a review queue but isn't.
- Finds every post that is in a review queue but should no longer be.
It then creates tasks for every post in 1 and deletes/invalidates every task from 2. These queries have bounds and performance tweaks but can still be very hard on SQL Server.
At the same time, we have a couple other jobs that can be hard on SQL Server, the awarding of badges and an hourly syncing job. At the top of the hour, we could have 1000 of these jobs all wanting to run at once.
Since we have all our sites run on shared infrastructure, we can't have 1,000 jobs all simultaneously trying to get to run their expensive queries, so they get queued up to only run 8 at a time. This keeps the network from falling over.
So what probably went wrong?
My suspicion from looking a differences in per-site behavior, is that we simply had more work to do in 5 minutes than could get done and scheduler wasn't designed for that. When each job needs to run, it attempts to grab at a semaphore lock to get permission. By its nature, the semaphore isn't FIFO but closer to random chance as to who runs when. That is fine under normal conditions because there's not a huge difference in quality of service between being the first and last job to run.
Under our supposed conditions, where maybe 50-100 or more jobs are waiting to run after 5 minutes, we run into a big problem. The jobs that just got to run are now queueing up again and not waiting patiently behind the tasks that got there first. Random chance could keep a job waiting for minutes or hours.
Anyway, that's my theory.
What are we going to do about it?
I had a lot of proposals that got reviewed, rejected, and tweaked that I'm not keen on relitigating, but I can tell you what our short-term and longer-term plans are here.
In the short term, I am setting up monitoring so we can see how frequently these jobs are running or failing to run and alert if there's a major disruption. Hopefully prior to you observing it. If we do observe it, we can temporarily decrease the frequency of jobs to give them the breathing room they need to complete.
In the longer term, our plan is to make infrastructure improvements that move from our more expensive syncing queries to event based task creation and invalidation. That will eliminate the sync query overhead and risk and simplify workflow changes we already had planned. I'm already working on a POC for First Posts since it's the least complicated of the bunch.
Incidentally, this was already on our radar for the project but this incident and meta report has bumped up the priority.