Page MenuHomePhabricator
Create Task
Maniphest T250060

tl_namespace index on templatelinks is unique only in s8
Closed, ResolvedPublic
Actions

Description

"templatelinks tl_namespace index-uniqueness-mismatch": {
    "s8": [
        "db1109.eqiad.wmnet",
        "db1104.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1111.eqiad.wmnet",
        "db1087.eqiad.wmnet",
        "db1126.eqiad.wmnet",
        "db1099.eqiad.wmnet"
    ]
},

It should not be unique: https://gerrit.wikimedia.org/g/mediawiki/core/+/7c077ba897075b1a79fab79266131757eeedd04e/maintenance/tables.sql#786 but it should exist

Progress:

  • Codfw (to be done with replication)

Eqiad:

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1114
  • db1087

To run:

alter table templatelinks drop index if exists tl_namespace, add index if not exists tl_namespace (tl_namespace,tl_title,tl_from);

Related Objects
Search...

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
· · ·
ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
ResolvedMarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
· · ·

Event Timeline

Ladsgroup created this task.Apr 13 2020, 9:35 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 13 2020, 9:35 AM
Ladsgroup renamed this task from tl_namespace index on templatelinks is not unique only in s8 to tl_namespace index on templatelinks is unique only in s8.Apr 13 2020, 9:36 AM
Ladsgroup updated the task description. (Show Details)
Marostegui triaged this task as Medium priority.Apr 13 2020, 11:48 AM
Marostegui updated the task description. (Show Details)
Marostegui added a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.
Marostegui moved this task from Triage to Pending comment on the DBA board.
Ladsgroup mentioned this in T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Apr 13 2020, 12:39 PM
Marostegui claimed this task.Apr 20 2020, 5:48 AM
Marostegui moved this task from Pending comment to In progress on the DBA board.
Stashbot added a comment.Apr 20 2020, 5:50 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-20T05:50:22Z] <marostegui> Deploy schema change on s8 codfw - lag will show up T250060

Marostegui updated the task description. (Show Details)Apr 20 2020, 5:51 AM

codfw was done and looks like:

root@db2079.codfw.wmnet[wikidatawiki]> show create table templatelinks\G
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(10) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.03 sec)
Stashbot added a comment.Apr 20 2020, 5:53 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-20T05:53:55Z] <marostegui> Deploy schema change on s8 eqiad hosts T250060

Marostegui updated the task description. (Show Details)Apr 20 2020, 5:59 AM
Marostegui updated the task description. (Show Details)Apr 20 2020, 6:23 AM
Marostegui updated the task description. (Show Details)Apr 20 2020, 7:01 AM
Marostegui closed this task as Resolved.Apr 20 2020, 7:15 AM

Looks like the following query needs changes:

SELECT  /*! STRAIGHT_JOIN */ tl_from,tl_namespace AS `bl_namespace`,tl_title AS `bl_title`,page_title,page_namespace  FROM `templatelinks` FORCE INDEX (tl_namespace),`page`    WHERE (tl_from = page_id) AND ((tl_namespace = 4 AND tl_title = 'Database_reports/Identified_duplicates/2')) AND page_is_redirect = 0  ORDER BY tl_from LIMIT 501

We'd need that FORCE INDEX (tl_namespace) to become FORCE INDEX (tl_backlinks_namespace)

Marostegui reopened this task as Open.Apr 20 2020, 7:15 AM
Stashbot added a comment.Apr 20 2020, 7:20 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-20T07:20:58Z] <marostegui> Re add tl_namespace index to db1104 and db1092 - T250060

Marostegui added a comment.Apr 20 2020, 7:21 AM

Index re-added to the API hosts db1092 and db1104 as they were the ones giving errors:

root@db1104.eqiad.wmnet[wikidatawiki]> set session sql_log_bin=0; alter table templatelinks add index if not exists  tl_namespace (tl_namespace,tl_title,tl_from);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (9.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1104.eqiad.wmnet[wikidatawiki]> show create table templatelinks\G
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(10) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
Marostegui updated the task description. (Show Details)Apr 20 2020, 7:28 AM
Marostegui closed this task as Resolved.Apr 20 2020, 7:37 AM

Index re-added everywhere. There was a misunderstanding on whether the index had to remain and only the UNIQUE was to be removed. My bad!. Fixed it.
All done

On the other hand we might want to remove the FORCE INDEX as the optimizer now seems to be choosing the right index for it.
I will also test 10.4 and create a task to follow this up if needs removal in the end.

Marostegui mentioned this in T250654: Remove FORCE INDEX (tl_namespace) from an STRAIGHT_JOIN on an API query against templatelinks table.Apr 20 2020, 7:45 AM
Ladsgroup mentioned this in T256685: pl_namespace index on pagelinks is unique only in s8.Jun 29 2020, 8:32 PM
Ladsgroup added a comment.Oct 13 2020, 12:27 PM

This and imagelinks.il_to are not done in enwikivoyage (s5), I assume they fell into cracks while moving these wikis from s3 to s5?

Marostegui added a comment.Oct 13 2020, 12:48 PM
In T250060#6538606, @Ladsgroup wrote:

This and imagelinks.il_to are not done in enwikivoyage (s5), I assume they fell into cracks while moving these wikis from s3 to s5?

Most likely, do you mind creating a new task for it?

Ladsgroup added a comment.Oct 13 2020, 12:49 PM

Sure!

Ladsgroup mentioned this in T265445: Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5.Oct 14 2020, 5:09 AM
Ladsgroup mentioned this in T312538: Collect list of tickets done for fixing core drifts .Jul 7 2022, 12:59 PM
Maintenance_bot moved this task from In progress to Done on the DBA board.Jul 7 2022, 1:30 PM
Content licensed under Creative Commons Attribution-ShareAlike 4.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or 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