Page MenuHomePhabricator

Fix revision special indexes and partitions on db1103:3314 and db1113:3316
Closed, ResolvedPublic

Description

@ArielGlenn reported to me in private that there might be some hosts with different PKs and indexes than the other ones for the same role for the revision table. After some checks it is indeed something to be fixed:

s4:
db1103:3314 is an rc slave that doesn't have special partitioning/PKs or PKs for revision. logging table is correct though.
Alter needed:

ALTER TABLE commonswiki.revision
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (rev_id, rev_user),
  DROP INDEX user_timestamp,
  ADD INDEX user_timestamp (rev_user, rev_timestamp, rev_id),
  DROP KEY rev_timestamp,
  ADD KEY rev_timestamp (rev_timestamp, rev_id),
  DROP KEY page_timestamp,
  ADD KEY page_timestamp (rev_page, rev_timestamp, rev_id),
  DROP KEY usertext_timestamp,
  ADD KEY usertext_timestamp (rev_user_text, rev_timestamp, rev_id),
  DROP KEY page_user_timestamp,
  ADD KEY page_user_timestamp (rev_page, rev_user, rev_timestamp, rev_id),
  DROP KEY rev_page_id,
  ADD KEY rev_page_id (rev_page, rev_id)
  PARTITION BY RANGE (rev_user) (
  PARTITION p1 VALUES LESS THAN (1),
  PARTITION p5000 VALUES LESS THAN (5000),
  PARTITION p10000 VALUES LESS THAN (10000),
  PARTITION p20000 VALUES LESS THAN (20000),
  PARTITION p30000 VALUES LESS THAN (30000),
  PARTITION p40000 VALUES LESS THAN (40000),
  PARTITION p50000 VALUES LESS THAN (50000),
  PARTITION p75000 VALUES LESS THAN (75000),
  PARTITION p100000 VALUES LESS THAN (100000),
  PARTITION p150000 VALUES LESS THAN (150000),
  PARTITION p200000 VALUES LESS THAN (200000),
  PARTITION p250000 VALUES LESS THAN (250000),
  PARTITION p300000 VALUES LESS THAN (300000),
  PARTITION p350000 VALUES LESS THAN (350000),
  PARTITION p400000 VALUES LESS THAN (400000),
  PARTITION p500000 VALUES LESS THAN (500000),
  PARTITION p750000 VALUES LESS THAN (750000),
  PARTITION p1000000 VALUES LESS THAN (1000000),
  PARTITION p1500000 VALUES LESS THAN (1500000),
  PARTITION p2000000 VALUES LESS THAN (2000000),
  PARTITION p3000000 VALUES LESS THAN (3000000),
  PARTITION p4000000 VALUES LESS THAN (4000000),
  PARTITION p5000000 VALUES LESS THAN (5000000),
  PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE );

s6:
db1113:3316 looks like it has special indexes on revision and it is an vslow/dump host, not rc slave.logging table is correct though.
Example:

PRIMARY KEY (`rev_id`),
KEY `rev_page_id` (`rev_page`,`rev_id`),
KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
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`)

And it should have:

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

ALTER needed:

ALTER TABLE revision DROP KEY IF EXISTS rev_page_id, DROP KEY IF EXISTS rev_timestamp, DROP KEY IF EXISTS page_timestamp, DROP KEY IF EXISTS user_timestamp, DROP KEY IF EXISTS usertext_timestamp, DROP KEY IF EXISTS page_user_timestamp, ADD KEY rev_timestamp (rev_timestamp),   ADD KEY page_timestamp (rev_page,rev_timestamp),   ADD KEY user_timestamp (rev_user,rev_timestamp),   ADD KEY usertext_timestamp (rev_user_text,rev_timestamp),   ADD KEY page_user_timestamp (rev_page,rev_user,rev_timestamp),   ADD KEY rev_page_id (rev_page,rev_id);

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 24 2019, 3:23 PM
Marostegui triaged this task as Normal priority.Apr 24 2019, 3:23 PM
Marostegui moved this task from Triage to In progress on the DBA board.

Change 506347 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1103:3314

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

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

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

Mentioned in SAL (#wikimedia-operations) [2019-04-25T05:27:19Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool db1103:3314 T221782 (duration: 00m 54s)

Mentioned in SAL (#wikimedia-operations) [2019-04-25T05:28:27Z] <marostegui> Deploy schema change on db1103:3314 to fix revision table partitioning and indexing - T221782

db1103:3314 is done and the table is now the same as db1097:3314:

Query OK, 326003615 rows affected (6 hours 17 min 9.99 sec)
Records: 326003615  Duplicates: 0  Warnings: 0


  PRIMARY KEY (`rev_id`,`rev_user`),
  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`),
  KEY `rev_page_id` (`rev_page`,`rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=347223206 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
/*!50100 PARTITION BY RANGE (rev_user)
(PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p5000 VALUES LESS THAN (5000) ENGINE = InnoDB,
 PARTITION p10000 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p20000 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION p30000 VALUES LESS THAN (30000) ENGINE = InnoDB,
 PARTITION p40000 VALUES LESS THAN (40000) ENGINE = InnoDB,
 PARTITION p50000 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION p75000 VALUES LESS THAN (75000) ENGINE = InnoDB,
 PARTITION p100000 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p150000 VALUES LESS THAN (150000) ENGINE = InnoDB,
 PARTITION p200000 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p250000 VALUES LESS THAN (250000) ENGINE = InnoDB,
 PARTITION p300000 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p350000 VALUES LESS THAN (350000) ENGINE = InnoDB,
 PARTITION p400000 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p500000 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p750000 VALUES LESS THAN (750000) ENGINE = InnoDB,
 PARTITION p1000000 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p1500000 VALUES LESS THAN (1500000) ENGINE = InnoDB,
 PARTITION p2000000 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p3000000 VALUES LESS THAN (3000000) ENGINE = InnoDB,
 PARTITION p4000000 VALUES LESS THAN (4000000) ENGINE = InnoDB,
 PARTITION p5000000 VALUES LESS THAN (5000000) ENGINE = InnoDB,
 PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Mentioned in SAL (#wikimedia-operations) [2019-04-25T12:39:52Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool db1103:3314 T221782 (duration: 00m 53s)

Change 506401 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1113:3316

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

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

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

Mentioned in SAL (#wikimedia-operations) [2019-04-25T12:48:45Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool db1113:3316 T221782 (duration: 00m 53s)

Mentioned in SAL (#wikimedia-operations) [2019-04-26T04:58:21Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool db1113:3316 T221782 (duration: 00m 56s)

Marostegui closed this task as Resolved.Apr 26 2019, 4:58 AM

db1113:3316 frwiki, jawiki and ruwiki got the revision table fixed and now have the proper indexes and PK:

  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=158754879 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8