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

Ladsgroup created this task.Nov 1 2019, 4:25 PM
jcrespo triaged this task as Medium priority.Nov 4 2019, 9:18 AM
jcrespo moved this task from Triage to Next 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.

Addshore moved this task from incoming to in progress on the Wikidata board.Nov 5 2019, 2:10 PM

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 Next 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

Marostegui updated the task description. (Show Details)Nov 18 2019, 2:17 PM

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
Marostegui added a comment.EditedNov 18 2019, 2:23 PM

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1109 T237120#6432786
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
Marostegui updated the task description. (Show Details)Nov 19 2019, 5:28 AM

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

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

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.Thu, Sep 3, 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

Marostegui closed this task as Resolved.Thu, Sep 3, 9:42 AM

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
Marostegui updated the task description. (Show Details)Thu, Sep 3, 9:42 AM