Page MenuHomePhabricator

Clean up revision UNIQUE indexes
Closed, ResolvedPublic

Description

See https://gerrit.wikimedia.org/r/#/c/282807/

Currently the revision tables either have:
PRIMARY KEY (rev_page,rev_id)
UNIQUE KEY (rev_id)

or...
PRIMARY KEY(rev_id)
UNIQUE KEY(rev_page,rev_id)

or...
PRIMARY KEY(rev_id)
KEY(rev_page,rev_id)

Only the last variant makes sense to me. All DBs should be normalized to this.

Event Timeline

aaron created this task.Aug 11 2016, 4:33 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 11 2016, 4:33 PM
aaron updated the task description. (Show Details)Aug 11 2016, 4:33 PM
jcrespo moved this task from Triage to Next on the DBA board.Aug 11 2016, 4:39 PM

Change 282807 had a related patch set uploaded (by Aaron Schulz):
Remove redundant UNIQUE from rev_page_id

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

Change 282807 merged by jenkins-bot:
Remove redundant UNIQUE from rev_page_id

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

Wait, this is not so direct; there is partitioning on the revision tables which on some wikis it is the difference between things directly not working and working, did you have that into account?

Note that I do not agree with mediawiki not knowing about that partitioning, but what should be the plan to support that (if any). It can be "not an issue for now", but eventually this will have to be handled.

To clarify, the scope of the ticket can be done with no issue; however, the "special slaves" have to maintain PRIMARY KEY (rev_id,rev_user) as they partition by user (or alternatively, get rid of the partitioning and search for an alternative for those multiple queries that would fail.

Marostegui moved this task from Next to In progress on the DBA board.Mar 13 2017, 9:47 AM
Marostegui moved this task from In progress to Next on the DBA board.Mar 13 2017, 9:52 AM
Marostegui added a subscriber: Marostegui.EditedMar 14 2017, 7:24 AM

An update on what's going on on the work we have been doing lately to try to unify our revision table and its PK and getting rid of the UNIQUE keys.
The following shards are done:

s1 (codfw is done, currently working on eqiad - T132416)
s4 (commons)
s5 (dewiki, wikidata) (wikidata is pending on the master and will be done once the DC switchover is done)
s6 (frwiki,jawiki,ruwiki)

Edit 11th April:
s7 (arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki) is done

Jaime has unified the recentchanges slaves across the board, so all those are done too.

Edit 25th May
s2 is done

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

s7 (arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki) is done

For the record we have faced this: T162774 while cleaning up some UNIQUE keys

s2 is done.

Pending only s3

Marostegui closed this task as Resolved.Jun 6 2017, 9:06 AM
Marostegui claimed this task.

All the shards are now done.

Thousands of alters have been done and checked but we could have missed some, if someone finds a wiki which is still not altered, please let me know.