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
ResolvedNone
Duplicate leucosticte
DeclinedNone
Resolveddaniel
Resolvedmatthiasmullie
Resolvedjcrespo
DuplicateNone
Resolvedjcrespo
ResolvedCatrope
ResolvedCatrope
Resolvedjcrespo
ResolvedTTO
OpenMarostegui
OpenNone
OpenNone
ResolvedAddshore
OpenAddshore
ResolvedKrinkle
OpenNone
Openbrion
Resolveddaniel
Resolveddaniel
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
OpenNone
ResolvedMarostegui
OpenNone
OpenNone
ResolvedMarostegui
ResolvedMarostegui
OpenNone
ResolvedMarostegui
ResolvedMarostegui
DeclinedSpringle
There are a very large number of changes, so older changes are hidden. Show Older Changes
jcrespo moved this task from Next to Meta/Epic on the DBA board.Nov 10 2016, 12:04 PM
Marostegui added a comment.EditedJan 4 2017, 7:57 AM

Following up a discussion Jaime and myself had yesterday...
What about starting to work on enwiki.revision on codfw?
If we are going to do a DC switchover at some point, we can start with codfw clean and while eqiad is on sby mode, we can fix the differences there.

The codfw this is the situation now:

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s1.hosts  | awk -F " " '{print $1}' | grep codfw| egrep -v "db10151|db1055|db1069|db2042|dbstore2001"`; do echo "***$i***"; mysql --skip-ssl -h$i -A enwiki -e "show create table revision\G" | grep "KEY" ;done


***db2034.codfw.wmnet***
db2034 is being altered now for testing (T149553#2912725)
***db2048.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
***db2055.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
***db2062.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
***db2069.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
***db2070.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
***dbstore2002.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=758083497 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
***db2016.codfw.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`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)

We need to keep in mind this: T102532 - and decide if we want to keep it (for consistency) or what.

Marostegui added a comment.EditedJan 20 2017, 1:22 PM

It took around 4 hours to ALTER db2047:

root@neodymium:/home/marostegui/git# mysql --skip-ssl metawiki -e "show create table pagelinks\G" --skip-ssl -hdb2047.codfw.wmnet
*************************** 1. row ***************************
       Table: pagelinks
Create Table: CREATE TABLE `pagelinks` (
  `pl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `pl_namespace` int(11) NOT NULL DEFAULT '0',
  `pl_title` varbinary(255) NOT NULL DEFAULT '',
  `pl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`) KEY_BLOCK_SIZE=4,
  KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`) KEY_BLOCK_SIZE=4,
  KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

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

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

Change 338707 merged by jenkins-bot:
db-codfw.php: Depool db2048

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

