While working on T223151 we realised that the optimizer is choosing the wrong plan in certain hosts for the following query (T223151#5599272):
SELECT /* Title::getFirstRevision xx */ 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` 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)) WHERE rev_page = '18762503' ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
This is the index chose across enwiki:
dbstore1003.eqiad.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 24848 Using where; Using filesort db2130.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db2116.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db2112.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 23624 Using where; Using filesort db2103.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 21700 Using where; Using filesort db2102.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 27758 Using where; Using filesort db2097.codfw.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db2094.codfw.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25550 Using where db2092.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 25350 Using where; Using filesort db2088.codfw.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,rev_page_id,page_user_timestamp page_timestamp 4 const 5124 Using where db2085.codfw.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,rev_page_id,page_user_timestamp page_timestamp 4 const 5025 Using where db2072.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db2071.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 25350 Using where; Using filesort db2048.codfw.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 23924 Using where; Using filesort db1139.eqiad.wmnet:3311 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_user_timestamp 4 const 25614 Using where; Using filesort db1134.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db1124.eqiad.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25550 Using where db1119.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_timestamp 4 const 27054 Using where db1118.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_user_timestamp 4 const 24504 Using where; Using filesort db1114.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_timestamp 4 const 24974 Using where db1106.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_timestamp 4 const 27216 Using where db1105.eqiad.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 5214 Using where db1099.eqiad.wmnet:3311 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 5098 Using where db1089.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_user_timestamp 4 const 25706 Using where; Using filesort db1083.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 25896 Using where db1080.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,page_timestamp,page_user_timestamp,rev_page_id page_timestamp 4 const 24130 Using where db1067.eqiad.wmnet:3306 1 SIMPLE revision ref PRIMARY,rev_page_id,page_timestamp,page_user_timestamp page_timestamp 4 const 25896 Using where
Per @Anomie's comment at T223151#5600630:
There's no reason for db1089 not to use the other plan, the rev_user column included in the page_user_timestamp index isn't referenced anywhere. Seems like one of those cases where it just chooses a wrong plan for no obvious reason. If it becomes a problem we could always ignore the index or force the right one.
This is the difference on db1099:3311 and db1089:
db1099:3311 root@db1099.eqiad.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */ 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` 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)) WHERE rev_page = '18762503' ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1; +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+ | 1 | SIMPLE | revision | ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_timestamp | 4 | const | 5100 | 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 | 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 | | +------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+ 5 rows in set (0.00 sec) db1089 (non partitioned): root@db1089.eqiad.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */ 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` 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)) WHERE rev_page = '18762503' ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1; +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ | 1 | SIMPLE | revision | ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4 | const | 25706 | Using where; Using filesort | | 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 | | | 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 | | +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ 5 rows in set (0.00 sec)
The query still runs fast with the wrong index 0.16 vs 0.01, but it is something to keep in mind.
An analyze table revision on db2092 didn't change the query plan:
root@db2092.codfw.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */ 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` 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)) WHERE rev_page = '18762503' ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1; +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ | 1 | SIMPLE | revision | ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4 | const | 25350 | Using where; Using filesort | | 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 | 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 | | +------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+ 5 rows in set (0.04 sec)
We can try to rebuild the table entirely to see if that helps.