(SELECT /* SpecialRecentChangesLinked::doMainQuery */ /*! STRAIGHT_JOIN */ rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,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,'0.73' AS `ores_damaging_threshold`,(SELECT GROUP_CONCAT(ct_tag SEPARATOR ',') FROM `change_tag` WHERE ct_rc_id=rc_id ) AS `ts_tags`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`,fp_stable,fp_pending_since FROM `recentchanges` LEFT JOIN `ores_model` `ores_damaging_mdl` ON (ores_damaging_mdl.oresm_is_current = '1' AND ores_damaging_mdl.oresm_name = 'damaging') LEFT JOIN `ores_classification` `ores_damaging_cls` ON ((ores_damaging_cls.oresc_model = ores_damaging_mdl.oresm_id) AND (rc_this_oldid = ores_damaging_cls.oresc_rev) AND ores_damaging_cls.oresc_class = '1') LEFT JOIN `ores_model` `ores_goodfaith_mdl` ON (ores_goodfaith_mdl.oresm_is_current = '1' AND ores_goodfaith_mdl.oresm_name = 'goodfaith') LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON ((ores_goodfaith_cls.oresc_model = ores_goodfaith_mdl.oresm_id) AND (rc_this_oldid = ores_goodfaith_cls.oresc_rev) AND ores_goodfaith_cls.oresc_class = '1') LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) INNER JOIN `pagelinks` ON ((rc_namespace = pl_namespace) AND (rc_title = pl_title)) WHERE (rc_bot = 0) AND (rc_type != '6') AND (rc_source != 'wb') AND (ores_damaging_cls.oresc_probability > '0.73') AND rc_patrolled = '0' AND (rc_timestamp >= '20170409000000') AND pl_from = '1341445' ORDER BY rc_timestamp DESC LIMIT 50 ) UNION (SELECT /*! STRAIGHT_JOIN */ rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,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,'0.73' AS `ores_damaging_threshold`,(SELECT GROUP_CONCAT(ct_tag SEPARATOR ',') FROM `change_tag` WHERE ct_rc_id=rc_id ) AS `ts_tags`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`,fp_stable,fp_pending_since FROM `recentchanges` LEFT JOIN `ores_model` `ores_damaging_mdl` ON (ores_damaging_mdl.oresm_is_current = '1' AND ores_damaging_mdl.oresm_name = 'damaging') LEFT JOIN `ores_classification` `ores_damaging_cls` ON ((ores_damaging_cls.oresc_model = ores_damaging_mdl.oresm_id) AND (rc_this_oldid = ores_damaging_cls.oresc_rev) AND ores_damaging_cls.oresc_class = '1') LEFT JOIN `ores_model` `ores_goodfaith_mdl` ON (ores_goodfaith_mdl.oresm_is_current = '1' AND ores_goodfaith_mdl.oresm_name = 'goodfaith') LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON ((ores_goodfaith_cls.oresc_model = ores_goodfaith_mdl.oresm_id) AND (rc_this_oldid = ores_goodfaith_cls.oresc_rev) AND ores_goodfaith_cls.oresc_class = '1') LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) INNER JOIN `templatelinks` ON ((rc_namespace = tl_namespace) AND (rc_title = tl_title)) WHERE (rc_bot = 0) AND (rc_type != '6') AND (rc_source != 'wb') AND (ores_damaging_cls.oresc_probability > '0.73') AND rc_patrolled = '0' AND (rc_timestamp >= '20170409000000') AND tl_from = '1341445' ORDER BY rc_timestamp DESC LIMIT 50 ) UNION (SELECT /*! STRAIGHT_JOIN */ rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,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,'0.73' AS `ores_damaging_threshold`,(SELECT GROUP_CONCAT(ct_tag SEPARATOR ',') FROM `change_tag` WHERE ct_rc_id=rc_id ) AS `ts_tags`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score`,fp_stable,fp_pending_since FROM `recentchanges` LEFT JOIN `ores_model` `ores_damaging_mdl` ON (ores_damaging_mdl.oresm_is_current = '1' AND ores_damaging_mdl.oresm_name = 'damaging') LEFT JOIN `ores_classification` `ores_damaging_cls` ON ((ores_damaging_cls.oresc_model = ores_damaging_mdl.oresm_id) AND (rc_this_oldid = ores_damaging_cls.oresc_rev) AND ores_damaging_cls.oresc_class = '1') LEFT JOIN `ores_model` `ores_goodfaith_mdl` ON (ores_goodfaith_mdl.oresm_is_current = '1' AND ores_goodfaith_mdl.oresm_name = 'goodfaith') LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON ((ores_goodfaith_cls.oresc_model = ores_goodfaith_mdl.oresm_id) AND (rc_this_oldid = ores_goodfaith_cls.oresc_rev) AND ores_goodfaith_cls.oresc_class = '1') LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) INNER JOIN `imagelinks` ON ((rc_title = il_to)) WHERE (rc_bot = 0) AND (rc_type != '6') AND (rc_source != 'wb') AND (ores_damaging_cls.oresc_probability > '0.73') AND rc_patrolled = '0' AND (rc_timestamp >= '20170409000000') AND il_from = '1341445' AND rc_namespace = '6' ORDER BY rc_timestamp DESC LIMIT 50 ) ORDER BY rc_timestamp DESC LIMIT 50
Description
Details
Related Objects
- Mentioned In
- T134976: SpecialRecentChangesLinked::doMainQuery blocking database infrastructure
T179718: Revive ORES filters on Related Changes - Mentioned Here
- T134976: SpecialRecentChangesLinked::doMainQuery blocking database infrastructure
T161525: gerritbot doesn't add comment/patch-for-review when User:Ladsgroup creates patch in Gerrit
Event Timeline
Change 348349 had a related patch set uploaded (by Jcrespo):
[operations/software@master] Database slave watchdog- kill all queries, not only the selects
Change 348349 merged by Jcrespo:
[operations/software@master] Database slave watchdog- kill all queries, not only the selects
SpecialRecentChangesLinked is super slow and it's extending ChangesList meaning lots of things will be hooked on it without anyone noticing. I will disable ORES in that special page to make it more performant but we need to seriously reconsider this special page structure and queries (and add proper indexes if needed and possible)
Gerrit-bot doesn't add comment when I make a patch (T161525)
- https://gerrit.wikimedia.org/r/#/c/348378/ Disable ORES in Recentchangeslinked
Thanks for getting the patch ready so fast.
Pardon my ignorance, but I thought I would ask: Is there any side effect on disabling this apart from getting it off? ie: by disabling this, some other feature/query gets more load?
No, the special page is barely used and even disabling the whole page doesn't affect much.
Thanks for clearing that up. I have +1'ed it. I guess this will be deployed after the switch DC right? As there are no deployments scheduled for this week.
Change 348378 merged by jenkins-bot:
[mediawiki/extensions/ORES@master] Disable ORES in Recentchangeslinked
I'm fine waiting till next week :-). This hasn't happened again since the query killer was changed. If this bite us again we might need to go ahead then
Thanks!
Exactly, It joins with a huge table on columns that can't be indexed (how we can index ores_classification based on pagelinks columns?)
Ladsgroup, I belive this to be a very serious Wikimedia-Incident causing user visible outage, paging Ops people, so yes, I think we should deploy it ASAP, as otherwise it will happen again. @greg has the last word, I would assume, on this topic.
yes, if Ops are comfortable with the deploy right now (I think the dust has settled with the dc-switch over). do it asap.
And yeah, I agree with @jcrespo that this is a Wikimedia-Incident and should get a writeup and follow-ups filed.
Gerribot doesn't make a comment when I make a patch:
- wmf-1.29.20 mediawiki/extensions/ORES Disable ORES in Recentchangeslinked https://gerrit.wikimedia.org/r/#/c/349271/
Change 349271 merged by jenkins-bot:
[mediawiki/extensions/ORES@wmf/1.29.0-wmf.20] Disable ORES in Recentchangeslinked
Mentioned in SAL (#wikimedia-operations) [2017-04-20T19:24:43Z] <Amir1> start of ladsgroup@naos:/srv/mediawiki-staging/php-1.29.0-wmf.20$ scap sync-file php-1.29.0-wmf.20/extensions/ORES/includes/Hooks.php '[[gerrit:349271|Disable ORES in Recentchangeslinked]] (T163063)'
Just revisiting this issue: Do people think that the New Filters ORES implementation would still cause problems on Related Changes? Or is there any reason to believe that this issue has been cleared up by the many changes we've made since April?