Page MenuHomePhabricator

Deploy I2b042685 to all databases
Closed, ResolvedPublic

Description

For T89630, some indexes were reordered to make queries more efficient. But the index changes do not seem to have actually been deployed everywhere, for example the old index ordering is still present on db1070 dewiki.pagelinks and db1089 enwiki.pagelinks.

See https://gerrit.wikimedia.org/r/#/c/190774/ for the specific changes involved.

Event Timeline

jcrespo moved this task from Triage to Pending comment on the DBA board.

For s1 hosts:

$ while read host port; do echo "$host:$port"; mysql -A -h $host -P $port enwiki -e "SHOW CREATE TABLE templatelinks\G" | grep tl_backlinks_namespace; done < s1.hosts
db2034.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2042.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2048.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2055.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2062.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2069.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2070.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
dbstore2001.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
dbstore2002.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db2016.codfw.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
labsdb1008.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
labsdb1003.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
labsdb1001.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1069.eqiad.wmnet:3311
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
dbstore1001.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
dbstore1002.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1080.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1083.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1089.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1073.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1072.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1066.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1065.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1055.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1053.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
db1052.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1051.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1047.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
db1057.eqiad.wmnet:3306
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
$ while read host port; do echo "$host:$port"; mysql -A -h $host -P $port enwiki -e "SHOW CREATE TABLE pagelinks\G" | grep pl_backlinks_namespace; done < s1.hosts 
db2034.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2042.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2048.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2055.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2062.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2069.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2070.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
dbstore2001.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
dbstore2002.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db2016.codfw.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
labsdb1008.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
labsdb1003.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
labsdb1001.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1069.eqiad.wmnet:3311
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
dbstore1001.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
dbstore1002.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1080.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1083.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1089.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1073.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1072.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1066.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1065.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1055.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1053.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
db1052.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1051.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1047.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
db1057.eqiad.wmnet:3306
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)

This affects almost all servers, all wikis; it will be easier to apply the change to all wikis rather than selectively.

This comment was removed by jcrespo.

Mentioned in SAL [2016-08-17T14:50:09Z] <jynus> deploying schema change on s6 hosts T139090

Mentioned in SAL [2016-08-19T08:56:31Z] <jynus> deploying schema change on s2 hosts T139090

Mentioned in SAL [2016-08-26T07:41:37Z] <jynus> deploying schema change on s3 hosts T139090

Mentioned in SAL [2016-09-03T10:21:07Z] <jynus> deploying schema change on s7 hosts T139090

Mentioned in SAL [2016-09-09T05:22:34Z] <jynus> deploying schema change on s5 hosts T139090

Mentioned in SAL [2016-09-12T10:02:58Z] <jynus> deploying schema change on s4 hosts T139090

jcrespo triaged this task as Medium priority.

Mentioned in SAL (#wikimedia-operations) [2016-09-20T10:25:11Z] <jynus> deploying schema change on s1 hosts T139090

So this change has been deployed to all hosts, but when I was finishing it, I realized I missed the imagelinks reorder. No time has been wasted, but I have to recreate that table, too on all servers. It should be faster than the other ones, I think.

However, it may take another month to be fully deployed so this can be closed. :-(

Mentioned in SAL (#wikimedia-operations) [2016-10-26T09:52:49Z] <jynus> starting schema change (imagelinks) on s1 T139090

Mentioned in SAL (#wikimedia-operations) [2016-10-27T09:15:09Z] <jynus> applying schema change (imagelinks) to s2 wikis T139090

Mentioned in SAL (#wikimedia-operations) [2016-10-28T08:01:30Z] <jynus> applying schema change (imagelinks) to s3 wikis T139090

Mentioned in SAL (#wikimedia-operations) [2016-10-29T11:10:04Z] <jynus> performing schema change on s4 (imagelinks) T139090

Mentioned in SAL (#wikimedia-operations) [2016-11-07T10:07:31Z] <jynus> performing schema change on s5 (imagelinks) T139090

Mentioned in SAL (#wikimedia-operations) [2016-11-07T12:09:19Z] <jynus> performing schema change on s6 (imagelinks) T139090

Mentioned in SAL (#wikimedia-operations) [2016-11-07T17:40:36Z] <jynus> performing schema change on s7 (imagelinks) T139090

After running over 30 000 alter tables, this is nominatively done; however, it is highly likely that some failed and could not be retried; for example if they were under maintenance at the time, network errors, human mistakes, etc.

We need to check all tables and rerun the alters where missing, to make sure everything is consistent.

After running over 30 000 alter tables, this is nominatively done;

Nice job!!! :)

self reminder: I cannot check right now dbstore2002.codfw.wmnet because it is under maintenance.

Neither for: db2034.codfw.wmnet

Mentioned in SAL (#wikimedia-operations) [2016-11-08T11:15:33Z] <jynus> running schema change on db2070 (pagelinks) T139090

Mentioned in SAL (#wikimedia-operations) [2016-11-08T11:37:18Z] <jynus> running schema change on db1045 (pagelinks) T139090

Aside from the dbs that do not exist on labs, and the above 2 hosts, the following failed to apply correctly:

  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ db2070.codfw.wmnet:enwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:enwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:cswiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:enwiktionary  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:idwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:itwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:nlwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:plwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:ptwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:svwiki  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:zhwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:enwikinews
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:shwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:srwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1003.eqiad.wmnet:wikidatawiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ db1045.eqiad.wmnet:wikidatawiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:frwiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:jawiki
  KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ labsdb1001.eqiad.wmnet:ruwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ db1080.eqiad.wmnet:enwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ db1089.eqiad.wmnet:enwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:enwiktionary
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:cebwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:mgwiktionary
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:itwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:svwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:zhwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1003.eqiad.wmnet:dewiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:frwiki
  KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ labsdb1001.eqiad.wmnet:ruwiki

I will not fix labsdb1001 and labsdb1003- they are at EOL, and the new ones will have the right structure; plus it will create unnecessary lag and metadata locking to users.

This makes those tables missing (aside from the 2 down hosts):

KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ db2070.codfw.wmnet:enwiki
KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ db1080.eqiad.wmnet:enwiki
KEY `tl_backlinks_namespace` (`tl_namespace`,`tl_title`,`tl_from_namespace`,`tl_from`)
^ db1089.eqiad.wmnet:enwiki
KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
^ db1045.eqiad.wmnet:wikidatawiki

Now that dbstore2002 is up again, I can see it is fully ALTER'ed for the shards that it contains (s1, s4, s3).
db2034 is considered broken, so no issue with that.

I will depool db1080 and db1089, sequentially, to apply the latest 2 schema changes (probably they failed because high concurrency).

Change 320382 had a related patch set uploaded (by Jcrespo):
Depool db1080 to deploy safely a long-running schema change

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

Change 320382 merged by Jcrespo:
Depool db1080 to deploy safely a long-running schema change

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

Mentioned in SAL (#wikimedia-operations) [2016-11-08T16:24:21Z] <jynus> performing schema change templatelinks on db1080 T139090

Change 320422 had a related patch set uploaded (by Jcrespo):
mariadb: repool db1080

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

Change 320423 had a related patch set uploaded (by Jcrespo):
Depool db1089 to safely apply pending schema change

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

Change 320422 merged by Jcrespo:
mariadb: repool db1080

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

Change 320423 merged by jenkins-bot:
Depool db1089 to safely apply pending schema change

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

Mentioned in SAL (#wikimedia-operations) [2016-11-08T18:12:06Z] <jynus> performing schema change templatelinks on db1089 T139090

This is officially deployed- it only took continuous schema changes for 2 months.

Mentioned in SAL (#wikimedia-operations) [2020-04-18T15:13:48Z] <Amir1> applying schema change of T139090 on labswiki (wikitech)