Per T238966#6321601 we have now one host on enwiki serving production traffic that has MCR changes applied.
While we didn't see anything obvious when we altered s6, s2 and s5, I wanted to analyze slowly the changes before going for s4 (commonswiki).
On this host I have seen a query plan change on a host not running the MCR change (db1091) vs a host with the schema change applied (db1119)
MCR changes host
root@db1119.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRevisions::run */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, page_namespace, page_title, page_id, page_latest, page_is_redirect, page_len, user_name FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 534366 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 11 /* c541772d186a1d71a295aea404f6a2db db1119 enwiki 2s */; +------+-------------+---------------------+--------+-------------------------------------------------------------+--------------------------+---------+-----------------------------------------------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------------+--------+-------------------------------------------------------------+--------------------------+---------+-----------------------------------------------+-------+----------------+ | 1 | SIMPLE | page | const | PRIMARY | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | revision | ref | PRIMARY,rev_page_id,page_timestamp,rev_page_actor_timestamp | rev_page_actor_timestamp | 4 | const | 51898 | Using where | | 1 | SIMPLE | temp_rev_user | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | enwiki.revision.rev_id | 1 | Using index | | 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | | | 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rev_user.actor_user | 1 | Using where | | 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.revision.rev_id | 1 | Using index | | 1 | SIMPLE | comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_comment.revcomment_comment_id | 1 | | +------+-------------+---------------------+--------+-------------------------------------------------------------+--------------------------+---------+-----------------------------------------------+-------+----------------+ 7 rows in set (0.001 sec)
Non MCR host:
root@db1091.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRevisions::run */ rev_id, rev_page, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, comment_rev_comment.comment_text AS `rev_comment_text`, comment_rev_comment.comment_data AS `rev_comment_data`, comment_rev_comment.comment_id AS `rev_comment_cid`, actor_rev_user.actor_user AS `rev_user`, actor_rev_user.actor_name AS `rev_user_text`, temp_rev_user.revactor_actor AS `rev_actor`, page_namespace, page_title, page_id, page_latest, page_is_redirect, page_len, user_name FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 534366 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 11 /* c541772d186a1d71a295aea404f6a2db db1119 enwiki 2s */; +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+ | 1 | SIMPLE | page | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | revision | ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_timestamp | 4 | const | 50028 | Using where | | 1 | SIMPLE | temp_rev_user | eq_ref | PRIMARY,revactor_rev,actor_timestamp | revactor_rev | 4 | enwiki.revision.rev_id | 1 | Using index | | 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | | | 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rev_user.actor_user | 1 | Using where | | 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.revision.rev_id | 1 | Using index | | 1 | SIMPLE | comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_comment.revcomment_comment_id | 1 | | +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+ 7 rows in set (0.002 sec)
The query time on a non MCR host is around 0.002 (almost instant) and on the MCR host it is around 0.5 second, so that is some substantial increase for that given query.
If I force the query on the MCR host to use page_timestamp index like the non touched host does, the query goes back to original results (0.002), so almost instant. So using rev_page_actor_timestamp isn't ideal apparently.