Drop ct_ indexes on change_tag
Closed, ResolvedPublic

Description

While troubleshooting T205904 we found that there is a patch that was merged 9 years ago but never got deployed in production: https://github.com/wikimedia/mediawiki/blob/master/maintenance/archives/patch-change_tag-indexes.sql

That touches a few things, but for now, we are going to focus on the change_tag table as we are doing some work already with it: T203709

The following indexes are now redudant:

UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),

KEY `change_tag_rc_tag_nonuniq` (`ct_rc_id`,`ct_tag`),
KEY `change_tag_log_tag_nonuniq` (`ct_log_id`,`ct_tag`),
KEY `change_tag_rev_tag_nonuniq` (`ct_rev_id`,`ct_tag`)
KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),

So we need to drop them:

DROP INDEX ct_rc_id ON /*_*/change_tag;
DROP INDEX ct_log_id ON /*_*/change_tag;
DROP INDEX ct_rev_id ON /*_*/change_tag;
DROP INDEX ct_tag ON /*_*/change_tag;

ct_tag exists on some hosts, and not on others, so maybe that one was deployed. Who knows what happened 9 years ago.

This needs to be dropped in eqiad first, and once we are back in eqiad, finish it on codfw:

s1

s2

  • eqiad
  • codfw

s3

  • eqiad (only a few wikis needed it)
  • codfw

s4

  • eqiad
  • codfw

s5

  • eqiad
  • codfw

s6

  • eqiad
  • codfw

s7

  • eqiad
  • codfw

s8 - not needed T205913#4631293

labswiki (wikitech) - not needed T205913#4631293

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 1 2018, 6:57 PM
Marostegui triaged this task as High priority.Oct 1 2018, 6:57 PM

Setting it to high as we need to do this before we do the failover back to eqiad

Marostegui added a comment.EditedOct 1 2018, 7:07 PM

Wikis created after 2009 will not need this change, so for instance, s8 (wikidata) doesn't need it.

root@neodymium:~# mysql.py -hdb1071 wikidatawiki -e "show create table change_tag\G" | grep -i key
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `change_tag_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
  UNIQUE KEY `change_tag_log_tag_id` (`ct_log_id`,`ct_tag_id`),
  UNIQUE KEY `change_tag_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
  KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
  KEY `change_tag_rc_tag_nonuniq` (`ct_rc_id`,`ct_tag`),
  KEY `change_tag_log_tag_nonuniq` (`ct_log_id`,`ct_tag`),
  KEY `change_tag_rev_tag_nonuniq` (`ct_rev_id`,`ct_tag`)
Marostegui updated the task description. (Show Details)Oct 1 2018, 7:11 PM

As I did with T203709 I will deploy this change on some enwiki slaves of codfw to see if we catch queries with any of those indexes hardcoded.

Marostegui moved this task from Triage to In progress on the DBA board.Oct 2 2018, 5:46 AM

Change 463897 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2072

https://gerrit.wikimedia.org/r/463897

Change 463897 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2072

https://gerrit.wikimedia.org/r/463897

