Page MenuHomePhabricator

ORES on en.wiki chokes on "V. likely good faith" + "Likely have problems"
Closed, ResolvedPublic

Description

With the New Filters beta enabled on en.wiki.

  • Go to the RC Page and delete all the filters.
  • set your results target to (only) 50
  • Select "Very likely good faith" and "Likely have problems"
  • Expected results: find at least some edits
  • Actual results: the system searches and searches for a looooong time, then finally displays the "no results found"

Making this test on Polish, Portuguese and Russian, I did find results. So, there are a number of issues here:

  1. Why are there no results? Do we need to adjust the levels? As I say, I found results on other wikis.
  2. The very long search time is also an issue. Should this time out at some point (ideally after displaying any results it did find)
  3. What would the system have done if I'd picked 500 results?
  4. What would happen if I'd picked "Very likely have problems" instead?

As I say, these searches work fine (including for more results with narrower filters) on other wikis, so it seems to have something to do with the scale of en.wiki and, possibly, with its size.

Event Timeline

The query for this is:

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,wl_user,wl_notificationtimestamp,
    (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 `watchlist` ON (wl_user = '22559448' AND (wl_title=rc_title) AND (wl_namespace=rc_namespace))
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)) 
WHERE ((ores_damaging_cls.oresc_probability BETWEEN 0.879 AND 1))
AND ((ores_goodfaith_cls.oresc_probability BETWEEN 0.86 AND 1))
AND (rc_timestamp >= '20170418000000')
AND rc_new IN ('0','1')
ORDER BY rc_timestamp DESC
LIMIT 50

I tried it with &limit=3 too and still got a timeout, so I'm guessing this query returns no results at all. There probably are no edits with a damaging score above 0.879 and also a goodfaith score above 0.86.

I get an error message after 60 seconds (which according to logstash corresponds to Error: 2062 Read timeout is reached (10.64.16.76)), but according to tendril the query runs for 5 minutes or more. There are almost 2M rows matching (rc_timestamp >= '20170418000000'), and it looks like going through them all takes a while. A simple "examine all rows looking for an impossible condition" query (select count(*) from recentchanges where (rc_timestamp >= '20170418000000') and rc_params='xyz';) took "only" 1m34s on the same DB server, whereas the query above took 4m24s.

Anecdotally this only appears to be a problem on enwiki, it seems that on other wikis this type of query does return results. We may need to reevaluate the schema to facilitate these types of queries though. We could also add an index on oresc_probability, but I'm not sure how much that would help, since we still have (and need) ORDER BY rc_timestamp in the query. Then again, maybe MySQL/MariaDB's index merge magic would make it work, or the resulting filesort might be fast (although, would we trust the query planner to make that determination?).

Now that T164621 has taken effect, I performed the following test:

  • On en.wiki, nusing a 7-day search, add the following to the default filters: "Very likely good faith" + "Likely have problems."
  • Expected result: find some intersection
  • Actual result: after about 2.5 minutes, found some two-dozen results!

So, the good news is we now have overlap. The bad news is the search takes a loooong time.

Incidentally, I checked the first 16 results. Stipulating that good-faith problems tend to be subtle, I graded them as follows:

  • good faith problems: 7
  • Straight up vandalism: 6
  • No problem: 3

Moving this to QA and starting a new ticket for speeding up this type of search.

The filter combination "V. likely good faith" + "Likely have problems" seems to be specifically problematic - other combination damaging and good faith filters return results in sensible time. The issue is present not on all wiki - the following wikis will load results for the filter combinationfor a very long time: enwiki, ruwiki, ptwiki. Other wiki do not have that issue , e.g. plwiki, hewiki.
Just to document the present query plan:

mysql:research@s3-analytics-slave [enwiki]> EXPLAIN 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,wl_user,wl_notificationtimestamp,     (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 `watchlist` ON (wl_user = '22559448' AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) 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))  WHERE ((ores_damaging_cls.oresc_probability BETWEEN 0.879 AND 1)) AND ((ores_goodfaith_cls.oresc_probability BETWEEN 0.86 AND 1)) AND (rc_timestamp >= '20170418000000') AND rc_new IN ('0','1') ORDER BY rc_timestamp DESC LIMIT 50\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: recentchanges
         type: range
possible_keys: rc_timestamp,new_name_timestamp,tmp_1
          key: rc_timestamp
      key_len: 16
          ref: NULL
         rows: 4504510
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: watchlist
         type: eq_ref
possible_keys: wl_user,namespace_title
          key: wl_user
      key_len: 265
          ref: const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_damaging_mdl
         type: ref
possible_keys: PRIMARY,oresm_version,ores_model_status
          key: ores_model_status
      key_len: 35
          ref: const,const
         rows: 1
        Extra: Using index condition
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_damaging_cls
         type: ref
possible_keys: oresc_winner
          key: oresc_winner
      key_len: 4
          ref: enwiki.recentchanges.rc_this_oldid
         rows: 8
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_goodfaith_mdl
         type: ref
possible_keys: PRIMARY,oresm_version,ores_model_status
          key: ores_model_status
      key_len: 35
          ref: const,const
         rows: 1
        Extra: Using index condition
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: ores_goodfaith_cls
         type: ref
possible_keys: oresc_winner
          key: oresc_winner
      key_len: 4
          ref: enwiki.recentchanges.rc_this_oldid
         rows: 8
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: PRIMARY
        table: flaggedpages
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: enwiki.recentchanges.rc_cur_id
         rows: 1
        Extra: 
*************************** 8. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: change_tag
         type: ref
possible_keys: ct_rc_id,change_tag_rc_tag
          key: ct_rc_id
      key_len: 5
          ref: enwiki.recentchanges.rc_id
         rows: 301282
        Extra: Using index
8 rows in set (0.24 sec)

Since the issue will be addressed in other tickets QA Recommendation: Resolve

jmatazzoni closed this task as Resolved.May 12 2017, 8:24 PM
jmatazzoni claimed this task.
Restricted Application added a project: Growth-Team. · View Herald TranscriptJul 10 2018, 5:52 PM