Rampant differences in indexes on enwiki.revision across the DB cluster
Open, HighPublic

Description

I only analyzed enwiki on s1, but I wouldn't be surprised if discrepancies like these existed for other DBs too.

  • Different PRIMARY KEYs:
    • 6 servers (the non-contributions eqiad slaves: db1053, db1057, db1065, db1066, db1072, db1073) have PRIMARY KEY(rev_id). This is the sane thing to do, and what MediaWiki's tables.sql prescribes.
    • 7 servers (the eqiad master: db1052; and the non-special codfw slaves: db2016, db2048, db2055, db2062, db2069, db2070) have PRIMARY KEY(rev_page, rev_id). All of these also have UNIQUE KEY(rev_id).
    • 4 servers (the contributions slaves in both eqiad and codfw: db1051, db1055, db2034, db2042) have PRIMARY KEY(rev_id, rev_user). These servers do not have any sort of unique constraint on rev_id. It's still an auto-increment field, and we will probably (hopefully?) never rotate a contributions slave into the master position, so we hopefully won't ever get duplicate rev_id values, but the fact that not all servers have a uniqueness constraint scares me.
  • Presence of an index on (rev_page, rev_id)
    • Per the above, 7 servers have this as their primary key
    • The 6 servers that have PRIMARY KEY(rev_id) also have a key on (rev_page, rev_id)
    • The 4 servers that have PRIMARY KEY(rev_id, rev_user) do not have a key on (rev_page, rev_id) at all
  • Presence of the rev_timestamp key: what's in this key varies (see below), but 2 servers (db1065 and db1066) don't have it at all. If there were code that used FORCE INDEX(rev_timestamp), that could cause query errors. (I don't believe there is, nor can I think of anything that uses this index offhand; I wonder if it's used.)
  • rev_id padding for the rev_timestamp, page_timestamp, user_timestamp, usertext_timestamp and page_user_timestamp indexes
    • On 7 servers (all eqiad servers except db1052 and db1066), these indexes have rev_id as their last field.
    • On db1066, page_timestamp does not have rev_id at the end, but the other indexes do
    • On the other 9 servers (db1052 and all codfw servers), these indexes do not have rev_id at the end
  • Partitions on the contributions slaves
    • The codfw contributions slaves (db2034 and db2042) have identical partitions for rev_user, going up to 28M
    • db1051 has a much smaller set of partitions, stopping at 24M
    • db1055 has almost the same partitions as db1051, but does not have the ones at 50K and 24M
    • The non-contributions servers do not have these partitions, by design

Related Objects

StatusAssignedTask
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
ResolvedNone
Duplicate leucosticte
DeclinedNone
Resolveddaniel
Resolvedmatthiasmullie
Resolvedjcrespo
DuplicateNone
Resolvedjcrespo
ResolvedCatrope
ResolvedCatrope
Resolvedjcrespo
ResolvedTTO
OpenMarostegui
OpenNone
Resolvedjcrespo
ResolvedAddshore
OpenAddshore
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
ResolvedMarostegui
OpenNone
ResolvedKrinkle
OpenNone
OpenNone
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
OpenNone
OpenAnomie
ResolvedMarostegui
ResolvedMarostegui
DeclinedSpringle
There are a very large number of changes, so older changes are hidden. Show Older Changes

Change 340080 had a related patch set uploaded (by Marostegui):
db-codfw.php: Depool db2034

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

Change 340080 merged by jenkins-bot:
db-codfw.php: Depool db2034

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

