Page MenuHomePhabricator
Create Task
Maniphest T132416

Rampant differences in indexes on enwiki.revision across the DB cluster
Closed, ResolvedPublic
Actions

Assigned To
Marostegui
Authored By
Catrope
Apr 12 2016, 12:58 AM
Tags
Referenced Files
None
Subscribers
aaron
Aklapper
ArielGlenn
• brooke
Catrope
daniel
Danny_B
View All 28 Subscribers
Tokens
"Orange Medal" token, awarded by ssastry."Orange Medal" token, awarded by Krinkle."Yellow Medal" token, awarded by Jdforrester-WMF."Yellow Medal" token, awarded by aaron."Love" token, awarded by jcrespo."Love" token, awarded by Ladsgroup.

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

Details

Show related patches 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
Stashbot added a comment.Mar 14 2017, 7:07 AM

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

Marostegui closed subtask T159414: Rampant differences in indexes and PK on s6 (frwiki, jawiki, ruwiki) for revision table as Resolved.Mar 14 2017, 7:20 AM
Marostegui mentioned this in T142725: Clean up revision UNIQUE indexes.Mar 14 2017, 7:24 AM
Marostegui added a comment.Mar 14 2017, 2:03 PM

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
Stashbot added a comment.Mar 14 2017, 2:36 PM

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

• brooke subscribed.Mar 14 2017, 3:12 PM
daniel subscribed.Mar 16 2017, 4:02 PM
jcrespo changed the task status from Open to Stalled.Mar 28 2017, 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.Mar 28 2017, 1:03 PM
Catrope added a comment.Mar 28 2017, 2:50 PM
In T132416#3136759, @jcrespo wrote:

@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?

jcrespo added a comment.Mar 28 2017, 2:52 PM

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.

Marostegui mentioned this in T155099: Database maintenance scheduled while eqiad datacenter is non primary (after the DC switchover).Mar 31 2017, 8:20 AM
Stashbot added a comment.Mar 31 2017, 9:52 AM

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

Marostegui added a comment.Mar 31 2017, 11:18 AM

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.Apr 3 2017, 11:26 AM
jcrespo added a comment.Apr 3 2017, 11:39 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.

Marostegui added a comment.Apr 3 2017, 11:40 AM
In T132416#3150325, @jcrespo wrote:

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

gerritbot added a comment.Apr 10 2017, 2:27 PM

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

gerritbot added a comment.Apr 10 2017, 2:46 PM

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

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

Stashbot added a comment.Apr 10 2017, 2:48 PM

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

Stashbot added a comment.Apr 10 2017, 2:48 PM

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.Apr 10 2017, 2:50 PM
Nemo_bis subscribed.Apr 10 2017, 2:51 PM
Marostegui added a comment.Apr 11 2017, 5:56 AM

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
Stashbot added a comment.Apr 11 2017, 6:01 AM

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

gerritbot added a comment.Apr 11 2017, 6:22 AM

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

gerritbot added a comment.Apr 11 2017, 6:25 AM

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

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

Stashbot added a comment.Apr 11 2017, 6:26 AM

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

Stashbot added a comment.Apr 11 2017, 6:28 AM

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

Marostegui added a comment.Apr 11 2017, 6:49 AM

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
Stashbot added a comment.Apr 11 2017, 6:50 AM

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

Marostegui added a comment.Apr 12 2017, 6:25 AM

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
Stashbot added a comment.Apr 12 2017, 6:32 AM

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

Marostegui added a comment.Apr 12 2017, 8:24 AM

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.

Marostegui added a comment.Apr 17 2017, 7:07 AM

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'
gerritbot added a comment.Apr 20 2017, 7:25 AM

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

gerritbot added a comment.Apr 20 2017, 7:28 AM

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

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

Stashbot added a comment.Apr 20 2017, 7:31 AM

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

Stashbot added a comment.Apr 20 2017, 7:53 AM

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

