Page MenuHomePhabricator

Wikibase\Repo\Store\Sql\SqlEntitiesWithoutTermFinder::getEntitiesWithoutTerm can take 19 hours to execute and it is run by the web requests user
Closed, ResolvedPublic

Description

For example, I found on db1070 2 long running queries:

Server 	Connection 	User 	Client 	Database 	Time
db1070	51133099	wikiuser	mw1256	wikidatawiki	19h
SELECT /* Wikibase\Repo\Store\Sql\SqlEntitiesWithoutTermFinder::getEntitiesWithoutTerm */ page_title AS `entity_id_serialization` FROM `page` LEFT JOIN `wb_terms` ON ((((term_entity_id = REPLACE(page_title, 'Q', '')) AND term_entity_type = 'item' AND page_namespace = '0') OR ((term_entity_id = REPLACE(page_title, 'P', '')) AND term_entity_type = 'property' AND page_namespace = '120')) AND term_type = 'description' AND term_language = 'fr') WHERE (term_entity_type IS NULL) AND page_is_redirect = '0' AND page_namespace IN ('0','120') ORDER BY page_id DESC LIMIT 51
db1070	51146589	wikiuser	mw1256	wikidatawiki	19h
SELECT /* Wikibase\Repo\Store\Sql\SqlEntitiesWithoutTermFinder::getEntitiesWithoutTerm */ page_title AS `entity_id_serialization` FROM `page` LEFT JOIN `wb_terms` ON ((((term_entity_id = REPLACE(page_title, 'Q', '')) AND term_entity_type = 'item' AND page_namespace = '0') OR ((term_entity_id = REPLACE(page_title, 'P', '')) AND term_entity_type = 'property' AND page_namespace = '120')) AND term_type = 'description' AND term_language = 'fr') WHERE (term_entity_type IS NULL) AND page_is_redirect = '0' AND page_namespace IN ('0','120') ORDER BY page_id DESC LIMIT 51

They where running on db1070 (nice because it is the vslow slave), but they where running with the web request user, not with the dumps or the admin user, which means they will be killed in 300 seconds (not being killed is a bug, and that will be corrected immediately).

The request is to identify what those queries are- if they should be interactive responses, check its execution, as it should be under 300 seconds; if it is non-interactive, they should be probably run from terbium and with a different user so they are not killed. There is anyway a maximum execution time of 24 hours, and only on special cases, for queries that are done once a month or so on enwiki.

Event Timeline

jcrespo created this task.Mar 20 2017, 8:49 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 20 2017, 8:49 AM
aude added a subscriber: aude.Mar 20 2017, 1:55 PM

I am afraid this not only runs on vslow slaves. db1070 is not receiving vslow traffic just yet (it was depooled for reimage and only pooled with just normal traffic to warm it up by the end of the week) see: https://noc.wikimedia.org/conf/highlight.php?file=db-eqiad.php

thiemowmde triaged this task as High priority.Mar 25 2017, 2:46 PM

We wanted to disable this special page for the Item namespace for a while. Did we forgot about this? T147638 is resolved. It appears the only thing that's missing now is the setting that actually disables it.

We should also make sure the "all" option does not include the Item namespace when it is disabled.

aude added a comment.Mar 26 2017, 6:39 PM

I think the page and query is okay (probably) when it is selecting pages/enttieis in *one* namespace. (including item namespace, i think is ok)

The problem is having the query work with the 'all' option, which I suggest we remove.

(PS. When we move to have serialized entity ids (e.g. 'Q64') in the wb_terms table, then think we might be able to make the queries more performant, including having the 'all' option again, if we want)

I can not think of a use case that requires this "all" option and totally agree we should simply remove it.

This is ongoing right now, for example:

db1082	307726822	wikiuser	mw1248	wikidatawiki	4m
SELECT /* Wikibase\Repo\Store\Sql\SqlEntitiesWithoutTermFinder::getEntitiesWithoutTerm */ page_title AS `entity_id_serialization` FROM `page` LEFT JOIN `wb_terms` ON ((((term_entity_id = REPLACE(page_title, 'Q', '')) AND term_entity_type = 'item' AND page_namespace = '0') OR ((term_entity_id = REPLACE(page_title, 'P', '')) AND term_entity_type = 'property' AND page_namespace = '120')) AND term_type = 'label' AND term_language = 'en') WHERE (term_entity_type IS NULL) AND page_is_redirect = '0' AND page_namespace IN ('0','120') ORDER BY page_id DESC LIMIT 51

Looks like we wrote the code, but failed to deploy the necessary config change.

We'll need this:

$wgWBRepoSettings['supportedEntityTypesForEntitiesWithoutTermListings'] = [ 'property' ];

Change 345179 had a related patch set uploaded (by Daniel Kinzler):
[operations/mediawiki-config@master] Allow only properties on Special:EntitiesWithoutLabel and Special:EntitiesWithoutDescription.

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

aude added a comment.EditedMar 29 2017, 4:37 AM

For just items:

queries for selecting items without descriptions on wikidata:

51 rows in set (0.01 sec)

[wikidatawiki]> SELECT  page_title AS `entity_id_serialization`  FROM `page` LEFT JOIN `wb_terms` ON ((((term_entity_id = REPLACE(page_title, 'Q', '')) AND term_entity_type = 'item' AND page_namespace = '0')) AND term_type = 'description' AND term_language = 'ar')   WHERE (term_entity_type IS NULL) AND page_is_redirect = '0' AND page_namespace = '0'  ORDER BY page_id DESC LIMIT 51

with a somewhat large limit and offset:

4000 rows in set (0.10 sec)

[wikidatawiki]> SELECT  page_title AS `entity_id_serialization`  FROM `page` LEFT JOIN `wb_terms` ON ((((term_entity_id = REPLACE(page_title, 'Q', '')) AND term_entity_type = 'item' AND page_namespace = '0')) AND term_type = 'description' AND term_language = 'ar')   WHERE (term_entity_type IS NULL) AND page_is_redirect = '0' AND page_namespace = '0'  ORDER BY page_id DESC LIMIT 4000,4000;

I think these queries are okay with one entity type, and not sure it's necessary to restrict the page to only work with properties. (although if we really want and think it helps, ok...)

The problem (the query posted by Jaime) is indeed the query with 'all' option, which we should remove, and only allow the query with one entity type at a time.

hoo added a comment.Apr 3 2017, 2:37 PM

So are we fine with removing the all option and adding a limitation for limit + offset?

aude added a comment.Apr 4 2017, 12:27 PM

think there already is a limit of 5000

aude added a comment.Apr 4 2017, 12:28 PM

think we can mark this as resolved? If we think necessary, can add a limit for offset

@hoo The all-option has been removed, as per T161631.
@aude Having a limit on the offset would be good, if we can't implement paging based on a unique key. But I suppose that is worth a separate ticket. This one can then be closed. The issues for the live site should be solved, now.

aude closed this task as Resolved.Apr 4 2017, 12:49 PM
aude removed a project: Patch-For-Review.

Thank you very much for working on this- do you have an estimation on when this will be fully deployed?

aude added a comment.Apr 4 2017, 1:02 PM

@jcrespo we backported/deployed this last Thursday

Thank you again!