Page MenuHomePhabricator

Slow query "IndexPager::buildQueryInfo (contributions page unfiltered)" after actor rollout
Closed, ResolvedPublic

Description

After setting the actor migration to write-both/read-new on group 1 (Ica3662baf4, ca. 13:50 UTC), I noticed an increase in slow queries resembling

SELECT /* IndexPager::buildQueryInfo (contributions page unfiltered)  */  rev_page,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,page_is_new,revactor_timestamp AS `rev_timestamp`,revactor_rev AS `rev_id`,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rev_id=rev_id  ) AS `ts_tags`  FROM `revision` 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` FORCE INDEX (actor_timestamp) 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 ((temp_rev_user.revactor_actor = '165')) AND ((rev_deleted & 12) != 12)  ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 501;

After some analysis, it seems that the change in rMWa88be858eeaa: Fix order on Special:Contributions when timestamps are identical broke the workaround in rMW59e856e3e64e: Improve some queries ordering by rev_timestamp with actor migration READ_NEW by adding rev_id to the ORDER BY clause. We now need to alias rev_id => revactor_rev as well.

Event Timeline

Change 504021 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@master] ContibsPager: Fix slow query with actor migration read-new mode

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

Change 504066 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@wmf/1.33.0-wmf.25] ContibsPager: Fix slow query with actor migration read-new mode

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

Change 504066 merged by jenkins-bot:
[mediawiki/core@wmf/1.33.0-wmf.25] ContibsPager: Fix slow query with actor migration read-new mode

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

Change 504021 merged by jenkins-bot:
[mediawiki/core@master] ContibsPager: Fix slow query with actor migration read-new mode

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

Fixed and backported. Log entries seem to have stopped.