Page MenuHomePhabricator
Create Task
Maniphest T240673

Pages shouldn't time out due to expensive database queries
Closed, ResolvedPublic
Actions

Description

The Wikipedia Library program page (https://wikilink.wmflabs.org/programs/1) and a number of organisation pages currently don't load due to hitting the 30 second nginx timeout. The database queries for loading the data happen on page load and take an excessive amount of time to execute. The biggest offenders are the LinkEvents over time graph and the three breakout tables for top organisation/editor/project/page.

Instead of calculating the data for the tables and other figures on every page load, we should add new models to store this data, calculating it asynchronously. Page loads would then pull this data instead of waiting for lots of calculations.

Another option could have been to use caching, by crawling each page in the background so that cached versions were always available. This isn't feasible here because we have date range filtering - it would be impossible to load each combination of dates.

Acceptance criteria

  • All program and organisation pages load within 5 seconds.

Related Objects
Search...

Event Timeline

Samwalton9-WMF created this task.Dec 13 2019, 11:58 AM
Restricted Application added a subscriber: Sadads. · View Herald TranscriptDec 13 2019, 11:58 AM
Samwalton9-WMF triaged this task as High priority.Dec 13 2019, 11:58 AM
Samwalton9-WMF renamed this task from Wikipedia Library program page doesn't load due to expensive db queries to Some pages don't load due to expensive db queries.Apr 13 2020, 2:20 PM
Samwalton9-WMF updated the task description. (Show Details)
MBinder_WMF added a project: The-Wikipedia-Library (Kanban).Jun 2 2020, 7:43 PM
ARamirez_WMF moved this task from Kanban to Bugs on the The-Wikipedia-Library board.Jun 2 2020, 9:05 PM
ARamirez_WMF edited projects, added The-Wikipedia-Library; removed The-Wikipedia-Library (Kanban).
Samwalton9-WMF moved this task from Bugs to Open Tasks WikiLink on the The-Wikipedia-Library board.Jun 3 2020, 1:26 PM
Samwalton9-WMF updated the task description. (Show Details)Jul 8 2020, 10:30 AM
Samwalton9-WMF renamed this task from Some pages don't load due to expensive db queries to Pages shouldn't time out due to expensive db queries.Jul 16 2020, 10:16 AM
Samwalton9-WMF renamed this task from Pages shouldn't time out due to expensive db queries to Pages shouldn't time out due to expensive database queries.Aug 6 2020, 8:52 AM
Samwalton9-WMF moved this task from Open Tasks WikiLink to To be discussed on the The-Wikipedia-Library board.
Samwalton9-WMF mentioned this in T250084: Investigate how to keep the EventStream tracking script connected [8HR].
Samwalton9-WMF updated the task description. (Show Details)EditedAug 6 2020, 10:11 AM

We discussed this task yesterday and decided that the best approach is likely going to be doing the calculations for top tables and the graph asynchronously, storing that data and fetching it on page load.

If we do this we could also probably get rid of memcached, replacing it with file-based caching if we decide caching is still useful. This could be a separate task.

Additionally, we noted that Django 3.1 just introduced async views, though the feature isn't expected to be completed until 3.2.

Samwalton9-WMF moved this task from To be discussed to Ready for Dev on the The-Wikipedia-Library board.Aug 6 2020, 10:13 AM
Samwalton9-WMF mentioned this in T259866: [SPIKE] Investigate Django async views [2hrs].Aug 7 2020, 7:31 AM
Samwalton9-WMF moved this task from Ready for Dev to To be discussed on the The-Wikipedia-Library board.

The first step here will be evaluating Django async views in 3.1 (T259866). This would necessitate a Django upgrade before proceeding with this work, but may be worthwhile.

Samwalton9-WMF moved this task from To be discussed to Kanban on the The-Wikipedia-Library board.Aug 18 2020, 2:35 PM
Samwalton9-WMF edited projects, added The-Wikipedia-Library (Kanban); removed The-Wikipedia-Library.
Samwalton9-WMF added a comment.Aug 24 2020, 10:13 AM

Something which occurred to me today - the tables and graphs change based on the user's filters. How would this play with doing our calculations asynchronously?

Scardenasmolinar claimed this task.Sep 29 2020, 2:43 PM
Scardenasmolinar moved this task from Ready to In Progress on the The-Wikipedia-Library (Kanban) board.
Samwalton9-WMF removed a project: The-Wikipedia-Library (Kanban).Dec 1 2020, 3:58 PM

Taking this off the Kanban now that it's split into component tasks.

Samwalton9-WMF closed subtask T259866: [SPIKE] Investigate Django async views [2hrs] as Resolved.Dec 17 2020, 3:11 PM
Samwalton9-WMF closed subtask T269146: Aggregate Wikilink data into daily totals as Resolved.Jan 5 2021, 10:05 AM
Samwalton9-WMF closed this task as Resolved.Jan 8 2021, 12:27 PM
Samwalton9-WMF closed subtask T269147: Use daily aggregates to populate tables and graphs on Organisation and Program pages as Resolved.
Samwalton9-WMF closed subtask T269841: Reimplement user list filtering as Resolved.

This has one remaining feature to reimplement, but it's low priority and we're not going to work on it soon.

All organisation pages load in a few seconds. The Wikipedia Library program page currently takes closer to 20 seconds, but we're considering that good enough for now with caching.

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL