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.
| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| Remove redundant UNIQUE from rev_page_id | mediawiki/core | master | +27 -1 |
Change 282807 had a related patch set uploaded (by Aaron Schulz):
Remove redundant UNIQUE from rev_page_id
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.
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
s7 (arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki) is 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.