Admins from (https://openstack-browser.toolforge.org/project/globaleducation) added as subscribers, if this isn't something you can help with apologies for extra notifications.

I've been working with Rails performance expert Nate Berkopec this month, and we've come to a clearer understanding of the usual cause of recurring outages. In most cases (except for the most recent one), the system gets overloaded through the combination of the background processes (which are responsibile for updating stats for courses and tie up a significant portion of system resources on essentially a continual basis) and multiple users requesting particularly slow pages (ie, ones that load a lot of data). The web server uses up to 6 "Passenger" processes that handle a web request by sending it to Ruby on Rails application. If all 6 of these are tied up with very slow requests, then the system can't process any more requests in the meantime.

The manual_update route for doing an immediate stats update for a program was one major source of slow requests, as a course with highly active users or a very long tracking period can take minutes or even hours to update. The manual_update route would attempt an update as a web request instead of a background job, meaning it would tie up one web process until it completed. Although this route isn't accessible via any UI elements, I had told a number of program organizers about it at times when they had urgent need for updated stats, and word may have spread further; it was being used very regularly. I've restricted that feature to super admin users.

The main remaining culprits for tying up web processes are the explore index, which is simply trying to do too complex of a summary of stats across the system and isn't being cached aggressively enough, and the Articles and Users lists for campaigns, which are loading too much data too inefficiently. These should be fixable by reworking the slow pages to load less data at once, query more efficiently, and/or save cached data for longer.

These situations are much more likely to tie up all the processes when many people are using the Dashboard at once, so downtime has often occurred during weekends busy with editathons.

The background jobs and the capacity to provide updated stats for so many resource-intensive programs is the biggest bottleneck of the system, so we're currently prioritizing an archicture change to spread the system across multiple servers. With one server for web requests, another for the database, and another for background jobs, it should become far more resilient to periods of high simultaneous use.

I'm deploying the major update to the statistics generation system today. I've paused all the data update workers, and I'm currently generating a database dump. After that, I'll deploy and then the system will begin processing all current courses using the new system; it will take quite a while to do the initial round of processing, as it will be pulling in data for all edits since the beginning of each still-active event. Once the initial round of processing is complete, updates should eventually reach a steady state with a better rate that currently.

Meanwhile, based on how it went on dashboard.wikiedu.org, where the new system was deployed more than a week ago, it should dramatically reduce database load, and hopefully that means the end of the recurring downtime problems, which we believe were caused by update processes doing expensive queries over the Revisions table of the Dashboard's database... which are completely eliminated in the new update system.

The deployment is complete, and I've enqueued all the current events for their first cycle through the new update cycle. There are 804 current ones, and the low-revision count ones should get processed first. The others will take a while, I'll keep this issue updated as it progresses.

It looks like things are working smoothly so far. I have added extra workers, since the memory footprint of the update cycle is so much lower now, so there a bunch of processes working through the update backlog. We identified a number of inefficiencies that we can fix to speed up the update cycles, which I'll be deploying as we have them ready, but meanwhile we're down to 515 events in the queues awaiting their first update with the new system. The bigger ones that will take a long time to process are at the end, and may take a while to get through, but I will start steady-state processing for the ones that aren't going to take too long once we've done all the initial processing of the events with fewer than 10k edits.

For this first manually-managed update cycle, I sorted events into the short, medium or long queue based on revision count, with every event with less than 1000 revision in the short queue, events between 1k and 10k in the medium queue, and events with more than 10k edits in the long queue. So once the short and medium queues are empty, I'll start steady-state processing those so that they start getting as-often-as-possible updates. Interested observers can keep an eye on the queue progress here: https://outreachdashboard.wmflabs.org/status

Working through the initial backlog continues apace. We're down to 204 courses remaining, but that still includes the majority of the very large courses.

This morning the web server became unresponsive, despite relatively minimal database load and the background workers working smoothly without memory spikes. This suggests that there's some frontend routes that are slow enough to lock up the available slots in the server queue even without a database CPU or memory bottleneck. I'll work figuring out the cause.

We continue chewing threw the backlog, down to 57 events remaining for their first update. We're on the last 20 of the medium queue (up to 10k edits). Once those are complete, I will start steady-state updates for the rest of the already-processed events.

We've gotten through nearly all of the first updates now, and I've re-initiated update processing for the already-processed ones. The last few are still working through their first updates, but we're cycles through now with about 1 or 2 days of latency for the rest.

Webserver downtime has happened several times, and we're exploring some slow queries that may be at the root of it. I moved one source of the slow queries to a separate server, so we'll see if that helps.

Okay, I think I've finally found the major cause of the downtime that continued happening after the data rearchitecture deployment! I deployed a fix today, and I'm very optimistic that it will be much more stable going forward.

Also, updates for ongoing events are now happening with at most about 4 hours of latency (yay!) and that's going to get even lower once we fix a few more inefficiencies that are causing unnecessary reprocessing. I think I'll be able to re-enable updates for the very large programs that I had manually disabled before, and also to lift the limits on the number of editors for a program and the amount of time it runs for.