[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

daniel added a comment.@jcrespo so we should do one query per ID, with limit 1? ok!TASK DETAILhttps://phabricator.wikimedia.org/T116404EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: danielCc: aaron, aude, daniel, hoo, Aklapper, jcrespo, Vali.matei, Minhnv-280

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment.@daniel I think this is a case of prematurely optimizing. It is true that things like: foreach ... { 'SELECT' } Are usually considered bad practices, but for trying to minimize "round-trip time", we are actually doing a way worse query. Your query cannot know that with onl

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

daniel added a comment.@jcrespo @hoo: ick, 14 million rows? And this isn't optimized away because of the DISTINCT? The intent of the query is: "for the given set of entity IDs, tell me which ones are in the table (at least once)". So the total result will never be larger than the number of IDs in

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment.This is not a problem with the servers, the query planner, or the indexing: MariaDB db1068 commonswiki > EXPLAIN SELECT DISTINCT eu_entity_id FROM `wbc_entity_usage` WHERE eu_entity_id IN ('Q148475','Q54919','Q423048','Q2494649','Q13219454','Q131454','Q36578','Q1798125

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

hoo added a comment.db1070 vs. db1068 (different database, cold queries, the fact that the result rows match is just a coincidence): mysql:wikiadmin@db1068 [commonswiki]> SELECT /* Wikibase\Client\Usage\Sql\EntityUsageTable::getUsedEntityIdStrings */ DISTINCT eu_entity_id FROM `wbc_entity_usage`

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

hoo added a comment. I haven't seen it in the error logs recently at least, but I'm not looking at them every day. TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: jcrespo, hoo Cc: aude, daniel

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment. Marius, or someone else, do you know if this is still ongoing after latest deployments + hardware upgrades? TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: jcrespo Cc:

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment. Can confirm last seen on db1018: SELECT DISTINCT eu_entity_id FROM `wbc_entity_usage` WHERE eu_entity_id IN ('Q10864210','Q10866766','Q10874855','Q10877844','Q10877846','Q10878314','Q10879635', 'Q10880445','Q10882043','Q10887655','Q10890010','Q10890075','Q1089605

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

hoo added a comment. I can no longer see this issue on either db1060 nor db1054, but it's still reproducible on eg. db1018. TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: jcrespo, hoo Cc: aude,

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment. The initial issue still happens, although now the query is consistently slow every time on both servers. TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: jcrespo Cc: aude

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment. A similar thing is happening on zhwiki for a different query- the optimizer seems to have some bug for that wiki in particular? TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences

[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

jcrespo added a comment. I do not see this happening on enwiki. Checking on other wikis/hosts. TASK DETAIL https://phabricator.wikimedia.org/T116404 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: jcrespo Cc: aude, daniel, hoo, Aklapper, jcrespo, Wi