Page MenuHomePhabricator

Schema change for renaming several indexes in change_tag table
Closed, ResolvedPublic

Description

Part of T270033: Fix and enforce table prefix usage in columns and indexes in core

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/651160/8/maintenance/archives/patch-change_tag-rename-indexes.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: Tested in beta cluster.
  6. if the data should be made available on the labs replicas and/or dumps: Yes, data in this table is public

Progress

  • s1
    • eqiad
    • codfw
  • s2
    • eqiad
    • codfw
  • s3
    • eqiad
    • codfw
  • s4
    • eqiad
    • codfw
  • s5
    • eqiad
    • codfw
  • s7
    • eqiad
    • codfw
  • s8
    • eqiad
    • codfw
  • labswiki
  • labtestwiki

Event Timeline

LSobanski triaged this task as Medium priority.Jun 9 2021, 8:46 AM
LSobanski moved this task from Triage to Ready on the DBA board.
Marostegui moved this task from Ready to In progress on the DBA board.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.
Marostegui updated the task description. (Show Details)

s6 codfw is entirely done.
I have altered two eqiad hosts and will leave it like that till Monday to make sure there are no queries forcing that index.

  • dbstore1005
  • db1180
  • db1173
  • db1168
  • db1165
  • db1155
  • db1140
  • db1131
  • db1113
  • db1098
  • db1096
  • clouddb1021
  • clouddb1019
  • clouddb1015

Even though this change can be done online, it is extremely hard do to it with the host pooled and with replication running due to the traffic flowing to this table. So it really needs depooling + stopping replication.

This change is very very complex, so I am going to deploy it on codfw only and once we have done the switchover (the 28th) will deploy it on eqiad.

@Ladsgroup I have changed the schema change to make sure it is done in one transaction, can you please confirm this looks good to you?

MariaDB [test2]> alter table change_tag drop key change_tag_rc_tag_id, add UNIQUE INDEX ct_rc_tag_id (ct_rc_id, ct_tag_id), drop key change_tag_log_tag_id, add UNIQUE INDEX ct_log_tag_id (ct_log_id, ct_tag_id), drop key change_tag_rev_tag_id, add UNIQUE INDEX ct_rev_tag_id (ct_rev_id, ct_tag_id), drop key change_tag_tag_id_id, add key ct_tag_id_id (    ct_tag_id, ct_rc_id, ct_rev_id, ct_log_id  );
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test2]> show create table change_tag;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| change_tag | CREATE TABLE `change_tag` (
  `ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ct_rc_id` int(10) unsigned DEFAULT NULL,
  `ct_log_id` int(10) unsigned DEFAULT NULL,
  `ct_rev_id` int(10) unsigned DEFAULT NULL,
  `ct_params` blob DEFAULT NULL,
  `ct_tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ct_id`),
  UNIQUE KEY `ct_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
  UNIQUE KEY `ct_log_tag_id` (`ct_log_id`,`ct_tag_id`),
  UNIQUE KEY `ct_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
  KEY `ct_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=162709226 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
Marostegui moved this task from In progress to Blocked on the DBA board.

Codfw is fully done, so now waiting for the DC switch to finish eqiad.

Marostegui updated the task description. (Show Details)

All done