[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2016-11-28T19:50:12Z] Synchronized php-1.29.0-wmf.3/extensions/Wikidata: SWAT: [[gerrit:323880|Update Wikibase: Use the "redirect" table in SqlEntityIdPager]] T151356 (duration: 02m 11s)TASK

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

gerritbot added a comment. Change 323871 merged by jenkins-bot: Use the "redirect" table in SqlEntityIdPager https://gerrit.wikimedia.org/r/323871TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: hoo,

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

gerritbot added a comment. Change 323817 merged by jenkins-bot: Use the "redirect" table in SqlEntityIdPager https://gerrit.wikimedia.org/r/323817TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: hoo,

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

gerritbot added a comment. Change 323871 had a related patch set uploaded (by Hoo man): Use the "redirect" table in SqlEntityIdPager https://gerrit.wikimedia.org/r/323871TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2016-11-28T12:17:19Z] Killed the Wikidata json dumpers on snapshot1007 due to T151356. Will be restarted once a fix has been deployed.TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

gerritbot added a comment. Change 323817 had a related patch set uploaded (by Hoo man): Use the "redirect" table in SqlEntityIdPager https://gerrit.wikimedia.org/r/323817TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2016-11-24T11:41:26Z] Killed the Wikidata JSON dump creation on snapshot1007: Wont succeed before Monday, due to T151356TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. I am ok with that, but on my version, it doesn't create a temporary table (maybe you were mixing 2 different executions?): MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title FROM `page` LEFT JOIN redirect ON rd_from = page_id WHERE

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

hoo added a comment. There already is a separate table with redirect information (redirect), the page_is_redirect field is mostly for convenience these days, I guess. We could rewrite the query as follows: SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. The histograms are still not enough to convince T151356#2817971 to not optimize the page_is_redirect condition. My advice is to rewrite the query into: MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title,page_is_redirect FROM `page`

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. If we have to fail back to index hinting, this should be preferred -ignore rather than force: MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title FROM `page` IGNORE INDEX(page_redirect_namespace_len) WHERE (page_id > 3128073) AND

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. So we have the choice of forcing a bad query plan ourselves, or leaving it to Maria to pick a bad query plan... No, why is this a binary solution? we maybe can rewrite the query to do what we want and it changes if the environement changes. What we do not want to do at

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

daniel added a comment. So we have the choice of forcing a bad query plan ourselves, or leaving it to Maria to pick a bad query plan...TASK DETAILhttps://phabricator.wikimedia.org/T151356EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: hoo, danielCc: Aklapper,

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. The problem is not how to do the force, that can be added directly, the problem is that force index is a poor workaround, if the index changes in the future or the row distribution, we may be forcing a bad query plan.TASK

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

hoo added a comment. In T151356#2815197, @jcrespo wrote: This is the goal, but we will try to achieve this without the force index, depending on how much I can change the original query: MediaWiki's database abstraction is quite flexible, so most things should just work.TASK

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

jcrespo added a comment. This is the goal, but we will try to achieve this without the force index, depending on how much I can change the original query: MariaDB MARIADB db1082 wikidatawiki > EXPLAIN SELECT page_id, page_title FROM `page` FORCE INDEX(PRIMARY) WHERE (page_id > 3128073) AND

[Wikidata-bugs] [Maniphest] [Commented On] T151356: Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

hoo added a comment. EXPLAINs: mysql:wikiadmin@db1082 [wikidatawiki]> EXPLAIN SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */ page_id,page_title FROM `page`WHERE (page_id > 3128073) AND page_namespace IN ('0','120') AND page_is_redirect = '0' ORDER BY