Possibly gradual roll out.
Description
Description
Details
Details
Customize query in gerrit
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T300222 Implement normalizing MediaWiki link tables | |||
| Resolved | • Ladsgroup | T299417 Normalize templatelinks table | |||
| Resolved | • Ladsgroup | T299421 Turn on write both in production for templatelinks normalization |
Event Timeline
Comment Actions
I'm going to turn this on in s6 wikis. Later in other wikis (but not on wikis that T300775: Add tl_target_id column to templatelinks is not done yet)
Comment Actions
Change 772816 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Enable WRITE BOTH for templatelinks normalization in wikitech
Comment Actions
Change 772816 merged by jenkins-bot:
[operations/mediawiki-config@master] Enable WRITE BOTH for templatelinks normalization in wikitech
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-03-22T12:12:12Z] <ladsgroup@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Config: [[gerrit:772816|Enable WRITE BOTH for templatelinks normalization in wikitech (T299421)]] (duration: 01m 41s)
Comment Actions
Change 772817 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Enable WRITE BOTH on rest of s6 for templatelinks normalization
Comment Actions
Change 772817 merged by jenkins-bot:
[operations/mediawiki-config@master] Enable WRITE BOTH on rest of s6 for templatelinks normalization
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-03-22T12:24:36Z] <ladsgroup@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Config: [[gerrit:772817|Enable WRITE BOTH on rest of s6 for templatelinks normalization (T299421)]] (duration: 00m 54s)
Comment Actions
Change 773594 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Enable WRITE BOTH for templatelinks normalization in more wikis
Comment Actions
Change 773594 merged by jenkins-bot:
[operations/mediawiki-config@master] Enable WRITE BOTH for templatelinks normalization in more wikis
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-03-28T08:46:52Z] <ladsgroup@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Config: [[gerrit:773594|Enable WRITE BOTH for templatelinks normalization in more wikis (T299421)]] (duration: 00m 54s)
Comment Actions
Change 774816 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set write both for all wikis except s1 and s4
Comment Actions
Change 774816 merged by jenkins-bot:
[operations/mediawiki-config@master] Set write both for all wikis except s1 and s4
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-03-29T13:27:22Z] <ladsgroup@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Config: [[gerrit:774816|Set write both for all wikis except s1 and s4 (T299421)]] (duration: 00m 55s)
Comment Actions
Change 779852 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set templatelinks migration schema to write both in s4
Comment Actions
Change 779852 merged by jenkins-bot:
[operations/mediawiki-config@master] Set templatelinks migration schema to write both in s4
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-04-13T13:30:48Z] <reedy@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Set templatelinks migration schema to write both in s4 - T299421 (duration: 00m 55s)
Comment Actions
Found another slow query in frwiki:
SELECT lt_title,tl_from_namespace,tl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_namespace,page_title,page_id,page_content_model,page_is_redirect,page_is_new,page_latest,page_touched,page_len,page_restrictions FROM `page`,`templatelinks` FORCE INDEX (tl_backlinks_namespace_target_id) JOIN `linktarget` ON ((tl_target_id=lt_id)) WHERE tl_from_namespace IN (0,1,2,3,4,5,12,13) AND (tl_from = page_id) AND ((lt_namespace = 10 AND lt_title IN ('SPE','Semi-protection_étendue') )) AND page_is_redirect = 0 ORDER BY lt_title,tl_from_namespace,tl_from LIMIT 501I'll see what I can do to make it faster.
Comment Actions
Explain:
MariaDB [frwiki]> explain SELECT lt_title,tl_from_namespace,tl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_namespace,page_title,page_id,page_content_model,page_is_redirect,page_is_new,page_latest,page_touched,page_len,page_restrictions FROM `page`,`templatelinks` FORCE INDEX (tl_backlinks_namespace_target_id) JOIN `linktarget` ON ((tl_target_id=lt_id)) WHERE tl_from_namespace IN
(0,1,2,3,4,5,12,13) AND (tl_from = page_id) AND ((lt_namespace = 10 AND lt_title IN ('SPE','Semi-protection_étendue') )) AND page_is_redirect = 0 ORDER BY lt_title,tl_from_namespace,tl_from LIMIT 5 ;
+------+-------------+---------------+------------+-------------------------------------+--------------------------------------------+---------+------------------------------+-----------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------+------------+-------------------------------------+--------------------------------------------+---------+------------------------------+-----------+---------------------------------------------------------------+
| 1 | SIMPLE | linktarget | range | PRIMARY,lt_namespace_title | lt_namespace_title | 261 | NULL | 2 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | templatelinks | hash_range | tl_backlinks_namespace_target_id | #hash#$hj:tl_backlinks_namespace_target_id | 9:4 | frwiki.linktarget.lt_id | 191341573 | Using where; Using index; Using join buffer (flat, BNLH join) |
| 1 | SIMPLE | page | eq_ref | PRIMARY,page_redirect_namespace_len | PRIMARY | 4 | frwiki.templatelinks.tl_from | 1 | Using where |
+------+-------------+---------------+------------+-------------------------------------+--------------------------------------------+---------+------------------------------+-----------+---------------------------------------------------------------+
3 rows in set (0.057 sec)Comment Actions
Join decomposition makes it instant:
MariaDB [frwiki]> SELECT lt_title,tl_from_namespace,tl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_namespace,page_title,page_id,page_content_model,page_is_redirect,page_is_new,page_latest,page_touched,page_len,page_restrictions FROM `page`,`templatelinks` FORCE INDEX (tl_backlinks_namespace_target_id) JOIN `linktarget` ON ((tl_target_id=lt_id)) WHERE tl_from_namespace IN (0,1,2,3,4,5,12,13) AND (tl_from = page_id) AND tl_target_id IN (626975, 5802) AND page_is_redirect = 0 ORDER BY lt_title,tl_from_namespace,tl_from LIMIT 501; ... 298 rows in set (0.006 sec)
Comment Actions
FWIW subquery didn't fix it:
SELECT lt_title,tl_from_namespace,tl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_namespace,page_title,page_id,page_content_model,page_is_redirect,page_is_new,page_latest,page_touched,page_len,page_restrictions FROM `page`,`templatelinks` FORCE INDEX (tl_backlinks_namespace_target_id) JOIN `linktarget` ON ((tl_target_id=lt_id)) WHERE tl_from_namespace IN (0,1,2,3,4,5,12,13) AND (tl_from = page_id) AND tl_target_id in (select lt_id from linktarget where (lt_namespace = 10 AND lt_title IN ('SPE','Semi-protection_étendue') )) AND page_is_redirect = 0 ORDER BY lt_title,tl_from_namespace,tl_from LIMIT 501Comment Actions
Change 793763 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Turn on WRITE BOTH for templatelink migration in enwiki
Comment Actions
Change 793763 merged by jenkins-bot:
[operations/mediawiki-config@master] Turn on WRITE BOTH for templatelink migration in enwiki
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2022-05-23T06:10:43Z] <ladsgroup@deploy1002> Synchronized wmf-config/InitialiseSettings.php: Config: [[gerrit:793763|Turn on WRITE BOTH for templatelink migration in enwiki (T299421)]] (duration: 00m 51s)