Page MenuHomePhabricator
Create Task
Maniphest T205913

Drop ct_ indexes on change_tag
Closed, ResolvedPublic
Actions

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

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
db-codfw.php: Depool db2062operations/mediawiki-configmaster+2 -2
db-codfw.php: Depool db2055operations/mediawiki-configmaster+4 -4
db-codfw.php: Depool db2085:3111operations/mediawiki-configmaster+6 -6
db-codfw.php: Depool db2070operations/mediawiki-configmaster+1 -1
db-codfw.php: Depool db2072operations/mediawiki-configmaster+1 -1
Customize query in gerrit

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedMarosteguiT205913 Drop ct_ indexes on change_tag
 ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
 ResolvedNoneT57455 Provide dynamic report of differences between replica databases and production databases
 ResolvedMarosteguiT51199 Add index log_type_action
 ResolvedMarosteguiT217397 logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
 ResolvedMarosteguiT250055 Remove image.img_deleted column from production
 ResolvedMarosteguiT250067 user_newtalk has two indexes not renamed in s4
 ResolvedMarosteguiT250062 ipb_parent_block_id_2 index on ipblocks table on s8 only
 ResolvedMarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
 ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
 ResolvedMarosteguiT249973 db1110 has 5 important database drifts that are unique to the host
 ResolvedMarosteguiT250056 lc_lang_key index is lingering in production
 ResolvedMarosteguiT250057 type_acton index in logging table is lingering in production
 DeclinedNoneT250058 searchindex indexes are missing in production
 ResolvedMarosteguiT250063 inverse_timestamp column exists in text table, it shouldn't
 ResolvedMarosteguiT250066 text table still has old_* fields and indexes on some hosts
 ResolvedLadsgroupT250071 Rename ipb_address index on ipb_address to ipb_address_unique
 ResolvedMarosteguiT252126 tl_from index on templatelinks is lingering in production
 ResolvedMarosteguiT256679 text table in db1144 drifts from core considerably
 ResolvedMarosteguiT256680 imagelinks has index mismatch on s8
 ResolvedMarosteguiT256682 page_restrictions indexes have been majestically drifting from code
 ResolvedMarosteguiT256684 pl_from index still lingers in random hosts
 ResolvedMarosteguiT256685 pl_namespace index on pagelinks is unique only in s8
 ResolvedMarosteguiT260111 All sorts of random drifts in wikis in s3
 ResolvedLadsgroupT265321 ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
 ResolvedMarosteguiT265349 querycache qc_type and qc_title have different nullabality on s1 only
 ResolvedMarosteguiT265445 Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
 ResolvedLadsgroupT269348 wikitech database has almost all of its varbinary fields wrong
 Resolved• KormatT277118 iw_url in interwiki is varbinary(127) in production but blob in code
 ResolvedMarosteguiT277354 "chemical" major mime type was never added to production database
 Resolved• KormatT278619 ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
 ResolvedMarosteguiT278621 job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
 Resolved• KormatT277116 fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
 ResolvedMarosteguiT308206 Slow query on echo_event table

Event Timeline

Marostegui created this task.Oct 1 2018, 6:57 PM
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 parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Oct 1 2018, 6:58 PM
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
Marostegui mentioned this in T205904: Key 'change_tag_rev_tag' doesn't exist in table 'change_tag'.Oct 1 2018, 7:22 PM
Marostegui added a comment.Oct 2 2018, 5:20 AM

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
gerritbot subscribed.Oct 2 2018, 7:10 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

gerritbot added a project: Patch-For-Review.Oct 2 2018, 7:10 AM
gerritbot added a comment.Oct 2 2018, 7:16 AM

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

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

Stashbot subscribed.Oct 2 2018, 7:18 AM

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
Stashbot added a comment.Oct 2 2018, 7:36 AM

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

Stashbot added a comment.Oct 2 2018, 7:50 AM

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.

Stashbot added a comment.Oct 2 2018, 8:04 AM

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
Stashbot added a comment.Oct 2 2018, 8:28 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
Stashbot added a comment.Oct 2 2018, 9:30 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
Marostegui added a comment.Oct 2 2018, 10:26 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

Stashbot added a comment.Oct 2 2018, 1:06 PM

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
Stashbot added a comment.Oct 2 2018, 1:47 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
gerritbot added a comment.Oct 3 2018, 5:17 AM

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

gerritbot added a comment.Oct 3 2018, 5:19 AM

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

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

Stashbot added a comment.Oct 3 2018, 5:20 AM

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

Stashbot added a comment.Oct 3 2018, 5:21 AM

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
Stashbot added a comment.Oct 3 2018, 5:26 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
gerritbot added a comment.Oct 3 2018, 5:51 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

gerritbot added a comment.Oct 3 2018, 5:54 AM

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

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

Stashbot added a comment.Oct 3 2018, 5:57 AM

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

gerritbot added a comment.Oct 3 2018, 6:31 AM

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

gerritbot added a comment.Oct 3 2018, 6:35 AM

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

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

Stashbot added a comment.Oct 3 2018, 6:37 AM

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

gerritbot added a comment.Oct 4 2018, 5:25 AM

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

gerritbot added a comment.Oct 4 2018, 5:29 AM

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

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

Stashbot added a comment.Oct 4 2018, 5:30 AM

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

Stashbot added a comment.Oct 4 2018, 5:36 AM

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 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.
Marostegui added a comment.Nov 14 2018, 8:04 AM

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

Stashbot added a comment.Nov 14 2018, 8:08 AM

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
Stashbot added a comment.Nov 14 2018, 8:14 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
Stashbot added a comment.Nov 14 2018, 8:17 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
Stashbot added a comment.Nov 14 2018, 8:19 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
Stashbot added a comment.Nov 14 2018, 8:22 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
Stashbot added a comment.Nov 14 2018, 8:24 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 closed this task as Resolved.Nov 14 2018, 8:24 AM
Marostegui updated the task description. (Show Details)

This is all done

jcrespo removed a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Feb 8 2019, 10:21 AM
jcrespo added a subtask: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.
Ladsgroup closed subtask T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas as Resolved.May 15 2021, 11:43 AM
Ladsgroup mentioned this in T312538: Collect list of tickets done for fixing core drifts .Jul 7 2022, 1:07 PM
Maintenance_bot moved this task from In progress to Done on the DBA board.Jul 7 2022, 1:29 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and 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 · Credits