Page MenuHomePhabricator
Create Task
Maniphest T105189

[Bug] Wikidata action=query&list=tags should not take 15-25 seconds to respond
Closed, ResolvedPublic
Actions

Description

The RTRC[1] tool queries the tags during startup to populate a filter dropdown in the UI.

On most wikis this has no notable impact on load time as it runs in parallel with other requests (such as the list=recentchanges query). But on Wikidata this thing takes well over 10 seconds to load.

I suspect this is because of all the "OAuth CID: ##" entries.

Example:
https://www.wikidata.org/w/api.php?format=json&action=query&list=tags&tgprop=displayname&continue=

[1]
https://github.com/Krinkle/mw-gadget-rtrc/issues/46
https://www.wikidata.org/wiki/Special:BlankPage/RTRC
https://www.mediawiki.org/wiki/Special:BlankPage/RTRC?withModule=ext.gadget.rtrc&kickstart=1

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedNoneT105189 [Bug] Wikidata action=query&list=tags should not take 15-25 seconds to respond
 ResolvedLadsgroupT91535 Performance issues with tags
 ResolvedLadsgroupT185355 Normalize change tag schema
 ResolvedLadsgroupT164167 change_tag table needs redesign
 ResolvedLadsgroupT20672 Change Tags structure to use numeric IDs instead of text
 OpenNoneT91312 Evaluate refactoring of change_tags to only associate with rev id
 ResolvedLadsgroupT193867 Create the change_tag_def table and add the ct_tag_id field to change_tag
 ResolvedPRODUCTION ERRORLadsgroupT194302 Schema change for new change_tag_def
 ResolvedMarosteguiT195193 Schema change for ct_tag_id field to change_tag
 Resolved• BstormT199818 Replicate ct_tag_id to labs
 ResolvedLadsgroupT193868 Add code to write to change_tag_def table
 ResolvedLadsgroupT193874 Add new indexes to change_tag table using ct_tag_id instead of ct_tag
 ResolvedLadsgroupT194162 Add code to read from change_tag_def instead of change_tag.ct_tag
 ResolvedLadsgroupT194163 Drop change_tag.ct_tag column
 ResolvedMarosteguiT210713 Drop change_tag.ct_tag column in production
 ResolvedMarosteguiT234800 Switchover s1 primary database master db1067 -> db1083 - 14th Nov 06:00 - 06:30 UTC
 ResolvedTrizek-WMFT234801 Community Relations support needed for a read-only window for s1 (enwiki)
 ResolvedLadsgroupT194164 Start reading from change_tag_def in production
 ResolvedLadsgroupT196671 Start reading from change_tag_def in beta cluster
 ResolvedLadsgroupT194165 Start writing to change_tag_def in production
 ResolvedLadsgroupT199334 Temporarily add config and use it to use change_tag_def table instead of change_tag table for Special:Tags
 ResolvedLadsgroupT200064 Update documentation on mediawiki.org: Manual:Change_tag_table
 ResolvedLadsgroupT193873 Run maintenance script to populate change_tag_def on WMF production (all wikis)
 ResolvedLadsgroupT193871 Add maintenance script to populate change_tag_def
 ResolvedLadsgroupT208846 Start reading from change_tag_def on wikidatawiki
 ResolvedMarosteguiT203709 Schema change for adding indexes of ct_tag_id
 ResolvedLadsgroupT211896 Query trying to use the wrong index (change_tag_rev_tag) on change_tag

Event Timeline

Krinkle created this task.Jul 8 2015, 5:05 PM
Krinkle raised the priority of this task from to Needs Triage.
Krinkle updated the task description. (Show Details)
Krinkle added projects: Regression, Wikidata, MediaWiki-Action-API, MediaWiki-extensions-OAuth, MediaWiki-libs-Rdbms.
Krinkle subscribed.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 8 2015, 5:05 PM
Krinkle updated the task description. (Show Details)Jul 8 2015, 5:06 PM
Krinkle set Security to None.
Sjoerddebruin subscribed.Jul 8 2015, 5:14 PM
Anomie subscribed.Jul 8 2015, 5:59 PM

The slowness in the module is due to the database query to fetch the list of used tags:

SELECT ct_tag, 0 AS hitcount FROM change_tag GROUP BY ct_tag ORDER BY ct_tag LIMIT 11;

Strangely, increasing the limit seems to make it much faster. The query plans for the low-limit case versus the high-limit case look like:

