Page MenuHomePhabricator

Fix PK on S5 dewiki.revision
Closed, ResolvedPublic

Description

S5 dewiki revision table needs fixing in its PK and its UNIQUE key like it was done with S4 commonswiki (T147305).
This is the current state (I have excluded both RC slaves)

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts | egrep -v "db2038|db1026|dbstore2001|db1069" | cut -f1 -d " "`; do echo $i; mysql -h$i dewiki -e "show create table revision\G"| egrep "PRIMARY|UNIQUE";done
dbstore2002.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2045.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2052.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2059.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2066.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2023.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
labsdb1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
labsdb1003.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
dbstore1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
dbstore1002.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1045.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1070.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1071.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1082.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1087.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1092.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1049.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),

So the table can look like this:

 *************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159496703 DEFAULT CHARSET=binary

So the following command needs to be executed - I will start with codfw hosts:

#./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id)"

Details

Related Gerrit Patches:
operations/mediawiki-config : masterdb-eqiad.php: Depool db1045
operations/mediawiki-config : masterdb-eqiad.php: Depool db1082
operations/mediawiki-config : masterdb-eqiad: Depool db1087
operations/mediawiki-config : masterdb-eqiad.php: Depool db1045
operations/mediawiki-config : masterdb-eqiad.php: Depool db1087
operations/mediawiki-config : masterdb-eqiad.php: Depool db1082
operations/mediawiki-config : masterdb-eqiad.php: Repool db1071
operations/mediawiki-config : masterdb-eqiad.php: Depool db1071 for maintenance
operations/mediawiki-config : masterdb-eqiad.php: Depool db1070 for maintenance

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Mentioned in SAL (#wikimedia-operations) [2016-11-02T15:01:43Z] <marostegui> Deploy schema change s5 dewiki.revision - only codfw T148967

ALTER running in db2059

db2059 finished

MariaDB MARIADB db2059.codfw.wmnet dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159324163 DEFAULT CHARSET=binary
1 row in set (0.04 sec)

Mentioned in SAL (#wikimedia-operations) [2016-11-03T09:36:40Z] <marostegui> Deploy schema change s5 dewiki.revision - only codfw https://phabricator.wikimedia.org/T148967

ALTER running in db2066

db2066 is now finished:

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb2066.codfw.wmnet -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159330927 DEFAULT CHARSET=binary

From codfw the only pending server is db2023 which is the master and I will do it Monday morning @jcrespo

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts | grep codf | cut -f1 -d " " | egrep -v "db2038|dbstore*"`; do echo $i; mysql -h$i dewiki -e "show create table revision\G" | grep PRIMARY;done
db2045.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2052.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2059.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2066.codfw.wmnet
  PRIMARY KEY (`rev_id`),
db2023.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
#./software/dbtools/osc_host.sh --host=db2023.codfw.wmnet --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page, rev_id)"
Marostegui added a comment.EditedNov 7 2016, 2:48 PM

I will not do this today, but tomorrow Tuesday as the ALTER of T147305 (db2019 - master) is still running and I do not want to have two masters running long ALTER tables at the same time.

Mentioned in SAL (#wikimedia-operations) [2016-11-08T07:30:03Z] <marostegui> Deploy schema change s5 dewiki.revision on codfw master (db2023) - T148967

This is now running on db2023

db2023 is now done

root@neodymium:~# mysql -hdb2023.codfw.wmnet -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=159496703 DEFAULT CHARSET=binary
Marostegui updated the task description. (Show Details)Nov 8 2016, 12:13 PM

codfw is finished so now eqiad needs fixing, the following hosts needs to get the right PK and the right indexes:

db1069.eqiad.wmnet
PRIMARY KEY (`rev_page`,`rev_id`),
labsdb1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
labsdb1003.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
dbstore1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
dbstore1002.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1045.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1070.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1071.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1082.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1087.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1092.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
db1049.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),

So the alter to run is:

#./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page, rev_id)"

Mentioned in SAL (#wikimedia-operations) [2016-11-24T08:59:46Z] <marostegui> Deploy alter table S5 - dewiki.revision on db1092 (depooled) - T148967

Running ALTER table on db1092 which is currently depooled due to: T151272

db1092 is done:

MariaDB PRODUCTION s5 localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160012432 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-11-28T07:42:46Z] <marostegui> Deploying alter table s5 dewiki.revision - T148967

ALTER running on db1069 s5 instance.

ALTER running on db1069 s5 instance.

