After data migration is done for that wiki.
Progress:
- Beta
- s1
- s2: All wikis done except trwiki and zhwiki
- s3:
- s4
- s5
- s6
- s7
- s8
- labtestwiki
After data migration is done for that wiki.
Progress:
Status | Subtype | Assigned | Task | ||
---|---|---|---|---|---|
Open | None | T300222 Implement normalizing MediaWiki link tables | |||
Open | None | T299947 Normalize pagelinks table | |||
Open | Ladsgroup | T351237 Set beta and production to read new for pagelinks migration |
Mentioned in SAL (#wikimedia-operations) [2023-11-20T16:48:08Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:975806|Set pagelinks migration to read new in testwiki, fawikiquote, cebwiki (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2023-11-20T16:56:57Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:975806|Set pagelinks migration to read new in testwiki, fawikiquote, cebwiki (T351237)]] (duration: 10m 06s)
s5 is done, let's do the biggest wikis
root@db1185:/srv/sqldata# ls -Ssh */pagelinks.ibd | head 82G dewiki/pagelinks.ibd 43G srwiki/pagelinks.ibd 22G shwiki/pagelinks.ibd 16G cebwiki/pagelinks.ibd
Change 980370 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set migration of pagelinks on large wikis of s5 to read new
Change 980370 merged by jenkins-bot:
[operations/mediawiki-config@master] Set migration of pagelinks on large wikis of s5 to read new
Mentioned in SAL (#wikimedia-operations) [2023-12-05T12:34:42Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:980370|Set migration of pagelinks on large wikis of s5 to read new (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2023-12-05T12:37:13Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:980370|Set migration of pagelinks on large wikis of s5 to read new (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2023-12-05T12:47:13Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:980370|Set migration of pagelinks on large wikis of s5 to read new (T351237)]] (duration: 12m 30s)
Setting dewiki to read new caused an uptick of slow queries like this: https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-mediawiki-1-7.0.0-1-2023.12.05?id=6PDDOowBRtLP5wy63J-T
An example of this query is:
SELECT lt_title,pl_from_namespace,pl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_title,page_namespace FROM `page`,`pagelinks` FORCE INDEX (pl_backlinks_namespace_target_id) JOIN `linktarget` ON ((pl_target_id=lt_id)) WHERE pl_from_namespace IN (0,14) AND (lt_title > 'Schweizerische_Bundesbahnen' OR (lt_title = 'Schweizerische_Bundesbahnen' AND (pl_from_namespace > 0 OR (pl_from_namespace = 0 AND (pl_from >= 862825))))) AND (pl_from = page_id) AND ((lt_namespace = 0 AND lt_title IN ('BLS_Re_4/4','Doppelstockwagen','Einheitswagen_(Schweiz,_Normalspur)','InterRegio_(Schweiz)','Liste_der_Lokomotiven_und_Triebwagen_der_SBB','Liste_der_Werbelokomotiven_der_Schweizerischen_Bundesbahnen','SBB_Re_4/4_II','SBB_Re_4/4_IV','Schweizerische_Bundesbahnen','Sicherheitsfahrschaltung') )) ORDER BY lt_title,pl_from_namespace,pl_from LIMIT 501 ;
explain:
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: linktarget type: range possible_keys: PRIMARY,lt_namespace_title key: lt_namespace_title key_len: 261 ref: NULL rows: 2 Extra: Using where; Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: pagelinks type: hash_range possible_keys: pl_backlinks_namespace_target_id key: #hash#$hj:pl_backlinks_namespace_target_id key_len: 9:4 ref: dewiki.linktarget.lt_id rows: 99307427 Extra: Using where; Using index; Using join buffer (flat, BNLH join) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: page type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: dewiki.pagelinks.pl_from rows: 1 Extra: 3 rows in set (0.002 sec)
Which has many issues but it seems the main issue starts from continuation I think. We probably should change continue params to switch to pl_target_id instead. Weirdly just switching to the target ids makes it fast:
explain SELECT lt_title,pl_from_namespace,pl_from,lt_namespace AS `bl_namespace`,lt_title AS `bl_title`,page_title,page_namespace FROM `page` JOIN `pagelinks` FORCE INDEX (pl_backlinks_namespace_target_id) on pl_from = page_id JOIN `linktarget` ON ((pl_target_id=lt_id)) WHERE pl_from_namespace IN (0,14) AND (lt_title > 'Schweizerische_Bundesbahnen' OR (lt_title = 'Schweizerische_Bundesbahnen' AND (pl_from_namespace > 0 OR (pl_from_namespace = 0 AND (pl_from >= 862825))))) AND ((lt_id IN (3174395, 1123856,1255584,744533,610455,3174396,1471155,2050921,634119, 827643 ) )) ORDER BY lt_title,pl_from_namespace,pl_from LIMIT 501 ;
That also can be considered.
The problem is actually not continue, it's the target ids ((lt_namespace = 0 AND lt_title IN ('BLS_Re_4/4','Doppelstockwagen','Einheitswagen_(Schweiz,_Normalspur)','InterRegio_(Schweiz)','Liste_der_Lokomotiven_und_Triebwagen_der_SBB','Liste_der_Werbelokomotiven_der_Schweizerischen_Bundesbahnen','SBB_Re_4/4_II','SBB_Re_4/4_IV','Schweizerische_Bundesbahnen','Sicherheitsfahrschaltung') )
I need to make this do the look up on the fly.
KILL ME. This happens only when pl_from_namespace has more than one value in which the forced index becomes its own enemy and turn it into a range look up. Patch coming.
Change 980919 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[mediawiki/core@master] api: Only force backlink namespace index when there is one ns only
Change 980919 merged by jenkins-bot:
[mediawiki/core@master] api: Only force backlink namespace index when there is one ns only
Change 980483 had a related patch set uploaded (by Jforrester; author: Amir Sarabadani):
[mediawiki/core@wmf/1.42.0-wmf.7] api: Only force backlink namespace index when there is one ns only
Change 980483 merged by jenkins-bot:
[mediawiki/core@wmf/1.42.0-wmf.7] api: Only force backlink namespace index when there is one ns only
Mentioned in SAL (#wikimedia-operations) [2023-12-07T13:38:50Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:980483|api: Only force backlink namespace index when there is one ns only (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2023-12-07T13:40:25Z] <ladsgroup@deploy2002> jforrester and ladsgroup: Backport for [[gerrit:980483|api: Only force backlink namespace index when there is one ns only (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2023-12-07T13:49:46Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:980483|api: Only force backlink namespace index when there is one ns only (T351237)]] (duration: 10m 55s)
Slow queries are gone but I found a missing table def somewhere else (https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-deploy-1-7.0.0-1-2023.12.07?id=z9SIRIwBk70gu8GmcB3U)
Error 1054: Unknown column 'lt_title' in 'field list' Function: ApiQueryBacklinks::runSecondQuery Query: SELECT /*! STRAIGHT_JOIN */ page_id,page_title,page_namespace,page_is_redirect,lt_title,page_namespace AS `from_ns`,lt_namespace FROM `pagelinks`,`page` FORCE INDEX (PRIMARY) WHERE (pl_from=page_id) AND (((lt_title = 'Leopardkatze' AND lt_namespace = 0) OR (lt_title = 'Tsushima-Katze' AND lt_namespace = 0) OR (lt_title = 'Bengal-Katze' AND lt_namespace = 0) OR (lt_title = 'Prionailurus_bengalensis' AND lt_namespace = 0) OR (lt_title = 'Amurkatze' AND lt_namespace = 0))) ORDER BY lt_title,pl_from LIMIT 251
Change 981338 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[mediawiki/core@master] api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery
Change 981338 merged by jenkins-bot:
[mediawiki/core@master] api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery
Change 981737 had a related patch set uploaded (by Jforrester; author: Amir Sarabadani):
[mediawiki/core@wmf/1.42.0-wmf.7] api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery
Change 981737 merged by jenkins-bot:
[mediawiki/core@wmf/1.42.0-wmf.7] api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery
Mentioned in SAL (#wikimedia-operations) [2023-12-11T22:12:21Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:981737|api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2023-12-11T22:13:59Z] <ladsgroup@deploy2002> jforrester and ladsgroup: Backport for [[gerrit:981737|api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2023-12-11T22:23:03Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:981737|api: Add support for pagelinks migration in ApiQueryBacklinks::runSecondQuery (T351237)]] (duration: 10m 42s)
Change 987657 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set commonswiki pagelinks migration stage to READ NEW
Change 987657 merged by jenkins-bot:
[operations/mediawiki-config@master] Set commonswiki pagelinks migration stage to READ NEW
Mentioned in SAL (#wikimedia-operations) [2024-01-08T10:02:52Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:987657|Set commonswiki pagelinks migration stage to READ NEW (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-01-08T10:04:17Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:987657|Set commonswiki pagelinks migration stage to READ NEW (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-01-08T10:11:45Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:987657|Set commonswiki pagelinks migration stage to READ NEW (T351237)]] (duration: 08m 52s)
Change 997420 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Switch the pagelinks default to add read new
Change 997420 merged by jenkins-bot:
[operations/mediawiki-config@master] Switch the pagelinks default to add read new
Mentioned in SAL (#wikimedia-operations) [2024-02-06T11:21:04Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:997420|Switch the pagelinks default to add read new (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-02-06T11:22:37Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:997420|Switch the pagelinks default to add read new (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-02-06T11:31:43Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:997420|Switch the pagelinks default to add read new (T351237)]] (duration: 10m 38s)
Change 1004614 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set fawiki to read new in pagelinks
Change 1004614 merged by jenkins-bot:
[operations/mediawiki-config@master] Set fawiki to read new in pagelinks
Mentioned in SAL (#wikimedia-operations) [2024-02-19T08:51:42Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:1004614|Set fawiki to read new in pagelinks (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-02-19T08:53:06Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:1004614|Set fawiki to read new in pagelinks (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-02-19T09:01:26Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:1004614|Set fawiki to read new in pagelinks (T351237)]] (duration: 09m 43s)
Change 1006853 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set three more wikis to read new on pagelinks migration
Change 1006853 merged by jenkins-bot:
[operations/mediawiki-config@master] Set three more wikis to read new on pagelinks migration
Mentioned in SAL (#wikimedia-operations) [2024-02-28T09:44:27Z] <ladsgroup@deploy2002> Started scap: Backport for [[gerrit:1006853|Set three more wikis to read new on pagelinks migration (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-02-28T09:45:55Z] <ladsgroup@deploy2002> ladsgroup: Backport for [[gerrit:1006853|Set three more wikis to read new on pagelinks migration (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-02-28T09:54:30Z] <ladsgroup@deploy2002> Finished scap: Backport for [[gerrit:1006853|Set three more wikis to read new on pagelinks migration (T351237)]] (duration: 10m 03s)
Change 1008503 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set two more wikis to read new for pagelinks migration
Change 1008503 merged by jenkins-bot:
[operations/mediawiki-config@master] Set two more wikis to read new for pagelinks migration
Mentioned in SAL (#wikimedia-operations) [2024-03-06T12:42:04Z] <jnuche@deploy2002> Started scap: Backport for [[gerrit:1008503|Set two more wikis to read new for pagelinks migration (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-03-06T12:45:36Z] <jnuche@deploy2002> jnuche and ladsgroup: Backport for [[gerrit:1008503|Set two more wikis to read new for pagelinks migration (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-03-06T12:55:25Z] <jnuche@deploy2002> Finished scap: Backport for [[gerrit:1008503|Set two more wikis to read new for pagelinks migration (T351237)]] (duration: 13m 20s)
Change 1013010 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set three more wikis to read new in pagelinks migration
Change #1013010 merged by jenkins-bot:
[operations/mediawiki-config@master] Set four more wikis to read new in pagelinks migration
Mentioned in SAL (#wikimedia-operations) [2024-03-25T10:50:06Z] <ladsgroup@deploy1002> Started scap: Backport for [[gerrit:1013010|Set four more wikis to read new in pagelinks migration (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-03-25T10:52:32Z] <ladsgroup@deploy1002> ladsgroup: Backport for [[gerrit:1013010|Set four more wikis to read new in pagelinks migration (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-03-25T11:03:59Z] <ladsgroup@deploy1002> Finished scap: Backport for [[gerrit:1013010|Set four more wikis to read new in pagelinks migration (T351237)]] (duration: 13m 13s)
Change #1019680 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):
[operations/mediawiki-config@master] Set all wikis to read new for pagelinks migration except trwiki, zhwiki
Change #1019680 merged by jenkins-bot:
[operations/mediawiki-config@master] Set all wikis to read new for pagelinks migration except trwiki, zhwiki
Mentioned in SAL (#wikimedia-operations) [2024-04-15T09:00:31Z] <ladsgroup@deploy1002> Started scap: Backport for [[gerrit:1019680|Set all wikis to read new for pagelinks migration except trwiki, zhwiki (T351237)]]
Mentioned in SAL (#wikimedia-operations) [2024-04-15T09:02:33Z] <ladsgroup@deploy1002> ladsgroup: Backport for [[gerrit:1019680|Set all wikis to read new for pagelinks migration except trwiki, zhwiki (T351237)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)
Mentioned in SAL (#wikimedia-operations) [2024-04-15T09:09:23Z] <ladsgroup@deploy1002> Finished scap: Backport for [[gerrit:1019680|Set all wikis to read new for pagelinks migration except trwiki, zhwiki (T351237)]] (duration: 08m 51s)
Mentioned in SAL (#wikimedia-operations) [2024-04-15T09:14:56Z] <cgoubert@deploy1002> Started scap: T351237
Mentioned in SAL (#wikimedia-operations) [2024-04-15T09:26:39Z] <cgoubert@deploy1002> Finished scap: T351237 (duration: 11m 43s)