Page MenuHomePhabricator

Linker::getRollbackEditCount queries are forced into a suboptimal execution plan due to index forcing
Closed, ResolvedPublic

Description

The page https://undertale-au-fanon.fandom.com/wiki/Special:RecentChanges is extremely slow (15+ seconds) to load for users with rollback rights. The wiki is running on MW 1.33 and Percona Server for MySQL 5.7.

Some brief profiling implicates queries from Linker::getRollbackEditCount. It looks like this query can take several seconds to execute due to index forcing generating a suboptimal execution plan:

MySQL [undertaleaufanon]> explain SELECT  rev_user_text,rev_deleted  FROM `revision` FORCE INDEX (page_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))   WHERE rev_page = '80'  ORDER BY rev_timestamp DESC LIMIT 11\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: temp_rev_comment
   partitions: NULL
         type: index
possible_keys: PRIMARY,revcomment_rev
          key: revcomment_rev
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_rev_comment
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: undertaleaufanon.temp_rev_comment.revcomment_comment_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: revision
   partitions: NULL
         type: ref
possible_keys: page_timestamp
          key: page_timestamp
      key_len: 4
          ref: const
         rows: 584
     filtered: 100.00
        Extra: Using index condition
3 rows in set, 1 warning (0.11 sec)

When I remove the index forcing the query plan is significantly improved:

MySQL [undertaleaufanon]> explain SELECT  rev_user_text,rev_deleted  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))   WHERE rev_page = '80'  ORDER BY rev_timestamp DESC LIMIT 11\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: revision
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,page_timestamp,page_user_timestamp,rev_page_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: temp_rev_comment
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,revcomment_rev
          key: revcomment_rev
      key_len: 4
          ref: undertaleaufanon.revision.rev_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_rev_comment
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: undertaleaufanon.temp_rev_comment.revcomment_comment_id
         rows: 1
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.11 sec)

First query takes 5+ seconds to execute, second query completes in below 100ms.

Event Timeline

TK-999 created this task.Aug 21 2020, 2:32 PM
Restricted Application added a project: Growth-Team. · View Herald TranscriptAug 21 2020, 2:32 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Looking at the latest source code, it seems the index forcing is still present there as well. It may be worth to consider removing it if we can verify it doesn't hurt the performance on MariaDB.

The query, along with the index forcing, was added in rMWa53fe6f080a2dfe48ed77c09af9e09ef37085408 and not really modified since.

TK-999 added a subscriber: hoo.

Tagging Platform as potential stakeholders and @hoo as the original author of the query :)

The query also performs well with the index forcing if I get rid of the JOINs on the comment tables, since they are never actually used by this query.

Looking in tendril for slow queries that has this index forced says that there are some queries that are slow for us too and become way faster once you remove the forced index. Here's an example:

SELECT /* IndexPager::buildQueryInfo (history page unfiltered) */ 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`, user_name, (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` FORCE INDEX (page_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)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 702579 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 5001 /* c47cac4131b0f472ea37f87011a99294 db1084 enwiki 2s */

(Takes 2.76s with forced index and 0.21s without it). cc @Marostegui

Interestingly, https://www.mediawiki.org/wiki/Manual:$wgShowRollbackEditCount suggests that $wgMiserMode can be used to disable this counting of edits for rollback links rendering, but the code seems to perform the calculation anyways (in \Linker::generateRollback).

Looking in tendril for slow queries that has this index forced says that there are some queries that are slow for us too and become way faster once you remove the forced index. Here's an example:

SELECT /* IndexPager::buildQueryInfo (history page unfiltered) */ 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`, user_name, (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` FORCE INDEX (page_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)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user)) WHERE rev_page = 702579 ORDER BY rev_timestamp DESC, rev_id DESC LIMIT 5001 /* c47cac4131b0f472ea37f87011a99294 db1084 enwiki 2s */

(Takes 2.76s with forced index and 0.21s without it). cc @Marostegui

I cannot reproduce it. On db1089 (enwiki) the FORCE still works better, 0.2 secs vs 3.1 secs (this is 10.1).
On 10.4 (db1084) it is the same.

eprodromou added a subscriber: eprodromou.

We're happy to look at a patch, or give advice if needed, but we don't see much for us in this ticket. Feel free to re-tag us if you need our help.

@TK-999 I'm very interested in the Percona aspect of this. Have you tried my Percona driver ??

I doubt it would solve this problem, but I'm curious about how you're approaching the different index handling.

@TK-999 I'm very interested in the Percona aspect of this. Have you tried my Percona driver ??

I doubt it would solve this problem, but I'm curious about how you're approaching the different index handling.

Thanks for the link! We're not using Percona XtraDB, though, but rather Percona Server which is basically just Oracle's MySQL with additional fixes.

After some investigation, this problem may be simply down to index stats not being recalculated on certain replicas after reimporting a wiki DB during the upgrade process, so I think I am going to close this ticket as not reproducible unless the issue rears its head again.

TK-999 closed this task as Resolved.Aug 25 2020, 8:41 PM