> explain SELECT ct_tag, 0 AS hitcount FROM change_tag GROUP BY ct_tag ORDER BY ct_tag LIMIT 101;
+------+-------------+------------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id   | select_type | table      | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+------+-------------+------------+-------+---------------+-------------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | change_tag | range | NULL          | change_tag_tag_id | 257     | NULL |   35 | Using index for group-by |
+------+-------------+------------+-------+---------------+-------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

> explain SELECT ct_tag, 0 AS hitcount FROM change_tag GROUP BY ct_tag ORDER BY ct_tag LIMIT 11;
+------+-------------+------------+-------+---------------+-------------------+---------+------+----------+-------------+
| id   | select_type | table      | type  | possible_keys | key               | key_len | ref  | rows     | Extra       |
+------+-------------+------------+-------+---------------+-------------------+---------+------+----------+-------------+
|    1 | SIMPLE      | change_tag | index | NULL          | change_tag_tag_id | 272     | NULL | 31483031 | Using index |
+------+-------------+------------+-------+---------------+-------------------+---------+------+----------+-------------+
1 row in set (0.00 sec)

There's also another mode ( https://www.wikidata.org/w/api.php?format=json&action=query&list=tags&tgprop=displayname|hitcount&continue=) that does COUNT(*) AS hitcount instead, BTW. That one uses the slower plan in all cases.

BTW, I note that the module only returns a 7 OAuth entries, although one of them (cid 93) accounts for 83% of the 31176994 rows in the change_tags table and the seven together account for almost 94%.

Anomie moved this task from Unsorted to Needs details or plan on the MediaWiki-Action-API board.Jul 8 2015, 5:59 PM
Krinkle edited projects, added Performance Issue; removed MediaWiki-libs-Rdbms, MediaWiki-extensions-OAuth.Jul 12 2015, 11:42 AM
Sjoerddebruin added a comment.EditedJul 27 2015, 7:59 PM

Can this be given some priority? I would love to work faster again.

Lydia_Pintscher added subscribers: Lydia_Pintscher, hoo, Addshore, Bene.Aug 7 2015, 2:20 PM
Krinkle updated the task description. (Show Details)Sep 12 2015, 5:59 PM
Ricordisamoa subscribed.Sep 12 2015, 6:02 PM
Anomie mentioned this in T115825: action=query&list=pagepropnames really slow on a big wiki, got error with ppnlimit=500 function: /* ApiQueryPagePropNames::execute */.Oct 19 2015, 2:06 PM
Krinkle unsubscribed.Mar 22 2016, 10:44 PM
matej_suchanek added a subtask: T91535: Performance issues with tags.Mar 23 2016, 3:24 PM
Lydia_Pintscher renamed this task from Wikidata action=query&list=tags should not take 15-25 seconds to respond to [Bug] Wikidata action=query&list=tags should not take 15-25 seconds to respond.Apr 21 2016, 1:52 PM
Lydia_Pintscher triaged this task as Medium priority.
Sjoerddebruin mentioned this in T27909: Add a drop-down list for the tags in Special:Newpages, Special:Log and Special:Contributions.Sep 25 2016, 10:43 AM
Cenarium subscribed.Sep 25 2016, 11:53 AM

No other wiki has 25M+ hitcount tags, and querying for defined tags requires a query for used tags in OAuth (since it only defines tags with hits). The query for tag stats is systematically slow and has effectively no cache since each tag addition purges it.
https://gerrit.wikimedia.org/r/#/c/218265 aims to partially solve this issue by providing a cache invalidated only when a new tag is created or a tag is deleted. If one doesn't need precise hitcounts, as is the case for a drop down menu, or for OAuth, this is enough.

Ladsgroup subscribed.Apr 24 2017, 10:27 AM

For me, it now responded in 3.93 seconds. I think this is resolved.

Ladsgroup added a project: TestMe.Apr 24 2017, 10:27 AM
Restricted Application added a subscriber: TerraCodes. · View Herald TranscriptApr 24 2017, 10:27 AM
Addshore added a comment.Apr 24 2017, 3:43 PM

Just over 4 seconds for me

Ladsgroup closed this task as Resolved.Apr 25 2017, 5:34 AM

I resolved it, feel free to repoen.

Ladsgroup closed subtask T91535: Performance issues with tags as Resolved.Jul 25 2018, 5:07 PM
Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:42 PM
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