Simple filter restrictions on meta causes Recent Changes to time out
Closed, ResolvedPublicBUG REPORT
Actions

Assigned To

Authored By

	Count_Count
	Sat, Jun 8, 5:26 AM

Description

Recent changes on meta times out if certain simple filter restrictions are used.

One instance described below.

Steps to replicate the issue (include links if applicable):

Go to the default Recent Changes (https://meta.wikimedia.org/wiki/Special:RecentChanges)
Restrict to registered users only (https://meta.wikimedia.org/wiki/Special:RecentChanges?userExpLevel=registered&hidebots=1&translations=filter&hidecategorization=1&hideWikibase=1&limit=50&days=7&urlversion=2)

What happens?:

Search times out.

What should have happened instead?:

Recent changes should be displayed.

Other information (browser name/version, screenshots, etc.):

Testes in Chrome and Safari on MacOS.

Event Timeline

Count_Count created this task.Sat, Jun 8, 5:26 AM

Restricted Application added a project: Growth-Team. · View Herald TranscriptSat, Jun 8, 5:26 AM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Aklapper added a project: Wikimedia-Slow-DB-Query.Sat, Jun 8, 5:04 PM

kostajh subscribed.Sat, Jun 8, 7:53 PM

Another one:
https://meta.wikimedia.org/w/index.php?userExpLevel=unregistered%3Bnewcomer&hidebots=1&hidecategorization=1&hideWikibase=1&hidelog=1&hidenewuserlog=1&namespace=1198&invert=1&tagfilter=mw-reverted&inverttags=1&limit=50&days=7&title=Special:RecentChanges&urlversion=2

One of the queries that are failing:

mysql:research@s7-analytics-replica.eqiad.wmnet [metawiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50 ;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                                                       | rows     | Extra                                                  |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+
|    1 | PRIMARY            | recentchanges_actor | range  | PRIMARY,actor_user                                                                                                    | actor_user   | 5       | NULL                                                                      | 11268769 | Using index condition; Using temporary; Using filesort |
|    1 | PRIMARY            | recentchanges       | ref    | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor     | 8       | metawiki.recentchanges_actor.actor_id                                     | 42       | Using index condition; Using where                     |
|    1 | PRIMARY            | watchlist           | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title                                                              | wl_user      | 265     | const,metawiki.recentchanges.rc_namespace,metawiki.recentchanges.rc_title | 1        |                                                        |
|    1 | PRIMARY            | watchlist_expiry    | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.watchlist.wl_id                                                  | 1        | Using where                                            |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id                                      | 1        |                                                        |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id                                          | 1        |                                                        |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 9       | metawiki.recentchanges.rc_id                                              | 1        | Using index                                            |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id                                             | 1        |                                                        |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+
8 rows in set (0.003 sec)

clearly picking the wrong index. Let me optimize the table.

Nope, it didn't fix it.

Why it's picking actor table first? there is no high cardinality condition on it (and even if you remove (((actor_user IS NOT NULL))), it still picks actor first). I thought it's the straight join there but removing that didn't make a difference.

cc. @Marostegui

Which host is this?

I think all would be reproducible but I tested it on dbstore1008:3317

Michael moved this task from Inbox to Triaged on the Growth-Team board.Tue, Jun 11, 6:00 PM

db1227 is the only host where the optimizer is fine and the query runs perfectly fast:

cumin2024@db1227.eqiad.wmnet[metawiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                                                       | rows   | Extra       |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14      | NULL                                                                      | 145000 | Using where |
|    1 | PRIMARY            | watchlist           | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title                                                              | wl_user      | 265     | const,metawiki.recentchanges.rc_namespace,metawiki.recentchanges.rc_title | 1      |             |
|    1 | PRIMARY            | watchlist_expiry    | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.watchlist.wl_id                                                  | 1      | Using where |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY,actor_user                                                                                                    | PRIMARY      | 8       | metawiki.recentchanges.rc_actor                                           | 1      | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id                                      | 1      |             |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id                                          | 1      |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 9       | metawiki.recentchanges.rc_id                                              | 1      | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id                                             | 1      |             |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
8 rows in set (0.009 sec)

It has nothing to do with the MariaDB versions as we have a mix of versions and other 10.6.17 behave weirdly. I've upgraded db1191 to 10.6.18 but there's no change. I will try to see what we can do.

Any chances the query can be written in a different way like:

cumin2024@dbstore1008.eqiad.wmnet[metawiki]> EXPLAIN SELECT      rc_id,     rc_timestamp,     rc_namespace,     rc_title,     rc_minor,     rc_bot,     rc_new,     rc_cur_id,     rc_this_oldid,     rc_last_oldid,     rc_type,     rc_source,     rc_patrolled,     rc_ip,     rc_old_len,     rc_new_len,     rc_deleted,     rc_logid,     rc_log_type,     rc_log_action,     rc_params,     rc_actor,     recentchanges_actor.actor_user AS `rc_user`,     recentchanges_actor.actor_name AS `rc_user_text`,     comment_rc_comment.comment_text AS `rc_comment_text`,     comment_rc_comment.comment_data AS `rc_comment_data`,     comment_rc_comment.comment_id AS `rc_comment_cid`,     rc_title,     rc_namespace,     wl_user,     wl_notificationtimestamp,     we_expiry,     page_latest,     (SELECT GROUP_CONCAT(ctd_name SEPARATOR ',')       FROM `change_tag`       JOIN `change_tag_def` ON (ct_tag_id = ctd_id)       WHERE ct_rc_id = rc_id) AS `ts_tags` FROM `recentchanges` STRAIGHT_JOIN `actor` `recentchanges_actor` ON (actor_id = rc_actor) LEFT JOIN `comment` `comment_rc_comment` ON (comment_id = rc_comment_id) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND wl_title = rc_title AND wl_namespace = rc_namespace) LEFT JOIN `watchlist_expiry` ON (wl_id = we_item) LEFT JOIN `page` ON (rc_cur_id = page_id) WHERE actor_user IS NOT NULL AND rc_bot = 0  AND rc_type != 6  AND rc_source != 'wb'  AND rc_namespace NOT IN (1198, 1199, 866, 867) AND rc_timestamp >= '20240601170235' AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC  LIMIT 50;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                                                       | rows   | Extra       |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14      | NULL                                                                      | 160905 | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id                                      | 1      |             |
|    1 | PRIMARY            | watchlist           | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title                                                              | wl_user      | 265     | const,metawiki.recentchanges.rc_namespace,metawiki.recentchanges.rc_title | 1      |             |
|    1 | PRIMARY            | watchlist_expiry    | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.watchlist.wl_id                                                  | 1      | Using where |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id                                          | 1      |             |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY,actor_user                                                                                                    | PRIMARY      | 8       | metawiki.recentchanges.rc_actor                                           | 1      | Using where |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 9       | metawiki.recentchanges.rc_id                                              | 1      | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id                                             | 1      |             |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
8 rows in set (0.003 sec)

I don't know how doable this is in MW, but looks like doing it that way is going to pick the right thing? Thoughts?

The SQL is a bit messy so I might have missed more changes, but the one I found is that you're doing straight join of RC to actor?That's technically possible but there are two problem:

IIRC, currently mw doesn't allow straight join of one table, adding STRAIGHT JOIN option makes the whole order of joins on all tables to become straight and that can cause issues.
Since in RC you can mix and match criteria so much, there might be cases that straight join could cause large issues (e.g. in wikidata if you set the filter to see only edits by logged out users, it'd add actor_user is null there)

Yeah, I know changing that code is probably not ideal. I'll try to see what I can do with the optimizer stats and reproduce the same plan db1227 uses.

In T366982#9890146, @Marostegui wrote:

Yeah, I know changing that code is probably not ideal. I'll try to see what I can do with the optimizer stats and reproduce the same plan db1227 uses.

Yeah, in some parts of mediawiki it's more doable than others. RC and watchlist specifically have always been a pain to add optimizer hints to :(

In T366982#9888380, @Ladsgroup wrote:

IIRC, currently mw doesn't allow straight join of one table, adding STRAIGHT JOIN option makes the whole order of joins on all tables to become straight and that can cause issues.

There is JoinGroupBase::straightJoin to have only one straigt join, there is also SelectQueryBuilder::straightJoinOption to make all straigt joins.

I have fixed dbstore1008:3317 by importing the index stats from db1227:

cumin2024@dbstore1008.eqiad.wmnet[metawiki]> explain SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50 ;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
| id   | select_type        | table               | type   | possible_keys                                                                                                         | key          | key_len | ref                                                                       | rows   | Extra       |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
|    1 | PRIMARY            | recentchanges       | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14      | NULL                                                                      | 141164 | Using where |
|    1 | PRIMARY            | watchlist           | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title                                                              | wl_user      | 265     | const,metawiki.recentchanges.rc_namespace,metawiki.recentchanges.rc_title | 1      |             |
|    1 | PRIMARY            | watchlist_expiry    | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.watchlist.wl_id                                                  | 1      | Using where |
|    1 | PRIMARY            | recentchanges_actor | eq_ref | PRIMARY,actor_user                                                                                                    | PRIMARY      | 8       | metawiki.recentchanges.rc_actor                                           | 1      | Using where |
|    1 | PRIMARY            | comment_rc_comment  | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 8       | metawiki.recentchanges.rc_comment_id                                      | 1      |             |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.recentchanges.rc_cur_id                                          | 1      |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rc_tag_id,ct_tag_id_id                                                                                             | ct_rc_tag_id | 9       | metawiki.recentchanges.rc_id                                              | 1      | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                                                                               | PRIMARY      | 4       | metawiki.change_tag.ct_tag_id                                             | 1      |             |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+--------+-------------+
8 rows in set (0.004 sec)

Now the query runs in no time:

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50 ;

<snip>
50 rows in set (0.007 sec)

I am going to do the same with some other hosts to confirm this works well.

This worked well on db2220 too:

cumin2024@db2220.codfw.wmnet[metawiki]> SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50 ;
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted


>>>> index stats imports happens here <<<<

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE (ct_rc_id=rc_id)  ) AS `ts_tags`  FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 50 ;
+----------+----------------+--------------+------------------------------------------------+----------+--------+--------+-----------+---------------+---------------+---------+-----------+--------------+-----------------------------------------+------------+------------+>
| rc_id    | rc_timestamp   | rc_namespace | rc_title                                       | rc_minor | rc_bot | rc_new | rc_cur_id | rc_this_oldid | rc_last_oldid | rc_type | rc_source | rc_patrolled | rc_ip                                   | rc_old_len | rc_new_len |>
+----------+----------------+--------------+------------------------------------------------+----------+--------+--------+-----------+---------------+---------------+---------+-----------+--------------+-----------------------------------------+------------+------------+>
| 31472455 | 20240614052442 |            2 | KentonRanford@global
<snip>

50 rows in set (0.071 sec)

I am going to do a few more codfw hosts and leave it running for the weekend - I don't want to do all of them just in case some other queries behave strangely. They shouldn't as db1227 is working fine.

Fixed db1170 too. Leaving it like this till Monday.

Thank you!

Umherirrender unsubscribed.Sun, Jun 16, 11:35 AM

Marostegui claimed this task.Tue, Jun 18, 9:21 AM

codfw is now fixed.

Marostegui triaged this task as Medium priority.Tue, Jun 18, 9:31 AM

eqiad fixed.

All host are now using the correct query plan.

Maintenance_bot moved this task from In progress to Done on the DBA board.Wed, Jun 19, 7:29 AM

Thanks a lot!

Simple filter restrictions on meta causes Recent Changes to time outClosed, ResolvedPublicBUG REPORTActions

Description

Event Timeline

Simple filter restrictions on meta causes Recent Changes to time out
Closed, ResolvedPublicBUG REPORT
Actions