Mentioned in SAL (#wikimedia-operations) [2017-02-27T07:21:23Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2034 - T132416 (duration: 00m 40s)

Mentioned in SAL (#wikimedia-operations) [2017-02-27T07:29:11Z] <marostegui> Deploy alter table enwiki.revision - db2034 - T132416

I am now going to alter the RC slaves on codfw, which they both need bigger alter tables as they have all the indexes a bit messed up. The following ALTER table will make them look like db1026 as per (T147747#3052575)

alter table revision drop key rev_timestamp, drop key page_timestamp, drop key user_timestamp, drop key usertext_timestamp, drop key page_user_timestamp, add KEY rev_timestamp (rev_timestamp,rev_id), add KEY page_timestamp (rev_page,rev_timestamp,rev_id), add KEY usertext_timestamp (rev_user_text,rev_timestamp,rev_id), add KEY user_timestamp (rev_user,rev_timestamp,rev_id), add KEY rev_page_id (rev_page,rev_id);
jcrespo added a comment.EditedFeb 27 2017, 2:49 PM

So, after months of archeology, trying to understand the reasons why things are they way they are without breaking anything in the way, the final state of the tables will be:

  • For normal slaves,
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`)

This is the exact same one on tables.sql

  • For special slaves:
PRIMARY KEY (`rev_id`,`rev_user`),
KEY `rev_page_id` (`rev_page`,`rev_id`),
KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED

The main difference being that it requires a different primary key due to partitioning, and that means that the extended key feature fails to work like in a normal case, so it needs an explicit rev_id at the end of them to prevent bad queries.

Differences on partitioning can be ignored- newer slaves will have more granularity, but that will be mostly non-impactful, and can be solved later, easily.

No other schema changes should happen on revision without logging it here until this is 100% done.

Marostegui added a comment.EditedFeb 28 2017, 6:58 AM

So, db2034 finished and as per our discussion yesterday evening it is currently like this:

PRIMARY KEY (`rev_id`,`rev_user`),
KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
KEY `rev_page_id` (`rev_page`,`rev_id`)

So it is missing the index: KEY page_user_timestamp (rev_page,rev_user,rev_timestamp,rev_id) which I am going to add now

Mentioned in SAL (#wikimedia-operations) [2017-02-28T07:00:32Z] <marostegui> Deploy alter table enwiki.revision db2034 - T132416

db2034 is done:

       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) DEFAULT 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`,`rev_user`),
  KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
  KEY `rev_page_id` (`rev_page`,`rev_id`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=768011452 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (rev_user)
(PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
<snip>

db2042 is being altered by @jcrespo - so the only pending host in s1 codfw is the master, dbstore2001 and dbstore2002.

Mentioned in SAL (#wikimedia-operations) [2017-03-01T07:05:09Z] <marostegui> Deploy alter table enwiki.revision - dbstore2002 - T132416

dbstore2002:

root@dbstore2002.codfw.wmnet[enwiki]> 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) DEFAULT 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`)

Mentioned in SAL (#wikimedia-operations) [2017-03-02T07:20:16Z] <marostegui> Deploy alter table enwiki.revision db2016 (codfw master) - T132416

The master of codfw (db2016) is now being altered

db2016 (codfw master) is done:

root@db2016.codfw.wmnet[enwiki]> 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) DEFAULT 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=768342662 DEFAULT CHARSET=binary
1 row in set (0.04 sec)

All codfw is now done! Will start with eqiad next week (rc slaves have been done by Jaime already - thanks!)

Change 341517 had a related patch set uploaded (by marostegui):
[operations/mediawiki-config] db-codfw.php: Repool db2034

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-03-07T12:03:59Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2034 - T132416 (duration: 00m 50s)

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-03-13T07:24:06Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1089 - T132416 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-03-13T07:24:12Z] <marostegui> Deploy alter table enwiki.revision db1089 - T132416

db1089 done

root@neodymium:/home/marostegui/databases_s2# mysql --skip-ssl -hdb1089 enwiki -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) DEFAULT 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=770113729 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-03-13T14:25:49Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1089 - T132416 (duration: 00m 41s)

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-03-13T14:32:45Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1083 - T132416 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-03-13T14:33:13Z] <marostegui> Deploy alter table enwiki.revision db1083 - T132416

db1083:

root@PRODUCTION s1[enwiki]> 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) DEFAULT 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=770237405 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-03-14T07:01:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1083 - T132416 (duration: 00m 41s)

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-03-14T07:07:03Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1080 - T132416 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-03-14T07:07:48Z] <marostegui> Deploy alter table enwiki.revision db1080 - T132416

db1080 is done but I am wondering if we should continue altering the pending slaves or wait for this to be fixed: T159319#3097521

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1080 enwiki -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) DEFAULT 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=770280041 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-03-14T14:36:30Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1080 - T132416 (duration: 00m 40s)

brion added a subscriber: brion.Mar 14 2017, 3:12 PM
daniel added a subscriber: daniel.Mar 16 2017, 4:02 PM
jcrespo changed the task status from "Open" to "Stalled".Tue, Mar 28, 1:02 PM

@Catrope Mediawiki "official" indexes do not seem to work on enwiki. Please advise how to proceed because this task is blocked by T159319.

jcrespo added a subtask: Restricted Task.Tue, Mar 28, 1:03 PM

@Catrope Mediawiki "official" indexes do not seem to work on enwiki. Please advise how to proceed because this task is blocked by T159319.

Is it blocked on anything else other than merging the patch attached to that task?

Is it blocked on anything else other than merging the patch attached to that task?

The whole task- those issues only appeared after the alters to make revision as mediawiki says it is, and not on the hosts that have not been altered yet. The patch is for one of the issues, but has not been reviewed, and only "patches" one of the issues, not the 3 of them.

Mentioned in SAL (#wikimedia-operations) [2017-03-31T09:52:08Z] <marostegui> Adding rev_timestamp index to revision page db1066 (s1) - T132416

db1066 is done:

root@neodymium:/home/marostegui# mysql --skip-ssl -hdb1066 enwiki -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) DEFAULT 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_page_id` (`rev_page`,`rev_id`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=773113492 DEFAULT CHARSET=binary
jcrespo changed the task status from "Stalled" to "Open".Mon, Apr 3, 11:26 AM

This can continue, now that the largest blockers on T159319 are gone- in fact, with the new changes, this is needed for a full resolution of that one.

This can continue, now that the largest blockers on T159319 are gone- in fact, with the new changes, this is needed for a full resolution of that one.

Great - I am now continuing with this ticket on its subtask for s7: T160390

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-04-10T14:48:04Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1073 - T132416 (duration: 00m 39s)

Mentioned in SAL (#wikimedia-operations) [2017-04-10T14:48:11Z] <marostegui> Deploy alter table enwiki.revision db1073 - T132416

Marostegui moved this task from Meta/Epic to In progress on the DBA board.Mon, Apr 10, 2:50 PM

db1073 is now done:

root@neodymium:~# mysql --skip-ssl -hdb1073 enwiki -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) DEFAULT 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=774874079 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-04-11T06:01:06Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1073 - T132416 (duration: 00m 39s)

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-04-11T06:26:45Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1072 - T132416 (duration: 00m 43s)

Mentioned in SAL (#wikimedia-operations) [2017-04-11T06:28:42Z] <marostegui> Deploy alter table enwiki.revision db1072 - T132416

dbstore2001 is done:

root@neodymium:/home/marostegui# mysql --skip-ssl -hdbstore2001.codfw.wmnet enwiki -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) DEFAULT 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=774714082 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Mentioned in SAL (#wikimedia-operations) [2017-04-11T06:50:05Z] <marostegui> Deploy alter table enwiki.revision dbstore1002 - T132416

db1072 is done:

root@neodymium:/home/marostegui# mysql --skip-ssl -hdb1072 enwiki -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) DEFAULT 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=775032243 DEFAULT CHARSET=binary

Mentioned in SAL (#wikimedia-operations) [2017-04-12T06:32:49Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1072 - T132416 (duration: 00m 46s)

Servers pending to alter on enwiki:

eqiad:

db1052 - master
db1067 
db1065
dbstore1002 (being altered since yesterday)
dbstore1001
db1069
db1095 (sanitarium2)
labsdb1001,1003,1009,1010,1011

codfw hosts are all done.

dbstore1002 finished after 3 days of altering:

root@neodymium:~# mysql --skip-ssl -hdbstore1002 enwiki -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) DEFAULT 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`,`rev_user`,`rev_deleted`,`rev_minor_edit`,`rev_text_id`,`rev_comment`),
  KEY `rev_timestamp_user` (`rev_timestamp`,`rev_user`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=TokuDB AUTO_INCREMENT=775810695 DEFAULT CHARSET=binary `compression`='tokudb_zlib'

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-04-20T07:31:55Z] <marostegui@naos> Synchronized wmf-config/db-eqiad.php: Depool db1065 - T132416 (duration: 02m 18s)

Mentioned in SAL (#wikimedia-operations) [2017-04-20T07:53:04Z] <marostegui> Deploy alter table enwiki.revision db1065 - https://phabricator.wikimedia.org/T132416

db1065 is done:

root@neodymium:~# mysql --skip-ssl -hdb1065 enwiki -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) DEFAULT 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_page_id` (`rev_page`,`rev_id`),
  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_timestamp` (`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=776371693 DEFAULT CHARSET=binary
Krinkle removed a subscriber: Krinkle.Fri, Apr 21, 2:53 AM

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2017-04-21T06:09:23Z] <marostegui> Deploy alter table enwiki.revision db1067 - T132416

db1067 is done:

root@neodymium:~# mysql -hdb1067 -A enwiki -e "show create table revision\G" --skip-ssl
*************************** 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) DEFAULT 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_page_id` (`rev_page`,`rev_id`),
  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_timestamp` (`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=776519854 DEFAULT CHARSET=binary

The only core production host pending is the master, db1052, but I am not sure if we should continue with the same alter or not include the rev_page_id as per: T163532 what do you guys @jcrespo @tstarling think?
The master currently has it as an UNIQUE key like all the hosts had, so I can just go ahead and fix the PK and set it as rev_id which is the only pending thing on it (if we do not want to add rev_page_id).

Mentioned in SAL (#wikimedia-operations) [2017-04-24T06:23:36Z] <marostegui> Deploy alter table enwiki.revision db1052 (eqiad master) - T132416

Mentioned in SAL (#wikimedia-operations) [2017-04-24T08:02:59Z] <marostegui> Deploy alter table enwiki.revision on db1095 (sanitarium2) - T132416

db1052 eqiad master is done:

root@neodymium:~# mysql --skip-ssl enwiki -e "show create table revision\G" -hdb1052
*************************** 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) DEFAULT 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=777095692 DEFAULT CHARSET=binary

db1095 (sanitarium2 is done too):

root@neodymium:~# mysql --skip-ssl enwiki -e "show create table revision\G" -hdb1095
*************************** 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) DEFAULT 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=777095717 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Mentioned in SAL (#wikimedia-operations) [2017-04-25T05:41:03Z] <marostegui> Deploy alter table enwiki.revision on labsdb1009 and labsdb1010 - T132416

db1095 is done:

root@neodymium:/home/marostegui/git/software/dbtools# mysql --skip-ssl -hdb1095 -e "show create table revision\G" enwiki
*************************** 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) DEFAULT 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`)