Page MenuHomePhabricator
Create Task
Maniphest T394785

Slow queries on finding articles created by a given user in GrowthExperiments
Closed, ResolvedPublic
Actions

Description

Caused by rEGRE96cd0dd1c8b9: UserImpact: Add created articles count

Currently this is one of the slowest queries in production that is happening quite frequently: https://logstash.wikimedia.org/goto/6ba380f339057c3cc485f8357295cc65

In some cases 30s long queries:
https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-mediawiki-1-7.0.0-1-2025.05.20?id=bXb67ZYB-jfKnJis4S16

They are stuff like:

SELECT  COUNT(*) AS `rowcount`  FROM (SELECT  1  FROM `page` JOIN `revision` ON ((page.page_id = rev_page))   WHERE page.page_namespace = 0 AND rev_actor = 1596 AND rev_parent_id = 0  LIMIT 1000  ) `tmp_count`

Please don't run this query if the user has more than x edits (1000? 10,000?) by checking user_editcount.

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
ComputedUserImpactLookup: Use logging table for approximate created articles countmediawiki/extensions/GrowthExperimentswmf/1.45.0-wmf.2+53 -15
ComputedUserImpactLookup: Use logging table for approximate created articles countmediawiki/extensions/GrowthExperimentsmaster+53 -15
Customize query in gerrit

Related Objects

Event Timeline

Ladsgroup created this task.May 20 2025, 2:14 PM
Restricted Application added a project: Growth-Team. · View Herald TranscriptMay 20 2025, 2:14 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Ladsgroup updated the task description. (Show Details)May 20 2025, 2:14 PM
Ladsgroup updated the task description. (Show Details)May 20 2025, 2:17 PM
Ladsgroup mentioned this in T388868: UserInfoCard: Add data point for articles created by the user to store.May 20 2025, 2:32 PM
Michael mentioned this in T341599: Impact Module: improvements for former newcomers.May 20 2025, 2:41 PM
Ladsgroup added a comment.May 20 2025, 10:58 PM

To document the discussion in slack: You can use logging table for now. That should be good enoughTM

kostajh claimed this task.May 21 2025, 7:59 AM
kostajh added a project: Trust and Safety Product Sprint (Sprint Carrot Cake (May 26 - June 13)).
kostajh edited projects, added GrowthExperiments-ImpactModule, CheckUser-UserInfoCard; removed GrowthExperiments.
kostajh updated the task description. (Show Details)
DMburugu moved this task from Inbox to Triaged on the Growth-Team board.May 21 2025, 2:16 PM
gerritbot added a comment.May 21 2025, 6:46 PM

Change #1148936 had a related patch set uploaded (by Kosta Harlan; author: Kosta Harlan):

[mediawiki/extensions/GrowthExperiments@master] (WIP) ComputedUserImpactLookup: Use logging table for counting created articles

https://gerrit.wikimedia.org/r/1148936

gerritbot added a project: Patch-For-Review.May 21 2025, 6:46 PM
kostajh moved this task from Priority Backlog to Needs Review on the Trust and Safety Product Sprint (Sprint Carrot Cake (May 26 - June 13)) board.May 22 2025, 8:58 AM
kostajh mentioned this in T68961: Logging needs an index to optimize searching by log_title.May 22 2025, 9:04 AM
gerritbot added a comment.May 22 2025, 11:51 AM

Change #1148936 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] ComputedUserImpactLookup: Use logging table for approximate created articles count

https://gerrit.wikimedia.org/r/1148936

ReleaseTaggerBot added a project: MW-1.45-notes (1.45.0-wmf.3; 2025-05-27).May 22 2025, 12:00 PM
Maintenance_bot removed a project: Patch-For-Review.May 22 2025, 12:30 PM
gerritbot added a comment.May 22 2025, 12:51 PM

Change #1149394 had a related patch set uploaded (by Kosta Harlan; author: Kosta Harlan):

[mediawiki/extensions/GrowthExperiments@wmf/1.45.0-wmf.2] ComputedUserImpactLookup: Use logging table for approximate created articles count

https://gerrit.wikimedia.org/r/1149394

gerritbot added a project: Patch-For-Review.May 22 2025, 12:51 PM
gerritbot added a comment.May 22 2025, 1:25 PM

Change #1149394 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@wmf/1.45.0-wmf.2] ComputedUserImpactLookup: Use logging table for approximate created articles count

https://gerrit.wikimedia.org/r/1149394

Stashbot added a comment.May 22 2025, 1:25 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-22T13:25:28Z] <mszabo@deploy1003> Started scap sync-world: Backport for [[gerrit:1149394|ComputedUserImpactLookup: Use logging table for approximate created articles count (T394785)]]

Stashbot added a comment.May 22 2025, 1:27 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-22T13:27:38Z] <mszabo@deploy1003> mszabo, kharlan: Backport for [[gerrit:1149394|ComputedUserImpactLookup: Use logging table for approximate created articles count (T394785)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Maintenance_bot removed a project: Patch-For-Review.May 22 2025, 1:30 PM
Stashbot added a comment.May 22 2025, 1:36 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-22T13:36:00Z] <mszabo@deploy1003> Finished scap sync-world: Backport for [[gerrit:1149394|ComputedUserImpactLookup: Use logging table for approximate created articles count (T394785)]] (duration: 10m 32s)

ReleaseTaggerBot edited projects, added MW-1.45-notes (1.45.0-wmf.2; 2025-05-20); removed MW-1.45-notes (1.45.0-wmf.3; 2025-05-27).May 22 2025, 2:00 PM
kostajh moved this task from Needs Review to Needs QA on the Trust and Safety Product Sprint (Sprint Carrot Cake (May 26 - June 13)) board.May 22 2025, 5:01 PM

https://logstash.wikimedia.org/goto/9b31df85a6095bbba6e11b487328bf8d slow queries are gone.

Ladsgroup added a comment.May 23 2025, 10:26 AM
In T394785#10849465, @kostajh wrote:

https://logstash.wikimedia.org/goto/9b31df85a6095bbba6e11b487328bf8d slow queries are gone.

Thank you!

kostajh mentioned this in T395215: UserInfoCard: Update articles count to show 1000+ if the count is 1000.May 25 2025, 8:27 PM
kostajh closed this task as Resolved.Jun 11 2025, 9:26 AM
kostajh moved this task from Needs QA to Done on the Trust and Safety Product Sprint (Sprint Carrot Cake (May 26 - June 13)) board.
Restricted Application added a project: Trust and Safety Product Team. · View Herald TranscriptJun 11 2025, 9:26 AM
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 · Credits