Page MenuHomePhabricator

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

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);

Event Timeline

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 moved this task from Next to In progress on the DBA board.

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)

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

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

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.

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?

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?