jcrespo mentioned this in T12788: Filter page histories by user, or contributions by title.Apr 20 2017, 1:19 PM
jcrespo merged a task: T12788: Filter page histories by user, or contributions by title.Apr 20 2017, 1:32 PM
jcrespo added subscribers: Danny_B, Ricordisamoa, liangent and 8 others.
Anomie added a parent task: T12788: Filter page histories by user, or contributions by title.Apr 20 2017, 1:38 PM
Marostegui added a comment.Apr 20 2017, 4:15 PM

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
mgrabovsky unsubscribed.Apr 20 2017, 4:50 PM
Krinkle unsubscribed.Apr 21 2017, 2:53 AM
gerritbot added a comment.Apr 21 2017, 6:00 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

gerritbot added a comment.Apr 21 2017, 6:05 AM

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

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

Stashbot added a comment.Apr 21 2017, 6:09 AM

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

Marostegui mentioned this in T163532: Drop index rev_page_id (rev_page, rev_id).Apr 21 2017, 2:23 PM
Marostegui added a subscriber: tstarling.Apr 21 2017, 2:27 PM

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

Stashbot added a comment.Apr 24 2017, 6:23 AM

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

Stashbot added a comment.Apr 24 2017, 8:03 AM

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

MarkAHershberger unsubscribed.Apr 24 2017, 4:30 PM
Marostegui added a comment.Apr 25 2017, 5:37 AM

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
Stashbot added a comment.Apr 25 2017, 5:41 AM

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

Marostegui added a comment.Apr 25 2017, 10:18 AM

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`)
Marostegui added a comment.Apr 26 2017, 5:57 AM

labsdb1009 and labsdb1010 are done:

mysql:root@localhost [enwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1009 |
+------------+
1 row in set (0.00 sec)

mysql:root@localhost [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=777270736 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

mysql:root@localhost [enwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1010 |
+------------+
1 row in set (0.00 sec)

mysql:root@localhost [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=777270795 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.01 sec)
Stashbot added a comment.Apr 26 2017, 5:57 AM

Mentioned in SAL (#wikimedia-operations) [2017-04-26T05:57:23Z] <marostegui> Deploy alter table enwiki.revision on labsdb1011 - T132416

Marostegui added a comment.Apr 27 2017, 6:02 AM

labsdb1011 is done:

root@labsdb1011:~# mysql --skip-ssl 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=777445244 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
Marostegui added a comment.Apr 27 2017, 6:05 AM

Pending hosts:

dbstore1001
db1069 (sanitarium2)
labsdb1001
labsdb1003

I think I will not do db1069, labsdb1001 and labsdb1003 as they will be decommissioned soon. If someone feels strongly about it, just let me know.

Anomie added a comment.Apr 27 2017, 1:43 PM

How soon is soon? And nothing is going to be cloned from them in the mean time?

Marostegui added a comment.Apr 27 2017, 1:44 PM

We do not clone stuff from labs servers

Stashbot added a comment.Apr 27 2017, 2:04 PM

Mentioned in SAL (#wikimedia-operations) [2017-04-27T14:04:46Z] <marostegui> Deploy alter table labswiki.revision on silver - T132416

Stashbot added a comment.Apr 27 2017, 2:08 PM

Mentioned in SAL (#wikimedia-operations) [2017-04-27T14:08:22Z] <marostegui> Deploy alter table labswiki.revision on labtestweb2001 - T132416

Marostegui added a comment.Apr 27 2017, 2:09 PM

I have altered silver and labstestweb2001

mysql:root@localhost [labswiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| silver     |
+------------+
1 row in set (0.00 sec)

mysql:root@localhost [labswiki]> show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(10) unsigned NOT NULL,
  `rev_text_id` int(10) unsigned NOT NULL,
  `rev_comment` tinyblob NOT NULL,
  `rev_user` int(10) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(10) unsigned DEFAULT NULL,
  `rev_parent_id` int(10) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  `rev_content_format` varbinary(64) DEFAULT NULL,
  `rev_content_model` varbinary(32) 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=1757715 DEFAULT CHARSET=latin1 MAX_ROWS=10000000 AVG_ROW_LENGTH=1024
1 row in set (0.00 sec)

mysql:root@localhost [labtestwiki]> select @@hostname;
+----------------+
| @@hostname     |
+----------------+
| labtestweb2001 |
+----------------+
1 row in set (0.00 sec)

mysql:root@localhost [labtestwiki]> show create table revision\G
*************************** 1. row ***************************
       Table: revision
Create Table: CREATE TABLE `revision` (
  `rev_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(10) unsigned NOT NULL,
  `rev_text_id` int(10) unsigned NOT NULL,
  `rev_comment` varbinary(767) NOT NULL,
  `rev_user` int(10) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(10) unsigned DEFAULT NULL,
  `rev_parent_id` int(10) 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=29372 DEFAULT CHARSET=binary MAX_ROWS=10000000 AVG_ROW_LENGTH=1024
