Page MenuHomePhabricator
Create Task
Maniphest T147305

Unify commonswiki.revision
Closed, ResolvedPublic
Actions

Description

I would like to unify S4 commonswiki.revision so we can get this shard out of the way now that we had to work on it (because of T147113)

So given that we need two slaves serving recentchanges services, those need to have partitioned tables and they need PK (rev_id, rev_user).

Those slaves, per DC are:

codfw:

db2037
db2044

eqiad:

db1056
db1019 - decommissioned so I would suggest maybe db1059 to take over this service. Jaime and myself have spoken about using a spare server to replace this one in the rc service until we have a multi-shard rc service ready.

For the rest of the slaves, I would like to change the PK to (rev_id) as we just did in T147113

This is the current PK for the following slaves that would need to be changed:

codfw:

db2065.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2058.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db2051.codfw.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),


eqiad:

db1064.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),
db1068.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`rev_id`),

And the master still needs to be changed:

db1040.eqiad.wmnet
  UNIQUE KEY `rev_id` (`rev_id`),

Details

SubjectRepoBranchLines +/-
db-eqiad.php: Depool db1084 for maintenanceoperations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1081 for maintenanceoperations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1068operations/mediawiki-configmaster+5 -4
db-eqiad.php: Depool db1059 for maintenanceoperations/mediawiki-configmaster+2 -2
db-equiad: Depool db1064operations/mediawiki-configmaster+8 -6
mariadb:Create a systemd unit & init.d script for new packageoperations/puppet/mariadbmaster+634 -0
mariadb: Move db1053 from s1 to s4operations/puppetproduction+2 -2
db-eqiad.php: Depool db1068 for maintenanceoperations/mediawiki-configmaster+4 -3
db-eqiad.php: Repool db1068operations/mediawiki-configmaster+2 -2
Customize query in gerrit

Related Objects
Search...

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
gerritbot added a project: Patch-For-Review.Oct 13 2016, 6:02 AM
gerritbot added a comment.Oct 13 2016, 9:38 AM

Change 315625 merged by jenkins-bot:
db-eqiad.php: Depool db1068 for maintenance

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

• Marostegui added a comment.Oct 13 2016, 2:47 PM

db1068 is now done:

MariaDB PRODUCTION s4 localhost commonswiki > 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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=209609772 DEFAULT CHARSET=binary
1 row in set (0.00 sec)
gerritbot added a comment.Oct 13 2016, 2:50 PM

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

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

gerritbot added a comment.Oct 13 2016, 2:54 PM

Change 315683 abandoned by Marostegui:
db-eqiad.php: Repool db1068

Reason:
It is easier just to revert this: https://gerrit.wikimedia.org/r/#/c/315625/

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

Stashbot added a comment.Oct 13 2016, 3:18 PM

Mentioned in SAL (#wikimedia-operations) [2016-10-13T15:18:48Z] <marostegui@mira> Synchronized wmf-config/db-eqiad.php: Repool db1068 after it was out for an ALTER table - T147305 (duration: 00m 58s)

ops-monitoring-bot subscribed.Oct 14 2016, 12:32 PM

Script wmf_auto_reimage was launched by jynus on neodymium.eqiad.wmnet for hosts:

['db1053.eqiad.wmnet']

The log can be found in /var/log/wmf-auto-reimage/201610141232_jynus_24987.log.

ops-monitoring-bot added a comment.Oct 14 2016, 12:38 PM

Completed auto-reimage of hosts:

['db1053.eqiad.wmnet']

Those hosts were successful:

[]
ops-monitoring-bot added a comment.Oct 14 2016, 12:40 PM

Script wmf_auto_reimage was launched by jynus on neodymium.eqiad.wmnet for hosts:

['db1053.eqiad.wmnet']

The log can be found in /var/log/wmf-auto-reimage/201610141240_jynus_25626.log.

gerritbot added a comment.Oct 14 2016, 1:02 PM

Change 315945 had a related patch set uploaded (by Jcrespo):
mariadb: Move db1053 from s1 to s4

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

ops-monitoring-bot added a comment.Oct 14 2016, 1:11 PM

Completed auto-reimage of hosts:

['db1053.eqiad.wmnet']

Those hosts were successful:

['db1053.eqiad.wmnet']
gerritbot added a comment.Oct 14 2016, 4:40 PM

Change 315945 merged by Jcrespo:
mariadb: Move db1053 from s1 to s4

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

gerritbot added a comment.Oct 17 2016, 7:43 AM

Change 316296 had a related patch set uploaded (by Marostegui):
db-equiad: Depool db1064

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

gerritbot added a comment.Oct 17 2016, 10:46 AM

Change 315228 had a related patch set uploaded (by Jcrespo):
mariadb:Create a systemd unit & init.d script for new package

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

gerritbot added a comment.Oct 17 2016, 10:54 AM

Change 315228 merged by Jcrespo:
mariadb:Create a systemd unit & init.d script for new package

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

gerritbot added a comment.Oct 18 2016, 7:51 AM

Change 316296 merged by jenkins-bot:
db-equiad: Depool db1064

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

Stashbot added a comment.Oct 18 2016, 7:57 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-18T07:57:16Z] <marostegui@mira> Synchronized wmf-config/db-eqiad.php: Depool db1064 as it needs an ALTER table and pool db1068 temporarily to serve vslow and dump service - T147305 (duration: 02m 53s)

