Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.
Sign up to join this community
Across the network, users are reporting cases where tag badges are re-awarded:
They basically lose all their tag badges, and then get them all back at once:
TL;DR; This was my fault and it's in the process of being resolved, but it is going to take a couple of days to flush out all of the issues.
On Monday, January 11th I was reviewing alerts on the main SQL Servers from the weekend. I noticed that on January 10th, we had a significant spike in CPU on the main server for Stack Overflow which caused the backups to fail and lots of exceptions to be thrown.
After some investigating, I found a nightly scheduled job to update Tag Totals and assign tag badges was causing it. Initially, I hoped it was a fluke, but 2 days later we had the same sustained CPU spike - it was time to fix it (or so I thought).
The query that was running in production calculates the tag totals for every user nightly. This is fine on most of the network, but on Stack Overflow we're talking millions and millions of users and tags to calculate nightly - lots of data to process.
I took a stab at fixing the query by limiting the number of users to calculate nightly. I rewrote the query and limited it to only calculate the tag totals for Users with Posts that received a vote or a tag edit in the last week. This change significantly reduced the amount of data needed to be processed and that portion of the query performed significantly better. I tested that piece thoroughly - it was code reviewed by 3 other devs and was pushed to production on January 20, 2021. The image below is the CPU for the query once in production - no more sustained CPU spike.
After this was pushed to production we started to see some exceptions in the logs when the scheduled job was running nightly. This morning, Dean Ward pointed me to some of the Meta reports about the tag/badge issues, so it was back to investigating.
It took just a few minutes to realize the issue. The job calculates the tag totals for each user, but then goes to the UserTagTotals table and deletes everything no longer needed for the user - meaning if a tag is no longer on that user's post we don't need the row any longer... well, since I had limited the totals the last 7 days it was deleting everything not in there.
Yes, I really did do this when I realized it.
At this point, I have reverted the change we pushed last week and have added a hint to the "old version" to stop killing the CPU on the server. I've manually executed the job on Stack Overflow and the sites that reported the issues. The job runs nightly and will take 2 nights to clear out all of the issues.
Again, sorry for the mix-up and it should be resolved in the next few days.
/help/badges/<id>?
Jan 26, 2021 at 20:52