Page MenuHomePhabricator

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

Related Objects

StatusSubtypeAssignedTask
ResolvedMarostegui
ResolvedLadsgroup
ResolvedNone
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedReedy
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
DeclinedNone
ResolvedMarostegui
ResolvedMarostegui
ResolvedLadsgroup
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedLadsgroup
ResolvedMarostegui
ResolvedMarostegui
ResolvedLadsgroup
ResolvedKormat
ResolvedMarostegui
ResolvedKormat
ResolvedMarostegui
ResolvedKormat
ResolvedMarostegui

Event Timeline

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

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

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.

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

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

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

@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

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

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

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

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

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

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

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 changed the task status from Open to Stalled.Oct 24 2018, 9:55 AM
Marostegui moved this task from In progress to Pending comment on the DBA board.
Marostegui changed the task status from Stalled to Open.Nov 12 2018, 9:09 AM
Marostegui moved this task from Pending comment to In progress on the DBA board.

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

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

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

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

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

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)

This is all done