Stashbot added a comment.Oct 18 2016, 8:53 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-18T08:53:56Z] <marostegui> Deploying ALTER table on S4 commonswiki (db1064 — last host) - T147305

• Marostegui added a comment.Oct 18 2016, 2:04 PM

db1064 has finished the ALTER table:

*************************** 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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=210151386 DEFAULT CHARSET=binary
• Marostegui added a comment.Oct 18 2016, 2:13 PM

All the core servers have been done.
Pending servers are:

labsdb1001
labsdb1003
db1069
dbstore2002
dbstore1001
dbstore1002

@jcrespo and myself have agreed on doing db1069 only as the other ones will be replaced soon, so not worth the time.

Stashbot added a comment.Oct 19 2016, 9:20 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-19T09:20:11Z] <marostegui> Deploying schema change on db1069 S4 instance commonswiki revision table - T147305

Stashbot added a comment.Oct 19 2016, 9:50 AM

Mentioned in SAL (#wikimedia-operations) [2016-10-19T09:50:17Z] <marostegui@mira> Synchronized wmf-config/db-eqiad.php: Repool db1064 after finishing the ALTER table - T147305 (duration: 01m 08s)

• Marostegui added a comment.Oct 20 2016, 6:20 AM

db1069's ALTER is now done so I believe we can close this ticket:

MariaDB MARIADB db1069 commonswiki > 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` tinyblob 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`)
) ENGINE=TokuDB AUTO_INCREMENT=210289883 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'
1 row in set (0.00 sec)
• Marostegui closed this task as Resolved.Oct 20 2016, 8:23 AM
• Marostegui mentioned this in T148967: Fix PK on S5 dewiki.revision.Oct 24 2016, 1:57 PM
• Marostegui added a comment.Oct 24 2016, 2:38 PM

I just realised that db2019 - codfw's master was never altered.
So it needs to gets its table changed to match the rest of the shard.

I will alter it tomorrow morning.

jcrespo reopened this task as Open.Oct 24 2016, 2:44 PM

See also T148967#2738139

• Marostegui added a comment.Oct 25 2016, 6:29 AM

Before proceeding check: T148967#2741095

• Marostegui added a comment.Nov 3 2016, 11:55 AM

According to T148967#2741095 and the final schema table we are missing:

KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
KEY `rev_page_id` (`rev_page`,`rev_id`)

In order to add them I will run the following command in codfw hosts so far:

dbstore2002.codfw.wmnet
dbstore2001.codfw.wmnet
db2065.codfw.wmnet
db2058.codfw.wmnet
db2051.codfw.wmnet
db2044.codfw.wmnet
db2037.codfw.wmnet
db2019.codfw.wmnet
./software/dbtools/osc_host.sh --host=xx.codfw.wmnet --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page,rev_id);"
• Marostegui added a comment.Nov 3 2016, 12:09 PM

The alter is running on db2065

Stashbot added a comment.Nov 3 2016, 12:09 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-03T12:09:42Z] <marostegui> Deploying schema change s4 commonswiki.revision only codfw - https://phabricator.wikimedia.org/T147305

• Marostegui added a comment.Nov 3 2016, 12:50 PM

db2065 is finished:

root@neodymium:~# mysql -hdb2065.codfw.wmnet -A commonswiki -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` tinyblob 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=211816622 DEFAULT CHARSET=binary

Now running in db2058

• Marostegui added a comment.Nov 3 2016, 1:42 PM

db2058 is finished

root@neodymium:~# mysql -hdb2058.codfw.wmnet -A commonswiki -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` tinyblob 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=211819744 DEFAULT CHARSET=binary

Now running on db2051

• Marostegui added a comment.Nov 3 2016, 2:19 PM

db2051 is now finished

