[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 db duplicate...@googlemail.com changed: What|Removed |Added Status|PATCH_TO_REVIEW |RESOLVED CC||duplicate...@googlemail.com Resolution|--- |FIXED --- Comment #14 from db duplicate...@googlemail.com --- Status: Merged -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #13 from Gerrit Notification Bot gerritad...@wikimedia.org --- Change 117373 merged by jenkins-bot: Redo WhatLinksHere query and add a *_from_namespace field to link tables https://gerrit.wikimedia.org/r/117373 -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #12 from Sean Pringle sprin...@wikimedia.org --- I am testing MariaDB 5.5.36 on db1034 with a patch to add a innodb_min_scan_time variable which allows ha_innodb::scan_time to be controlled, and by extension the apparent cost of an index scan (InnoDB tables scans are index scans on the clustered primary key). Historically this sort of thing could be done with MyISAM and max_seeks_for_key, but that isn't so effective for InnoDB. SpecialWhatLinksHere::showIndirectLinks queries on skewed data is one group of the queries this setting helps. Also likely a number of other queries using FORCE INDEX could benefit too. So far no adverse impact on other traffic or disk IO patterns. Not necessarily a reason to abandon Aaron's patch; just an update because the patch is blocked on a schema change that won't be scheduled until after the TechOps meet. -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #11 from Gerrit Notification Bot gerritad...@wikimedia.org --- Change 117373 had a related patch set uploaded by Krinkle: Redo WhatLinksHere query and add a *_from_namespace field to link tables https://gerrit.wikimedia.org/r/117373 -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #8 from Aaron Schulz aschulz4...@gmail.com --- This works now as long as the NS selector is not used. If it is, then we'd need an rd_from_namespace field to make this work. -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 Aaron Schulz aschulz4...@gmail.com changed: What|Removed |Added Assignee|wikibugs-l@lists.wikimedia. |aschulz4...@gmail.com |org | -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #9 from Aaron Schulz aschulz4...@gmail.com --- (In reply to Aaron Schulz from comment #8) This works now as long as the NS selector is not used. If it is, then we'd need an rd_from_namespace field to make this work. Sorry not rd_from_namespace but rather *_from_namespace for templatelinks, imagelinks, and pagelinks. -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #10 from Gerrit Notification Bot gerritad...@wikimedia.org --- Change 117373 had a related patch set uploaded by Aaron Schulz: Redid WhatLinksHere query and added a _from_namespace field https://gerrit.wikimedia.org/r/117373 -- You are receiving this mail because: You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #7 from Sean Pringle sprin...@wikimedia.org --- Aaron pinged me on IRC. Some observations made during that discussion follow. The pain points are now mostly mid-sized wikis (eg, *wiktionary, metawiki) that have: * large numbers of page or template links (hundreds of millions) * comparatively few pages (less than 10 million) * data skewed toward one or two namespaces * data skewed toward a small set of titles enwiktionary select tl_namespace, count(*) as links from templatelinks group by tl_namespace; +--+---+ | tl_namespace | links | +--+---+ |0 | 1696 | |1 |56 | |2 | 14901 | |3 | 129 | |4 | 1462 | |5 |12 | |8 | 103 | | 10 | 23967908 | | 11 | 1 | | 14 | 5 | | 15 | 4 | | 90 | 3 | | 100 |60 | | 101 | 2 | | 104 | 1546 | | 106 | 6 | | 110 | 1 | | 828 | 138128211 | +--+---+ When hitting 10 or 828 and a title with millions of links MariaDB may fall back on an index scan on `page`. These mid-sized wikis liked having STRAIGHT_JOIN even if others didn't :-) Could we tolerate the possibility of stale links and skip the JOIN on `page` altogther? Might need a denormalised tl_from_namespace field. Then pull out the page fields in a second batch WHERE page_id IN (...), or use a sub-query to do the same thing: SELECT page_id, page_namespace, page_title, rd_from FROM ( SELECT tl_from, rd_from FROM `templatelinks` LEFT JOIN `redirect` ON rd_from = tl_from AND rd_namespace = tl_namespace AND rd_title = tl_title AND (rd_interwiki = '' OR rd_interwiki IS NULL) WHERE tl_namespace = '828' AND tl_title = 'languages/data3/i' ORDER BY tl_from LIMIT 100 ) tmp JOIN page ON tl_from = page_id ORDER BY page_id LIMIT 51; Increase the inner LIMIT if stale links are a problem; 500 or 1000 would be fine. -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 Aaron Schulz aschulz4...@gmail.com changed: What|Removed |Added CC||aschulz4...@gmail.com --- Comment #6 from Aaron Schulz aschulz4...@gmail.com --- (In reply to Gerrit Notification Bot from comment #5) Change 114070 merged by jenkins-bot: Removed STRAIGHT_JOIN; the is slower when a namespace has a few pages https://gerrit.wikimedia.org/r/114070 This both helped and harmed some cases. More work is needed here. -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 Gerrit Notification Bot gerritad...@wikimedia.org changed: What|Removed |Added Status|NEW |PATCH_TO_REVIEW -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #4 from Gerrit Notification Bot gerritad...@wikimedia.org --- Change 114070 had a related patch set uploaded by Aaron Schulz: Removed STRAIGHT_JOIN; the is slower when a namespace has a few pages https://gerrit.wikimedia.org/r/114070 -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 --- Comment #5 from Gerrit Notification Bot gerritad...@wikimedia.org --- Change 114070 merged by jenkins-bot: Removed STRAIGHT_JOIN; the is slower when a namespace has a few pages https://gerrit.wikimedia.org/r/114070 -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 Bartosz Dziewoński matma@gmail.com changed: What|Removed |Added CC||mybugs.m...@gmail.com --- Comment #3 from Bartosz Dziewoński matma@gmail.com --- *** Bug 60838 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 60618] Very slow query for Special:WhatLinksHere limitted to a namespace when page has large number of backlinks
https://bugzilla.wikimedia.org/show_bug.cgi?id=60618 Bawolff (Brian Wolff) bawolff...@gmail.com changed: What|Removed |Added Summary|Database error when using |Very slow query for |Special:WhatLinksHere on|Special:WhatLinksHere |Commons |limitted to a namespace ||when page has large number ||of backlinks -- You are receiving this mail because: You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