Mentioned in SAL (#wikimedia-operations) [2017-02-20T07:28:34Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2048 - T132416 (duration: 00m 41s)

Mentioned in SAL (#wikimedia-operations) [2017-02-20T07:29:58Z] <marostegui> Deploy alter table on db2048 enwiki.revision - T132416

I have started to ALTER db2048 enwiki.revision with the following command: ./osc_host.sh --host=db2048.codfw.wmnet --port=3306 --db=enwiki --table=revision --method=ddl --no-replicate "drop key rev_id, drop primary key, add primary key (rev_id), add key rev_page_id (rev_page,rev_id)"
The idea is to see how long it takes and try to get as many hosts as possible done before the switchover, and then do the same with eqiad.

db2048 finished correctly in less than 24 hours, so that is great news. I am going to go ahead and keep altering some more codfw hosts:

root@neodymium:~# /home/jynus/sql.py -h db2048.codfw.wmnet -e "show create table revision;" enwiki --no-dry-run

Results for db2048.codfw.wmnet:3306/enwiki:

| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|----------+----------------------------------------------------------|
| revision | 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=766628347 DEFAULT CHARSET=binary |

Change 338934 had a related patch set uploaded (by Marostegui):
db-codfw.php: Repool db2048, depool db2055

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

Change 338934 merged by jenkins-bot:
db-codfw.php: Repool db2048, depool db2055

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

Mentioned in SAL (#wikimedia-operations) [2017-02-21T07:29:05Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2048 and depool db2055 - T132416 (duration: 00m 51s)

Mentioned in SAL (#wikimedia-operations) [2017-02-21T07:31:37Z] <marostegui> Deploy alter table enwiki.revision db2055 - T132416

T158454 is about the rev_timestamp index on db1065's (vslow) enwiki database. Basically the CirrusSearch forceSearchIndex maintenance script, used to backfill updates from elasticsearch downtime queries the revision table by timestamp and on most db servers this is reasonably efficient, but on db1065 the lack of a rev_timestamp index causes a full table scan.

db2055 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`),
  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=766802798 DEFAULT CHARSET=binary

Change 339118 had a related patch set uploaded (by Marostegui):
db-codfw.php: Repool db2055 restore db2048 weight

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

Change 339118 merged by jenkins-bot:
db-codfw.php: Repool db2055 restore db2048 weight

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

Mentioned in SAL (#wikimedia-operations) [2017-02-22T07:18:36Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2055 - T132416 (duration: 00m 40s)

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

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

Change 339119 merged by jenkins-bot:
db-codfw.php: Depool db2062

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

Mentioned in SAL (#wikimedia-operations) [2017-02-22T07:23:17Z] <marostegui> Deploy alter table enwiki.revision db2062 - T132416

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

T158454 is about the rev_timestamp index on db1065's (vslow) enwiki database. Basically the CirrusSearch forceSearchIndex maintenance script, used to backfill updates from elasticsearch downtime queries the revision table by timestamp and on most db servers this is reasonably efficient, but on db1065 the lack of a rev_timestamp index causes a full table scan.

Hi!
As I mentioned on that ticket, we are trying to do this operation on the stand by datacenter (right now codfw) and once we do the switchover, we will do it on eqiad on as many hosts as possible so we can try to unify revision table as much as possible.
It is a very big table, so it takes time, depending on the server it can be hours or even days.

Thanks!

db2062:

*************************** 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=766977002 DEFAULT CHARSET=binary

Change 339350 had a related patch set uploaded (by Marostegui):
db-codfw.php: Repool db2062 - depool db2069

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

Change 339350 merged by jenkins-bot:
db-codfw.php: Repool db2062 - depool db2069

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

Mentioned in SAL (#wikimedia-operations) [2017-02-23T07:14:19Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2062 and depool db2069 - T132416 (duration: 00m 42s)

Mentioned in SAL (#wikimedia-operations) [2017-02-23T07:16:02Z] <marostegui> Deploy alter table enwiki.revision db2069 - T132416

db2069

| revision | 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=767155358 DEFAULT CHARSET=binary

Change 339602 had a related patch set uploaded (by Marostegui):
db-codfw.php: Repool db2069 depool db2070

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

Change 339602 merged by jenkins-bot:
db-codfw.php: Repool db2069 depool db2070

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

Mentioned in SAL (#wikimedia-operations) [2017-02-24T07:26:46Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2069 and depool db2070 - T132416 (duration: 00m 45s)

Mentioned in SAL (#wikimedia-operations) [2017-02-24T07:32:26Z] <marostegui> Deploy alter table enwiki.revision on db2070 - T132416

db2070:

       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=767666721 DEFAULT CHARSET=binary

Change 340078 had a related patch set uploaded (by Marostegui):
db-codfw.php: Repool db2070

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

Change 340078 merged by jenkins-bot:
db-codfw.php: Repool db2070

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

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

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.EditedMon, Feb 27, 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.EditedTue, Feb 28, 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.Tue, Mar 14, 3:12 PM
daniel added a subscriber: daniel.Thu, Mar 16, 4:02 PM