root@neodymium:/home/marostegui# mysql -hdb2051.codfw.wmnet -A commonswiki -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` tinyblob 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=211821998 DEFAULT CHARSET=binary

The only server pending in codfw (apart from the dbstores which I will be doing later, once they have finished their current heavy processes) is the master, db2019
Which needs the full ALTERs, as it was not done on the previous runs, so it currently has the wrong PK and the UNIQUE key:

root@neodymium:/home/marostegui# mysql -hdb2019.codfw.wmnet -A commonswiki -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` tinyblob 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_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)
) ENGINE=InnoDB AUTO_INCREMENT=211822074 DEFAULT CHARSET=binary

And it needs to be like this:

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`)

So I will run this:

./software/dbtools/osc_host.sh --host=db2019.codfw.wmnet --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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);"
Stashbot added a comment.Nov 7 2016, 8:30 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-07T08:30:35Z] <marostegui> Deploy schema change on s4 master (db2019) commonswiki.revision - T147305

• Marostegui added a comment.Nov 7 2016, 4:14 PM

codfw master (db2019) has now the correct table schema

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` tinyblob 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=212427038 DEFAULT CHARSET=binary

All the hosts in s4 codfw are now consistent with the right PK and the 6 indexes.
dbstore2001,2 are currently being ALTERed.

Apart from that the following hosts in eqiad will be next:

These ones are missing the correct PK and the correct indexes, so they need this alter:

dbstore1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)

dbstore1002.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)

labsdb1001.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)

labsdb1003.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)

db1059.eqiad.wmnet
  PRIMARY KEY (`rev_page`,`rev_id`),
  UNIQUE KEY `rev_id` (`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`)

---


./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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);"

The following hosts only need one index added:

db1069.eqiad.wmnet
  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`)

db1064.eqiad.wmnet
  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`)

db1068.eqiad.wmnet
  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`)

db1081.eqiad.wmnet
  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`)

db1084.eqiad.wmnet
  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`)

db1091.eqiad.wmnet
  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`)

db1040.eqiad.wmnet
  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`)

---

./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp);"
gerritbot added a comment.Nov 8 2016, 7:28 AM

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

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

• Marostegui added a comment.Nov 8 2016, 8:11 AM

dbstore2001,2002 are done

root@neodymium:~# for i in dbstore2001 dbstore2002; do echo $i; mysql -h$i.codfw.wmnet -A commonswiki -e "show create table revision\G";done
dbstore2001
*************************** 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` tinyblob 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=212522810 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
dbstore2002
*************************** 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` tinyblob 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=212522810 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
Stashbot added a comment.Nov 8 2016, 8:44 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-08T08:44:33Z] <marostegui> Deploy schema change s4 commonswiki.revision table - T147305

• Marostegui added a comment.Nov 8 2016, 8:46 AM

This is now running on dbstore1001

gerritbot added a comment.Nov 8 2016, 10:06 AM

Change 320346 merged by jenkins-bot:
db-eqiad.php: Depool db1059 for maintenance

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

Stashbot mentioned this in T149079: codfw: Fix S4 commonswiki.templatelinks partitions.Nov 8 2016, 10:08 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-08T10:08:04Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1059 - T149079 T147305 (duration: 00m 57s)

• Marostegui added a comment.Nov 8 2016, 10:17 AM

This is running on db1059:

./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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.Nov 8 2016, 4:37 PM

db1059 is done:

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb1059 -A commonswiki -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` tinyblob 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=212567104 DEFAULT CHARSET=binary
• Marostegui added a comment.Nov 8 2016, 5:07 PM
In T147305#2778800, @Marostegui wrote:

This is now running on dbstore1001

Finished:

MariaDB MARIADB dbstore1001 commonswiki > 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` tinyblob 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=212429143 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'
1 row in set (0.00 sec)
Stashbot added a comment.Nov 10 2016, 8:01 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-10T08:01:05Z] <marostegui> Deploy schema change s4 commonswiki.revision (dbstore1002) - T147305

Stashbot added a comment.Nov 11 2016, 9:39 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-11T09:38:57Z] <marostegui> Deploy schema change s4 commonswiki.revision db1069 - T147305

Stashbot added a comment.Nov 11 2016, 10:14 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-11T10:14:55Z] <marostegui> Deploy alter table dbstore1002 s4 commonswiki.revision - T147305

• Marostegui added a comment.Nov 11 2016, 12:25 PM

dbstore1002 is done

MariaDB DBSTORE localhost commonswiki > 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` tinyblob 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=213051187 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'
Volans unsubscribed.Nov 11 2016, 12:31 PM
• Marostegui added a comment.EditedNov 11 2016, 12:44 PM