Mentioned in SAL (#wikimedia-operations) [2018-10-02T07:18:51Z] <marostegui> Deploy schema change on db2072 T205913

Marostegui added a comment.EditedOct 2 2018, 7:21 AM

s1 progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore2002
  • dbstore1002
  • dbstore1001
  • db2094
  • db2092
  • db2088
  • db2085
  • db2072
  • db2071
  • db2070
  • db2062
  • db2055
  • db2048
  • db1124
  • db1119
  • db1118 (replication broken - will not get it - test host)
  • db1114
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
  • db1067
Marostegui updated the task description. (Show Details)Oct 2 2018, 7:25 AM

Mentioned in SAL (#wikimedia-operations) [2018-10-02T07:36:53Z] <marostegui> Deploy schema change on db2088:3311 T205913

Mentioned in SAL (#wikimedia-operations) [2018-10-02T07:50:21Z] <marostegui> Deploy schema change on db2071 T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 7:53 AM
Marostegui added a comment.EditedOct 2 2018, 7:56 AM

@Ladsgroup I have altered the following active slaves in codfw:

db2088 - recentchanges
db2071 - api+small main traffic
db2072 - main traffic
db2094 (sanitarium host, no traffic, it just replicates from db2072)

The idea is to monitor those in the next few hours (https://logstash.wikimedia.org/goto/d7580cc63b4a18484d9c61c456d04bb3) and see if we get some hard-coded queries using any of those dropped indexes.

Meanwhile, I will start altering small wikis in eqiad (s2,s5 and s6) to advance on that front too.

Mentioned in SAL (#wikimedia-operations) [2018-10-02T08:04:12Z] <marostegui> Deploy schema change on s5 eqiad master, lag will be generated T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 8:09 AM

Mentioned in SAL (#wikimedia-operations) [2018-10-02T08:28:08Z] <marostegui> Deploy schema change on s6 eqiad master, lag will be generated T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 8:36 AM

Mentioned in SAL (#wikimedia-operations) [2018-10-02T09:30:01Z] <marostegui> Deploy schema change on s2 eqiad master, lag will be generated T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 9:43 AM

Altered also db2092 on enwiki, so we have another host to look for errors with hard coded queries (if any) : https://logstash.wikimedia.org/goto/2695c9487fb884e9001c8155dbce12e5

Mentioned in SAL (#wikimedia-operations) [2018-10-02T13:06:56Z] <marostegui> Deploy schema change on s7 eqiad, this will generate lag on eqiad - T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 1:26 PM

Mentioned in SAL (#wikimedia-operations) [2018-10-02T13:46:58Z] <marostegui> Deploy schema change on s4 eqiad, this will generate lag on eqiad - T205913

Marostegui updated the task description. (Show Details)Oct 2 2018, 1:48 PM
Marostegui updated the task description. (Show Details)Oct 2 2018, 1:54 PM

Change 464094 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2070

https://gerrit.wikimedia.org/r/464094

Change 464094 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2070

https://gerrit.wikimedia.org/r/464094

Mentioned in SAL (#wikimedia-operations) [2018-10-03T05:20:44Z] <marostegui> Deploy schema change on db2070 - T205913

Mentioned in SAL (#wikimedia-operations) [2018-10-03T05:21:42Z] <marostegui> Deploy schema change on db1075 (s3 eqiad master), lag will be generated - T205913

Marostegui updated the task description. (Show Details)Oct 3 2018, 5:25 AM

Mentioned in SAL (#wikimedia-operations) [2018-10-03T05:26:26Z] <marostegui> Deploy schema change on db1067 (s1 eqiad master), lag will be generated - T205913

Marostegui updated the task description. (Show Details)Oct 3 2018, 5:27 AM

Change 464099 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2085:3111

https://gerrit.wikimedia.org/r/464099

Change 464099 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2085:3111

https://gerrit.wikimedia.org/r/464099

Mentioned in SAL (#wikimedia-operations) [2018-10-03T05:57:28Z] <marostegui> Deploy schema change on db2085:3311 - T205913

Change 464102 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2055

https://gerrit.wikimedia.org/r/464102

Change 464102 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2055

https://gerrit.wikimedia.org/r/464102

Mentioned in SAL (#wikimedia-operations) [2018-10-03T06:37:40Z] <marostegui> Deploy schema change on db2055 - T205913

Change 464479 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2062

https://gerrit.wikimedia.org/r/464479

Change 464479 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2062

https://gerrit.wikimedia.org/r/464479

Mentioned in SAL (#wikimedia-operations) [2018-10-04T05:30:32Z] <marostegui> Deploy schema change on db2062 - T205913

Mentioned in SAL (#wikimedia-operations) [2018-10-04T05:36:20Z] <marostegui> Deploy schema change on db2048 (s1 master) - T205913

Marostegui updated the task description. (Show Details)Oct 4 2018, 5:37 AM
Marostegui moved this task from In progress to Next on the DBA board.Oct 24 2018, 9:55 AM
Marostegui changed the task status from Open to Stalled.
Marostegui changed the task status from Stalled to Open.Nov 12 2018, 9:09 AM
Marostegui moved this task from Next to In progress on the DBA board.

T203709: Schema change for adding indexes of ct_tag_id is now done, so I am going to proceed with this one //cc @Ladsgroup

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:08:08Z] <marostegui> Deploy schema change on s3 codfw master, this will generate lag on s3 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:12 AM

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:14:32Z] <marostegui> Deploy schema change on s4 codfw master, this will generate lag on s4 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:15 AM

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:17:48Z] <marostegui> Deploy schema change on s6 codfw master, this will generate lag on s6 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:18 AM

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:19:18Z] <marostegui> Deploy schema change on s2 codfw master, this will generate lag on s2 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:20 AM

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:22:19Z] <marostegui> Deploy schema change on s2 codfw master, this will generate lag on s7 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:23 AM

Mentioned in SAL (#wikimedia-operations) [2018-11-14T08:24:29Z] <marostegui> Deploy schema change on s5 codfw master, this will generate lag on s5 codfw - T205913

Marostegui updated the task description. (Show Details)Nov 14 2018, 8:24 AM
Marostegui closed this task as Resolved.

This is all done