This was accidentally killed - after talking to @jcrespo we decided that it is really not worth the time, but we will do it on: labsdb1009/10/11 instead of db1069 and labsdb1001 and 1003

Mentioned in SAL (#wikimedia-operations) [2016-11-29T14:47:26Z] <marostegui> Deploye alter table dbstore1001 - dewiki.revision - T148967

Alter is running on dbstore1001

Mentioned in SAL (#wikimedia-operations) [2016-11-30T07:17:32Z] <marostegui> Deploy alter table dbstore1002 - dewiki.revision - T148967

dbstore1001 is done

root@neodymium:~# mysql -hdbstore1001 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=TokuDB AUTO_INCREMENT=160178427 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'

Now running alter on dbstore1002

dbstore1002 is done

MariaDB DBSTORE localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=TokuDB AUTO_INCREMENT=160214633 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'

Change 324658 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1070 for maintenance

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

Change 324658 merged by jenkins-bot:
db-eqiad.php: Depool db1070 for maintenance

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

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:05:52Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db1070 - T148967 (duration: 02m 31s)

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:06:56Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1070 - T148967 (duration: 00m 48s)

Mentioned in SAL (#wikimedia-operations) [2016-12-01T07:32:42Z] <marostegui> Deploy alter table db1070 - dewiki.revision - T148967

Alter running on db1070

db1070 is done

MariaDB PRODUCTION s5 localhost dewiki > show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160255973 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-12-01T13:57:10Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1070 - T148967 (duration: 00m 45s)

Change 324861 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1071 for maintenance

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

Change 324861 merged by jenkins-bot:
db-eqiad.php: Depool db1071 for maintenance

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

Mentioned in SAL (#wikimedia-operations) [2016-12-02T07:51:56Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1071 - T148967 (duration: 02m 22s)

Mentioned in SAL (#wikimedia-operations) [2016-12-02T07:59:51Z] <marostegui> Deploy alter table db1071 - dewiki.revision - T148967

db1071 is done:

root@neodymium:~# mysql -hdb1071 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160278947 DEFAULT CHARSET=binary

Change 324881 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Repool db1071

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

Change 324881 merged by jenkins-bot:
db-eqiad.php: Repool db1071

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

Mentioned in SAL (#wikimedia-operations) [2016-12-02T11:10:46Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1071 - T148967 (duration: 00m 45s)

Mentioned in SAL (#wikimedia-operations) [2016-12-02T11:21:39Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1071 - T148967 (duration: 00m 44s)

Change 325260 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1082

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

Change 325260 merged by jenkins-bot:
db-eqiad.php: Depool db1082

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

Mentioned in SAL (#wikimedia-operations) [2016-12-05T07:45:20Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1082 - T148967 (duration: 02m 12s)

Mentioned in SAL (#wikimedia-operations) [2016-12-05T07:48:12Z] <marostegui> Deploy alter table db1082 - dewiki.revision - T148967

This is running on db1082

db1082 is done

root@neodymium:~# mysql -hdb1082 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160358943 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-12-05T10:58:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1082 - T148967 (duration: 00m 57s)

Change 325294 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1087

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

Change 325294 merged by jenkins-bot:
db-eqiad.php: Depool db1087

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

Mentioned in SAL (#wikimedia-operations) [2016-12-05T13:42:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T148967 (duration: 00m 44s)

Mentioned in SAL (#wikimedia-operations) [2016-12-05T13:48:22Z] <marostegui> Deploy alter table db1087 - dewiki.revision - T148967

db1087 is done:

root@neodymium:~# mysql -hdb1087 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160367851 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-12-05T16:07:07Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 (duration: 00m 59s)

Mentioned in SAL (#wikimedia-operations) [2016-12-05T16:08:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 (duration: 00m 49s)

Change 325745 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1045

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

Change 325745 merged by jenkins-bot:
db-eqiad.php: Depool db1045

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

Mentioned in SAL (#wikimedia-operations) [2016-12-07T08:32:58Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1045 - T148967 (duration: 02m 10s)

Mentioned in SAL (#wikimedia-operations) [2016-12-07T08:58:44Z] <marostegui> Deploy ALTER table db1045 dewiki.revision - T148967

db1045 is done

root@neodymium:~# mysql -hdb1045 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160424430 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2016-12-07T12:59:51Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1045 - T148967 (duration: 00m 56s)

The only pending host is db1049 (eqiad master)

Mentioned in SAL (#wikimedia-operations) [2016-12-12T09:05:51Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - T148967

I am about to run the following ALTER on db1049, which is the master:

./software/dbtools/osc_host.sh --host=db1049 --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "DROP index rev_id, DROP PRIMARY KEY, ADD PRIMARY KEY (rev_id), add key rev_page_id (rev_page, rev_id)"

Even though with the slaves I have seen it is an ONLINE operation and caused no lag, I have double checked MySQL doc and it indeed says that adding/dropping a PK can be done INPLACE

I will be running the above ALTER tomorrow morning around 7:45-8AM (UTC+1) as looks like it is the best time to avoid long running slow queries.

Mentioned in SAL (#wikimedia-operations) [2016-12-13T07:06:23Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - https://phabricator.wikimedia.org/T148967

This was started around 10 minutes ago.

The master db1049 is done

root@neodymium:/home/marostegui/git/software# mysql -hdb1049 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=160601168 DEFAULT CHARSET=binary

Change 326926 had a related patch set uploaded (by Marostegui):
db-eqiad: Depool db1087

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

Change 326926 merged by jenkins-bot:
db-eqiad: Depool db1087

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

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:27:15Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T148967 (duration: 00m 46s)

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:27:52Z] <marostegui> Deploy alter table db1087 dewiki.revision - T148967

The following server are missing the page_user_timestamp index:

db1045
db1082
db1087
db1049 (master)

I am currently altering db1087

./software/dbtools/osc_host.sh --host=db1087 --port=3306 --db=dewiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp)"

db1087 is done.

Mentioned in SAL (#wikimedia-operations) [2016-12-13T12:56:00Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T148967 and T69223 (duration: 00m 47s)

Change 326931 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1082

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

Change 326931 merged by jenkins-bot:
db-eqiad.php: Depool db1082

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

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:02:30Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1082 - T148967 and T69223 (duration: 00m 45s)

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:03:00Z] <marostegui> Deploy alter table db1082 dewiki.revision - T148967

db1082 is done

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:29:28Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1082 - T148967 and T69223 (duration: 00m 45s)

Change 326935 had a related patch set uploaded (by Marostegui):
db-eqiad.php: Depool db1045

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

Change 326935 merged by jenkins-bot:
db-eqiad.php: Depool db1045

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

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:38:55Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1045 - T148967 (duration: 00m 45s)

Mentioned in SAL (#wikimedia-operations) [2016-12-13T13:41:37Z] <marostegui> Deploy alter table db1045 dewiki.revision - T148967

db1045 is done
The only pending one is the master - will be done tomorrow morning.

Mentioned in SAL (#wikimedia-operations) [2016-12-13T16:19:39Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1045 - T148967 (duration: 00m 46s)

Mentioned in SAL (#wikimedia-operations) [2016-12-15T07:08:05Z] <marostegui> Deploy alter table db1049 (master) dewiki.revision - T148967

db1049 (master is done)

root@neodymium:~# mysql -hdb1049 -A dewiki -e "show create table revision\G"
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
  `rev_comment` varbinary(255) NOT NULL,
  `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(8) unsigned DEFAULT NULL,
  `rev_parent_id` int(8) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_model` varbinary(32) DEFAULT NULL,
  `rev_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `rev_page_id` (`rev_page`,`rev_id`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)

dewiki.revision is now unified. We agreed on not doing labs/db1069 servers so I believe this is ready to be closed.

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s5.hosts  | awk -F " " '{print $1}' | egrep -v "db2038|db1069|db1026"`; do echo $i; mysql -h$i -A dewiki -e "show create table revision\G" | grep KEY | grep -v COMPR | wc -l;done
cat:  : No such file or directory
dbstore2001.codfw.wmnet
7
dbstore2002.codfw.wmnet
7
db2045.codfw.wmnet
7
db2052.codfw.wmnet
7
db2059.codfw.wmnet
7
db2066.codfw.wmnet
7
db2023.codfw.wmnet
7
labsdb1001.eqiad.wmnet
6
labsdb1003.eqiad.wmnet
6
dbstore1001.eqiad.wmnet
7
dbstore1002.eqiad.wmnet
7
db1045.eqiad.wmnet
7
db1070.eqiad.wmnet
7
db1071.eqiad.wmnet
7
db1082.eqiad.wmnet
7
db1087.eqiad.wmnet
7
db1092.eqiad.wmnet
7
db1049.eqiad.wmnet
7
Marostegui closed this task as Resolved.Dec 15 2016, 7:46 AM