Summary of what is pending after this week:

Needed a PK change + indexes:

labsdb1001
labsdb1003

./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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);"

Need the indexes:

db1064
db1068
db1069

KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
KEY `rev_page_id` (`rev_page`,`rev_id`)

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

db1081, db1084, db1040 (master)

KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),

./software/dbtools/osc_host.sh --host=xxx --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp);"
Stashbot added a comment.Nov 14 2016, 8:21 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-14T08:21:38Z] <marostegui> Deploy schema change labsdb1001 s4 commonswiki revision table (T147305)

• Marostegui added a comment.EditedNov 14 2016, 8:25 AM

This is now running on labsdb1001

./software/dbtools/osc_host.sh --host=labsdb1001 --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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);"

db1064's ALTER will be run once the alter of T149079 is done

• Marostegui added a comment.Nov 14 2016, 6:19 PM

labsdb1001 is done:

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hlabsdb1001 -A commonswiki -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` tinyblob 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=213888393 DEFAULT CHARSET=binary `compression`='tokudb_zlib'
Stashbot added a comment.Nov 15 2016, 7:47 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-15T07:47:31Z] <marostegui> Deploy schema change s4 commonswiki.revision db1064 - T147305

• Marostegui added a comment.Nov 15 2016, 7:51 AM

This is now running in db1064

./software/dbtools/osc_host.sh --host=db1064 --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page,rev_id);"
• Marostegui added a comment.Nov 15 2016, 8:26 AM

db1064 is done

root@neodymium:~# mysql -hdb1064 -A commonswiki -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` tinyblob 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=214141101 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 15 2016, 12:01 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-15T12:01:52Z] <marostegui> Deploy schema change labsdb1003 s4 commonswiki revision table T147305

• Marostegui added a comment.Nov 15 2016, 12:06 PM

Running the PK change in labsdb1003

./software/dbtools/osc_host.sh --host=labsdb1003 --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "DROP KEY 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.Nov 16 2016, 7:24 AM

labsdb1003 is finished

root@neodymium:~# mysql -hlabsdb1003 -A commonswiki -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` tinyblob 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=214472955 DEFAULT CHARSET=binary `compression`='tokudb_zlib'
Stashbot added a comment.Nov 21 2016, 8:08 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-21T08:08:28Z] <marostegui> Deploy ALTER table db1069 commonswiki.revision - https://phabricator.wikimedia.org/T147305

• Marostegui added a comment.Nov 21 2016, 8:10 AM

Running on db1069:

./software/dbtools/osc_host.sh --host=db1069.eqiad.wmnet --port=3314 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page,rev_id);"
gerritbot added a comment.Nov 21 2016, 8:15 AM

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

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

• Marostegui added a comment.Nov 21 2016, 9:43 AM

db1069 is done:

root@neodymium:~# mysql -hdb1069 -P3314 -A commonswiki -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` tinyblob 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=216783016 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'
gerritbot added a comment.Nov 21 2016, 10:03 AM

Change 322615 merged by jenkins-bot:
db-eqiad.php: Depool db1068

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

Stashbot added a comment.Nov 21 2016, 10:05 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-21T10:05:49Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1068 - T147305 (duration: 00m 48s)

Stashbot added a comment.Nov 21 2016, 10:11 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-21T10:11:07Z] <marostegui> Deploy ALTER table db1068 commonswiki.revision - https://phabricator.wikimedia.org/T147305

• Marostegui added a comment.Nov 21 2016, 10:17 AM

Running on db1068:

./software/dbtools/osc_host.sh --host=db1068.eqiad.wmnet --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp), add key rev_page_id (rev_page,rev_id);"
• Marostegui added a comment.Nov 21 2016, 10:59 AM

db1068 is done

root@neodymium:~# mysql -hdb1068 -A commonswiki -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` tinyblob 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=216805935 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 21 2016, 1:13 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-21T13:13:42Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1068 - T147305 (duration: 00m 49s)

gerritbot added a comment.Nov 22 2016, 8:06 AM

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

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

gerritbot added a comment.Nov 22 2016, 8:08 AM

Change 322833 merged by jenkins-bot:
db-eqiad.php: Depool db1081 for maintenance

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

Stashbot added a comment.Nov 22 2016, 8:10 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T08:10:38Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1081 - T147305 (duration: 00m 50s)

Stashbot added a comment.Nov 22 2016, 8:14 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T08:14:09Z] <marostegui> Deploy ALTER table db1081 commonswiki.revision - T147305

• Marostegui added a comment.Nov 22 2016, 8:29 AM

db1081 is done