1 row in set (0.00 sec)
Marostegui added a comment.Apr 28 2017, 6:51 AM

labsdb1011 is done:

root@labsdb1011:~# mysql --skip-ssl 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=777615533 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
Marostegui added a comment.Apr 28 2017, 6:52 AM

This task is almost done. All the core + labs hosts are done on both dcs.
The only pending host is dbstore1001 which will be done after the dc switchover.

Stashbot added a comment.May 4 2017, 12:28 PM

Mentioned in SAL (#wikimedia-operations) [2017-05-04T12:28:43Z] <marostegui> Deploy alter table enwiki.revision on dbstore1001 - T132416

jcrespo removed a subtask: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.May 4 2017, 1:52 PM
Marostegui added a comment.May 9 2017, 5:58 AM

After almost 5 days, dbstore1001 is done:

root@neodymium:~# mysql --skip-ssl -hdbstore1001 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 `rev_page_id` (`rev_page`,`rev_id`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
) ENGINE=TokuDB AUTO_INCREMENT=778727807 DEFAULT CHARSET=binary `compression`='tokudb_zlib'
Marostegui closed this task as Resolved.May 9 2017, 6:42 AM
Marostegui claimed this task.

The scope of this ticket (enwiki) is done.
All the hosts have the new table schema in place.
The only hosts not altered are the old labs infra hosts: db1069 (sanitarium), labsdb1001 and labsdb1003. If someone feels they do need to get the new indexes and PK, please let me know.

jcrespo awarded a token.May 9 2017, 6:45 AM
aaron awarded a token.May 9 2017, 8:02 PM
jcrespo closed subtask Restricted Task as Resolved.May 10 2017, 9:41 AM
Jdforrester-WMF awarded a token.May 10 2017, 4:47 PM
Marostegui closed subtask T142725: Clean up revision UNIQUE indexes as Resolved.Jun 6 2017, 9:06 AM
jcrespo mentioned this in T71127: Discrepancies with logging table on different wikis.Aug 7 2017, 8:20 AM
Marostegui reopened this task as Open.Apr 12 2018, 1:43 PM

T191996#4126856 db1066 needs fixing for the indexes

gerritbot added a comment.Apr 12 2018, 4:16 PM

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

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

gerritbot added a comment.Apr 12 2018, 4:19 PM

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

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

Stashbot added a comment.Apr 12 2018, 4:21 PM

Mentioned in SAL (#wikimedia-operations) [2018-04-12T16:21:48Z] <marostegui> Deploy schema change on db1066 - T132416

Marostegui closed this task as Resolved.Apr 13 2018, 5:17 AM

db1066 is now fixed

root@neodymium:~# mysql -hdb1066.eqiad.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 '',
  `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 `rev_timestamp` (`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`)
) ENGINE=InnoDB AUTO_INCREMENT=836184607 DEFAULT CHARSET=binary
Marostegui mentioned this in T263127: Remove groups from db configs.Sep 17 2020, 1:51 PM
Aklapper removed subscribers: • wikibugs-l-list, • RobLa-WMF, Anomie.Oct 16 2020, 5:40 PM
Ladsgroup mentioned this in T312538: Collect list of tickets done for fixing core drifts .Jul 7 2022, 1:10 PM
Maintenance_bot moved this task from In progress to Done on the DBA board.Jul 7 2022, 1:29 PM
Krinkle awarded a token.Aug 22 2022, 1:32 PM
• ssastry awarded a token.Aug 22 2022, 3:52 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