Page MenuHomePhabricator

SpecialFewestRevisions::reallyDoQuery takes more than 9h to run
Open, HighPublic

Description

I just realised that we had a wikidatawiki vslow host with the following query:

1SELECT /* SpecialFewestRevisions::reallyDoQuery www-data@mwmain... */ page_namespace AS `namespace`,page_title AS `title`,COUNT(*) AS `value` FROM `revision`,`page` WHERE page_namespace IN ('640','146','0') AND (page_id = rev_page) AND (page_is_redirect = 0) GROUP BY page_namespace,page_title ORDER BY value LIMIT 5000

It was running for more than 9h, it was creating some lag on that host.

+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
| id   | select_type | table    | type | possible_keys                                  | key                         | key_len | ref                       | rows     | Extra                                                               |
+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
|    1 | SIMPLE      | page     | ref  | PRIMARY,name_title,page_redirect_namespace_len | page_redirect_namespace_len | 1       | const                     | 35724007 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | revision | ref  | page_timestamp,page_user_timestamp,rev_page_id | rev_page_id                 | 4       | wikidatawiki.page.page_id |        8 | Using index                                                         |
+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

It looks like it was coming from a cronjob, is this really needed or is there a way to optimize this into multiple chunks/processes?

Please feel free to change/remove/add tags as needed.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 13 2019, 11:07 AM

I think we should kill this thing for wikidata, it doesn't bring much value and it's really really resource intensive. cc @Lydia_Pintscher

@Marostegui Did you mean to CC someone else? I know nothing about this :)

I killed SpecialFewestRevisions::reallyDoQuery because it was causing the host (s8, db1101) where it was running to lag for 1-2 seconds, enough for queries waiting for replication to take a dive in performance, and logging hundreds of thousands of db errors. Long running queries are ok on a vslow server, but I wonder if we could make them in smaller batches (slow overal) to not tank the writes.

Some of these special pages are pretty useless for wikidata. I think a community consultation should be made to see which one of them are unused and then we can just disable them forever for Wikidata. cc. @Lydia_Pintscher @Lea_Lacroix_WMDE

matmarex removed a subscriber: matmarex.Mar 2 2020, 1:18 PM

Mentioned in SAL (#wikimedia-operations) [2020-04-27T07:15:39Z] <marostegui> Kill updateSpecialPages.php wikidatawiki --override --only=Fewestrevisions as it is causing lag - T238199

This hit us hard again:

| 1593977753 | wikiadmin       | 10.64.16.77:59698  | wikidatawiki       | Query       |   22436 | Copying to tmp table on disk                                          | SELECT /* SpecialFewestRevisions::reallyDoQuery  */  page_namespace AS `namespace`,page_title AS `ti |    0.000 |

I have killed it.

Removing this from the campsite, but adding T245818

Marostegui triaged this task as High priority.May 13 2020, 8:09 AM

This has happened again and caused errors again.
Can we please disable it?

Can we just skip the updateSpecialPages.php wikidatawiki --override --only=Fewestrevisions script altogether, instead of shooting it every month?

Change 596172 had a related patch set uploaded (by ArielGlenn; owner: ArielGlenn):
[operations/puppet@production] mediawiki: Disable fewestrevisions for wikidata

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

Can we just skip the updateSpecialPages.php wikidatawiki --override --only=Fewestrevisions script altogether, instead of shooting it every month?

That's a product manger decision T245818: Audit query pages on Wikidata and I wouldn't do it unless @Lydia_Pintscher is okay with it (otherwise I need to find a way for it to work properly).

Can we do this temporarily while the query is being fixed up? It looks like it had to be killed in Nov, Feb, Apr, May, so I'd rather temp disable than require folks to shoot it (and anything else hung as a side effect).

Can we do this temporarily while the query is being fixed up? It looks like it had to be killed in Nov, Feb, Apr, May, so I'd rather temp disable than require folks to shoot it (and anything else hung as a side effect).

I understand but the point is that for example, there might be an announcement needed or this might be needed in a vital system (like reporting for Q reviews and such) that we don't know. This kind of changes should be communicated in order to avoid surprises and suboptimal incidents.

Anyway, Lydia said it's fine to do it tomorrow when it gets announced by our communication manager. Does that work for you?

...

Anyway, Lydia said it's fine to do it tomorrow when it gets announced by our communication manager. Does that work for you?

Anything's fine as long as there's a plan of some sort before next month, so, sure!

With the announcement done, this can move forward on our side

Mentioned in SAL (#wikimedia-operations) [2020-05-27T13:18:59Z] <marostegui> Kill /usr/local/bin/mwscriptwikiset updateSpecialPages.php s8.dblist --override --only=Fewestrevisions T238199

And this just happened again:

| 1953784190 | wikiadmin            | 10.64.16.77:53846  | wikidatawiki       | Query       |   44238 | Copying to tmp table on disk                                          | SELECT /* SpecialFewestRevisions::reallyDoQuery  */  page_namespace AS `namespace`,page_title AS `ti |    0.000 |

I killed this on mwmain1002:

www-data 221626  0.0  0.0   4276   740 ?        Ss   01:00   0:00 /bin/sh -c /usr/local/bin/mwscriptwikiset updateSpecialPages.php s8.dblist --override --only=Fewestrevisions > /var/log/mediawiki/updateSpecialPages/s8@18-FewestRevisions.log 2>&1
www-data 221646  0.0  0.0  11180  2832 ?        S    01:00   0:00 /bin/bash /usr/local/bin/mwscriptwikiset updateSpecialPages.php s8.dblist --override --only=Fewestrevisions
``

Change 596172 merged by ArielGlenn:
[operations/puppet@production] mediawiki: Disable fewestrevisions for wikidata

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

The above patch to remove the cronjob has been merged, and the cronjob manually removed from mwmaint1002.
I guess this can be resolved @Ladsgroup?

Unless folks want to keep it open to work on speeding it up in the future?

Can we keep the task open a few more days? I just sent the announcement to the community https://www.wikidata.org/wiki/Wikidata:Project_chat#Special:FewestRevisions_to_be_disabled and I just want to make sure that there's no strong veto. But everything should be fine.

One thing that may be relevant here is that the query may have worked once or twice in the last 6 months due to this underlying issue. Last update that completed successfully was 27 March. In effect, because this bug, this wasn't disabled officially but it was already (for the most part) not-working (but still causing issues before the merge).