root@neodymium:~# mysql -hdb1081 -A commonswiki -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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=217267427 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 22 2016, 8:33 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T08:33:46Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1081 - T147305 (duration: 00m 54s)

gerritbot added a comment.Nov 22 2016, 8:54 AM

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

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

gerritbot added a comment.Nov 22 2016, 8:56 AM

Change 322837 merged by jenkins-bot:
db-eqiad.php: Depool db1084 for maintenance

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

Stashbot added a comment.Nov 22 2016, 8:58 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T08:58:50Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1084 - T147305 (duration: 00m 53s)

Stashbot added a comment.Nov 22 2016, 9:00 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T09:00:57Z] <marostegui> Deploy ALTER table db1084 commonswiki.revision - T147305

• Marostegui added a comment.Nov 22 2016, 9:14 AM

db1084 is done

root@neodymium:~# mysql -hdb1084 -A commonswiki -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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=217287166 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 22 2016, 11:16 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T11:16:34Z] <marostegui> Deploy ALTER table db1091 commonswiki.revision - T147305

• Marostegui added a comment.Nov 22 2016, 11:34 AM

db1091 is done

root@neodymium:/home/marostegui/git/software# mysql -hdb1091 -A commonswiki -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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=217342319 DEFAULT CHARSET=binary
Stashbot added a comment.Nov 22 2016, 2:14 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-22T14:14:40Z] <marostegui> Deploy ALTER table db1040 (master) commonswiki.revision - https://phabricator.wikimedia.org/T147305

• Marostegui added a comment.Nov 22 2016, 2:16 PM
In T147305#2814237, @Stashbot wrote:

Mentioned in SAL (#wikimedia-operations) [2016-11-22T14:14:40Z] <marostegui> Deploy ALTER table db1040 (master) commonswiki.revision - https://phabricator.wikimedia.org/T147305

This won't be done today, but tomorrow morning.

Stashbot added a comment.Nov 28 2016, 9:09 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-28T09:09:03Z] <marostegui> Deploy ALTER table (add an index) db1040 (master) commonswiki.revision - T147305

• Marostegui added a comment.Nov 28 2016, 9:11 AM

The ALTER is running on the master (db1040)

./software/dbtools/osc_host.sh --host=db1040.eqiad.wmnet --port=3306 --db=commonswiki --table=revision --method=ddl --no-replicate "add key page_user_timestamp (rev_page,rev_user,rev_timestamp);"
• Marostegui added a comment.Nov 28 2016, 9:54 AM

The master is done

root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb1040 -A commonswiki -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` tinyblob 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`)
) ENGINE=InnoDB AUTO_INCREMENT=222678547 DEFAULT CHARSET=binary
• Marostegui closed this task as Resolved.Nov 28 2016, 9:56 AM

All the servers are done (db2044|db2037|db1053|db1056 are rc ones):

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s4.hosts | awk -F " " '{print $1}' | egrep -v "db1069|db2044|db2037|db1053|db1056"`; do echo $i; mysql -h$i -A commonswiki -e "show create table revision\G" | grep "KEY" | grep -v COMPRESSED;done
dbstore2002.codfw.wmnet
  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`)
dbstore2001.codfw.wmnet
  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`)
db2065.codfw.wmnet
  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`)
db2058.codfw.wmnet
  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`)
db2051.codfw.wmnet
  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`)
db2019.codfw.wmnet
  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`)
dbstore1001.eqiad.wmnet
  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`)
dbstore1002.eqiad.wmnet
  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`)
labsdb1001.eqiad.wmnet
  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`)
labsdb1003.eqiad.wmnet
  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`)
db1059.eqiad.wmnet
  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`)
db1064.eqiad.wmnet
  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`)
db1068.eqiad.wmnet
  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`)
db1081.eqiad.wmnet
  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`)
db1084.eqiad.wmnet
  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`)
db1091.eqiad.wmnet
  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`)
db1040.eqiad.wmnet
  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`)
root@neodymium:/home/marostegui/git/software/dbtools# mysql -hdb1069 -P3314 -A commonswiki -e "show create table revision\G" | grep "KEY" | grep -v COMPRESSED
  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`)
Jdforrester-WMF added a comment.Nov 28 2016, 6:25 PM

Thank you!

• Marostegui mentioned this in T158454: enwiki database on db1065 missing rev_timestamp index.Feb 20 2017, 7:07 AM
• Marostegui mentioned this in T102532: revision page_user_timestamp index problematic on large wikis.Feb 27 2017, 3:08 PM
• Marostegui added a parent task: T142725: Clean up revision UNIQUE indexes.Jun 2 2017, 9:53 AM
Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:40 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL