Review schema changes for T125071 - Add index to image table on all wikis
Closed, ResolvedPublic

Description

  1. The ALTER TABLEs to run: https://gerrit.wikimedia.org/r/#/c/342631/2/maintenance/archives/patch-image-user-index-2.sql
  2. Where to run those changes: all wikis with "image" table
  3. When to run those changes: whenever is most convenient
  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: no
  6. If it involves new columns or tables: n/a
There are a very large number of changes, so older changes are hidden. Show Older Changes

Change 343255 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2058

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

Change 343255 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2058

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

Mentioned in SAL (#wikimedia-operations) [2017-03-17T08:31:53Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2058 - T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-17T08:32:07Z] <marostegui> Deploy schema change on dbstore2002 and db2058 (s4) - T160415 T73563

Marostegui moved this task from Triage to In progress on the DBA board.Mar 17 2017, 10:00 AM

dbstore2002 and db2058 are done:

root@neodymium:~# for i in db2058 dbstore2002; do echo $i; mysql --skip-ssl -h$i.codfw.wmnet commonswiki -e "show create table image\G" | grep img_user_timestamp; done
db2058
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
dbstore2002
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Mentioned in SAL (#wikimedia-operations) [2017-03-17T13:39:08Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2058 - T160415 - T73563 (duration: 01m 06s)

Change 343303 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2051

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

Change 343303 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2051

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

Mentioned in SAL (#wikimedia-operations) [2017-03-17T14:00:54Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2051 - T160415 - T73563 (duration: 00m 42s)

Mentioned in SAL (#wikimedia-operations) [2017-03-17T14:01:37Z] <marostegui> Deploy schema change on dbstore1001 and db2051 (s4) - T160415 - T73563

Mentioned in SAL (#wikimedia-operations) [2017-03-21T06:49:27Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2051 - T160415 - T73563 (duration: 01m 07s)

Marostegui added a comment.EditedMar 21 2017, 6:53 AM

db2051 and dbstore1001 are done:

root@neodymium:~# for i in db2051.codfw.wmnet dbstore1001.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp; done
db2051.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
dbstore1001.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 343823 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Depool db2044

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

Change 343823 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Depool db2044

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

Mentioned in SAL (#wikimedia-operations) [2017-03-21T07:18:02Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2044 - T160415 - T73563 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-03-21T07:18:09Z] <marostegui> Deploy schema change on db2044 and labsdb1009 (s4) - https://phabricator.wikimedia.org/T160415 - https://phabricator.wikimedia.org/T73563

db2044 and labsdb1009 are done:

root@neodymium:/home/marostegui/databases_s6# for i in db2044.codfw.wmnet labsdb1009.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp; done
db2044.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
labsdb1009.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 343867 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Repool db2044, depool db2037

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

Change 343867 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Repool db2044, depool db2037

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

Mentioned in SAL (#wikimedia-operations) [2017-03-21T14:01:55Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2044, depool db2037 T160415 - T73563 (duration: 00m 42s)

Change 344081 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-codfw.php: Repool db2037

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

db2037 is done:

root@neodymium:~# mysql --skip-ssl -hdb2037.codfw.wmnet commonswiki -e "show create table image\G" | grep img_user_timestamp;
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344081 merged by jenkins-bot:
[operations/mediawiki-config] db-codfw.php: Repool db2037

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T06:43:28Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2037 T160415 - T73563 (duration: 00m 43s)

Change 344086 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Depool db1091

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

Pending host in codfw: db2019 (the master), which will be done after all slaves in eqiad are done. This ALTER will generate lag on the whole codfw data center.

Starting now with eqiad hosts + labsdb1010 and labsdb1011 + sanitarium2

Change 344086 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Depool db1091

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T07:40:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1091 T160415 - T73563 (duration: 00m 43s)

db1091 is done:

root@neodymium:~# mysql --skip-ssl -hdb1091 commonswiki -e "show create table image\G" | grep img_user_timestamp;
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:29:47Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1091 T160415 - T73563 (duration: 00m 43s)

Change 344094 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Depool db1084

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

Change 344094 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Depool db1084

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:39:40Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1084 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-22T09:40:05Z] <marostegui> Deploy alter table s4 (commonswiki) db1084 - T73563 T160415

Change 344122 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Repool db1084,depool db1081

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

Change 344122 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Repool db1084,depool db1081

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T12:23:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1084, depool db1081 T160415 - T73563 (duration: 00m 43s)

db1084 and labsdb1010 are done:

root@neodymium:~# for i in  labsdb1010.eqiad.wmnet db1084.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp  ;done
labsdb1010.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1084.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Mentioned in SAL (#wikimedia-operations) [2017-03-22T12:26:37Z] <marostegui> Deploy schema change on s4 to db1081 and labsdb1011 - T160415 T73563

Change 344147 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config] db-eqiad.php: Repool db1081, depool db1068

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

db1081 is done:

root@neodymium:~# mysql --skip-ssl -hdb1081 commonswiki -e "show create table image\G" | grep img_user_timestamp;
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344147 merged by jenkins-bot:
[operations/mediawiki-config] db-eqiad.php: Repool db1081, depool db1068

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T14:18:52Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1081, depool db1068 T160415 - T73563 (duration: 00m 43s)

Change 344308 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1059, repool db1068

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

db1068 and labsdb1011 are done:

root@neodymium:~# for i in  labsdb1011.eqiad.wmnet db1068.eqiad.wmnet; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp  ;done
labsdb1011.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1068.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344308 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1059, repool db1068

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

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:36:05Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1068, depool db1059 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:37:53Z] <marostegui> Deploy schema change s4 on db1068 and labsdb1001 T160415 - T73563

Mentioned in SAL (#wikimedia-operations) [2017-03-23T07:37:53Z] <marostegui> Deploy schema change s4 on db1068 and labsdb1001 T160415 - T73563

s/db1068/db1059

db1059 and labsdb1001 are done:

root@neodymium:~# mysql --skip-ssl -hdb1059 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

[root@labsdb1001 12:09 /root]
# mysql --skip-ssl commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344365 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1064, repool db1059

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

Change 344365 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1064, repool db1059

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

Mentioned in SAL (#wikimedia-operations) [2017-03-23T12:58:26Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1059, depool db1064 T160415 - T73563 (duration: 00m 43s)

labsdb1003 is done:

[root@labsdb1003 15:45 /root]
# mysql --skip-ssl commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

db1095 and db1064 are done:

root@neodymium:~# for i in db1095 db1064; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp; done
db1095
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1064
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344579 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Change 344579 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Mentioned in SAL (#wikimedia-operations) [2017-03-24T07:48:22Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1056, repool db1059 T160415 - T73563 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-03-24T07:49:05Z] <marostegui> Deploy schema change s4 on db1069 and db1056 - T160415 - T73563

Mentioned in SAL (#wikimedia-operations) [2017-03-24T08:23:37Z] <marostegui> Deploy schema change s4 db2019 (codfw master) - T160415

db2019 is done, that means that the whole codfw dc is done:

root@neodymium:/home/marostegui/databases_s5# mysql --skip-ssl -hdb2019.codfw.wmnet commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

db1056 and db1069 are done:

root@neodymium:/home/marostegui/databases_s5# mysql --skip-ssl -hdb1056 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
root@neodymium:/home/marostegui/databases_s5# mysql --skip-ssl -hdb1069 -P3314 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Change 344605 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Repool db1056

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

Change 344605 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Repool db1056

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

Mentioned in SAL (#wikimedia-operations) [2017-03-24T12:30:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1056 T160415 - T73563 (duration: 00m 44s)

Change 344895 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1053

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

Change 344895 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1053

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

Mentioned in SAL (#wikimedia-operations) [2017-03-27T06:26:28Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1053 T160415 - T73563 (duration: 00m 56s)

db1053 is done:

root@neodymium:~# mysql --skip-ssl -hdb1053 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

Only eqiad master (db1040) is pending, which I will start in a bit as it is an inplace operation

Mentioned in SAL (#wikimedia-operations) [2017-03-27T11:51:59Z] <marostegui> Deploy new index on db1040, s4 primary master table: commonswiki.image - T160415

Mentioned in SAL (#wikimedia-operations) [2017-03-27T12:01:53Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1053 T160415 - T73563 (duration: 01m 07s)

db1040 (primary master) is done:

root@neodymium:~# mysql --skip-ssl -hdb1040 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
Marostegui closed this task as "Resolved".Mar 27 2017, 2:57 PM

All done:

root@neodymium:~# for i in `cat /home/marostegui/git/software/dbtools/s4.hosts | grep -v db1069 | awk -F " " '{print $1}'`; do echo $i; mysql --skip-ssl -h$i commonswiki -e "show create table image\G" | grep img_user_timestamp; done
dbstore2002.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
dbstore2001.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2065.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2058.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2051.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2044.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2037.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db2019.codfw.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
dbstore1001.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
dbstore1002.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1095.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1053.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1056.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1059.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1064.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1068.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1081.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1084.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1091.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
db1040.eqiad.wmnet
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

db1069 and the labs hosts:

root@neodymium:~# mysql --skip-ssl -hdb1069 -P3314 commonswiki -e "show create table image\G" | grep img_user_timestamp
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
labsdb1001
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

labsdb1003
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

labsdb1009
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

labsdb1010
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)

labsdb1011
  KEY `img_user_timestamp` (`img_user`,`img_timestamp`)
jcrespo reopened this task as "Open".Tue, Apr 11, 4:45 PM
jcrespo triaged this task as "High" priority.

I do not see this applied to s3. This can cause errors due to a reference to the index.

Mentioned in SAL (#wikimedia-operations) [2017-04-11T16:56:35Z] <marostegui> Deploy unscheduled alter table on db1078 (s3, image table) - T160415

Mentioned in SAL (#wikimedia-operations) [2017-04-11T16:58:24Z] <marostegui> Deploy unscheduled alter table on db1077 (s3, image table) - T160415

Both main servers have been done now, so errors should be almost gone.
Starting with the other ones now too

Mentioned in SAL (#wikimedia-operations) [2017-04-11T17:00:10Z] <marostegui> Deploy unscheduled alter table on db1035 (s3, image table) - T160415

Mentioned in SAL (#wikimedia-operations) [2017-04-11T17:02:30Z] <marostegui> Deploy unscheduled alter table on db1038 (s3, image table) - T160415

Mentioned in SAL (#wikimedia-operations) [2017-04-11T17:04:49Z] <marostegui> Deploy unscheduled alter table on db1015 (s3, image table) - T160415

Mentioned in SAL (#wikimedia-operations) [2017-04-11T17:06:07Z] <marostegui> Deploy unscheduled alter table on db1044 (s3, image table) - T160415

All eqiad main servers are now done, so errors should be all gone.
I will alter the master + codfw tomorrow.

Sorry for the inconveniences of forgetting s3, I really cannot believe I did.

db1069 (sanitarium) db1095 (sanitarium2), labsdb1001,1003, 1009,1010,1011 are also done.

dbstore1001 and dbstore1002 are done

jcrespo lowered the priority of this task from "High" to "Normal".Tue, Apr 11, 5:30 PM

dbstore2002 and dbstore2001 are done.

All codfw slaves are done:

db2057
db2050
db2043
db2036

Pending hosts: only eqiad and codfw masters, that I will do tomorrow morning.

Mentioned in SAL (#wikimedia-operations) [2017-04-12T05:56:37Z] <marostegui> Deploy alter table on db2108 codfw master (s3, image table) - T160415

codfw master (db2018) is done

Mentioned in SAL (#wikimedia-operations) [2017-04-12T06:13:07Z] <marostegui> Deploy alter table on db1075 eqiad master (s3, image table) - T160415

Marostegui closed this task as "Resolved".Wed, Apr 12, 6:15 AM

db1075 eqiad master is done
All the hosts are now done.