Page MenuHomePhabricator

Schema change on production for increase the size of wbt_text_in_lang.wbxl_language
Closed, ResolvedPublic

Description

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/547730/1/repo/sql/increase_wbxl_language.sql
  2. Where to run those changes: wikidatawiki (s8), testwikidatawiki (s3)
  3. When to run those changes: At any time, the sooner the better
  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.

This blocks switching to read-new for the wb_terms migration.

Progress:

Event Timeline

jcrespo triaged this task as Medium priority.Nov 4 2019, 9:18 AM
jcrespo moved this task from Triage to Pending comment on the DBA board.

Thanks, acking this, but I hope this is not an emergency, as we may take a bit more than usual to apply it, as 50% of persistence team is on unavailable ATM. Would potentially starting next week be good enough (first on the queue)?

Thanks. We got lucky and it's not a big blocker for us right now.

I can try to start with this next week to make sure the deletion of wb_terms doesn't get (more) blocked. I have a huge backlog to catch up with after my holidays so impossible to start with this during this week.
testwikidata should be easy to do and I will do that first (if that helps with anything)

I can try to start with this next week to make sure the deletion of wb_terms doesn't get (more) blocked. I have a huge backlog to catch up with after my holidays so impossible to start with this during this week.
testwikidata should be easy to do and I will do that first (if that helps with anything)

I think it's fine. We have so many things to do, this is not a blocker right now.

Marostegui updated the task description. (Show Details)
Marostegui moved this task from Pending comment to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2019-11-18T10:56:28Z] <marostegui> Deploy schema change on db2078 (codfw master for wikidatawiki), this will create lag on s8 codfw - T237120

This is how the table looks like after the ALTER:

       Table: wbt_text_in_lang
Create Table: CREATE TABLE `wbt_text_in_lang` (
  `wbxl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `wbxl_language` varbinary(20) NOT NULL,
  `wbxl_text_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`wbxl_id`),
  UNIQUE KEY `wbt_text_in_lang_text_id_text_id` (`wbxl_text_id`,`wbxl_language`),
  KEY `wbt_text_in_lang_language` (`wbxl_language`)
) ENGINE=InnoDB AUTO_INCREMENT=328694252 DEFAULT CHARSET=binary

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1109 T237120#6432786
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087

Mentioned in SAL (#wikimedia-operations) [2019-11-20T07:14:00Z] <marostegui> Deploy schema change on s3 (testwikidatawiki) directly on s3 primary master T237120

Impossible to alter this table on the master while it is a master.
I just got a bunch of metadata locking for lots of connections and the cluster had a glitch. We'd need to schedule a master failover for s8 after Christmas in order to get the master done.

Marostegui changed the task status from Open to Stalled.Nov 26 2019, 11:31 AM
Marostegui updated the task description. (Show Details)
Marostegui moved this task from In progress to Blocked external/Not db team on the DBA board.
Marostegui moved this task from in progress to blocked on others on the Wikidata board.

Probably a good idea to coordinate this required failover with: T238966 as that one will also probably require a failover as the revision table is quite big

We might be doing a DC switchover in Q3, so we can wait till eqiad is passive to address the final alter on the current primary master.

Marostegui changed the task status from Stalled to Open.Sep 3 2020, 5:41 AM
Marostegui moved this task from Blocked external/Not db team to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2020-09-03T07:18:39Z] <marostegui> Stop slave on s8 eqiad master (lag will appear on s8 eqiad) - T237120

Mentioned in SAL (#wikimedia-operations) [2020-09-03T07:19:46Z] <marostegui> Deploy schema change on s8 eqiad master T237120

The master is finally done

root@db1109.eqiad.wmnet[wikidatawiki]> ALTER TABLE /*_*/wbt_text_in_lang MODIFY wbxl_language VARBINARY(20) NOT NULL;
Query OK, 524052382 rows affected (1 hour 56 min 38.887 sec)
Records: 524052382  Duplicates: 0  Warnings: 0