[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn closed this task as "Resolved". ArielGlenn claimed this task. ArielGlenn added a comment. Re-enabled, checked daily runs, they look good, so I'm resolving this. Thanks, everybody! TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikim

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. In T260232#6448382 , @gerritbot wrote: > Change 625642 **merged** by jenkins-bot: > [mediawiki/core@master] don't pass null page id to page related queries for category change rdf dumps > > https://gerri

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ReleaseTaggerBot added projects: MW-1.36-notes (1.36.0-wmf.9; 2020-09-15), MW-1.35-notes, MW-1.31-release-notes, MW-1.34-notes. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: ReleaseTaggerBot Cc:

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626043 **merged** by jenkins-bot: [mediawiki/core@REL1_31] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626043 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricat

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626042 **merged** by jenkins-bot: [mediawiki/core@REL1_34] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626042 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricat

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626041 **merged** by jenkins-bot: [mediawiki/core@REL1_35] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626041 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricat

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626043 had a related patch set uploaded (by Reedy; owner: ArielGlenn): [mediawiki/core@REL1_31] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626043 TASK DETAIL https://phabricator.wikimedia.org/T260232

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626042 had a related patch set uploaded (by Reedy; owner: ArielGlenn): [mediawiki/core@REL1_34] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626042 TASK DETAIL https://phabricator.wikimedia.org/T260232

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 626041 had a related patch set uploaded (by Reedy; owner: ArielGlenn): [mediawiki/core@REL1_35] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/626041 TASK DETAIL https://phabricator.wikimedia.org/T260232

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 625642 **merged** by jenkins-bot: [mediawiki/core@master] don't pass null page id to page related queries for category change rdf dumps https://gerrit.wikimedia.org/r/625642 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES h

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 620775 **merged** by jenkins-bot: [mediawiki/core@master] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/620775 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricato

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

daniel edited projects, added Platform Team Workboards (Clinic Duty Team); removed Platform Team Workboards (External Code Reviews). TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: daniel Cc: Arie

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

eprodromou triaged this task as "High" priority. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: eprodromou Cc: ArielGlenn, CBogen, Cparle, Umherirrender, DannyS712, Naike, WDoranWMF, Krinkle, aar

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 625642 had a related patch set uploaded (by ArielGlenn; owner: ArielGlenn): [mediawiki/core@master] don't pass page id 0 to page related queries for category change rdf dumps https://gerrit.wikimedia.org/r/625642 TASK DETAIL https://phabricator.wiki

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. In T260232#6390706 , @gerritbot wrote: > Change 620775 had a related patch set uploaded (by ArielGlenn; owner: ArielGlenn): > [mediawiki/core@master] don't include null page ids in query list for category d

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 623692 **merged** by ArielGlenn: [operations/puppet@production] disable category rdf dumps for now https://gerrit.wikimedia.org/r/623692 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/setti

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 623692 had a related patch set uploaded (by ArielGlenn; owner: ArielGlenn): [operations/puppet@production] disable category rdf dumps for now https://gerrit.wikimedia.org/r/623692 TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENC

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

CBogen added a project: Structured-Data-Backlog. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: CBogen Cc: ArielGlenn, CBogen, Cparle, Umherirrender, DannyS712, Naike, WDoranWMF, Krinkle, aaron,

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

eprodromou edited projects, added Platform Team Workboards (External Code Reviews); removed Platform Engineering. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: eprodromou Cc: ArielGlenn, CBogen,

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 620900 had a related patch set uploaded (by Lucas Werkmeister (WMDE); owner: Lucas Werkmeister (WMDE)): [mediawiki/core@master] Add test for dumping category without page https://gerrit.wikimedia.org/r/620900 TASK DETAIL https://phabricator.wikimedia

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a project: Patch-For-Review. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: gerritbot Cc: ArielGlenn, CBogen, Cparle, Umherirrender, DannyS712, Naike, WDoranWMF, Krinkle, aaron, R

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

gerritbot added a comment. Change 620775 had a related patch set uploaded (by ArielGlenn; owner: ArielGlenn): [mediawiki/core@master] don't include null page ids in query list for category dumps https://gerrit.wikimedia.org/r/620775 TASK DETAIL https://phabricator.wikimedia.org/T260

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. I took to brute force approach of writing all queries to a log file by adding the appropriate fopen/fputs/fclose in Database::select (live on snapshot1010, testbed host). I then ran: dumpsgen@snapshot1010:/srv/mediawiki$ /usr/bin/php7.2 /srv/mediawiki/mult

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Umherirrender added a comment. In T260232#6386684 , @Reedy wrote: > Obviously it'd be very helpful if `BatchRowIterator::next` wasn't coming up as the calling function... but I guess that's harder with the iterator function calls... I w

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Reedy added a comment. Hacking in searching for `''` in the SQL... The only results I get are like this the below, where the `''` is involving escaped `'` as part of a page name string(90) "( cl_from = '60585988' AND cl_to > 'Ulmus_\'Concavaefolia\'' ) OR ( cl_from > '60585988' )"

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Cparle added a comment. Something else I don't understand about this - if I take the ids from that query that fails and run this: select page_title,page_id from page where page_id in (92967652,234494,24559020,960551,3007520,76398273,6972234,363488,2257260,4157420,89319925,84920900,4179

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Reedy added a comment. Thanks Cormac! I'd basically done the same thing in the background too. That query on commons, without a limit gives 9197995 rows TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpref

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Cparle added a comment. > I guess it'd be useful to get the actual SQL query being generated by this (ie by the code, not by what it should be if a human built it from that): SELECT page_title,page_id,pp_propname,cat_pages,cat_subcats,cat_files FROM `page` LEFT JOIN `page_props

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. Just for completeness, on db2073 I also I ran the original query with the crap entry, the show explain showed use of a filesort as above, and the execution time was... well it's still going, 330 seconds in. I killed it. TASK DETAIL https://phabricator.wikimedia.

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. I saw multiple queries with this string in them while camping on the production vslow and looking at the processlist. I don't know how many of the queries have this issue. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabri

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Reedy added a comment. Looking at the code... foreach ( $this->getCategoryIterator( $dbr ) as $batch ) { $pages = []; foreach ( $batch as $row ) { $this->categoriesRdf->writeCategoryData(

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. When I ran the above query on db2073 (codfw dups and vslow host) without the crap ' ' field in there, it returned in 0.00 seconds. Maybe the bad entries are a new development? TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://pha

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Reedy added a comment. In T260232#6385464 , @ArielGlenn wrote: > SELECT /* BatchRowIterator::next */ cl_from,cl_to FROM `categorylinks` WHERE cl_type = 'subcat' AND cl_from IN (92967652,234494,24559020,960551,3007520,76398273,69

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. SELECT /* BatchRowIterator::next */ cl_from,cl_to FROM `categorylinks` WHERE cl_type = 'subcat' AND cl_from IN (92967652,234494,24559020,960551,3007520,76398273,6972234,363488,2257260,4157420,89319925,84920900,41797907,61421859,92055128,9221880,14562,267627

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

ArielGlenn added a comment. Daily rdf dumps are probably broken until this is resolved, just a fyi for folks importing these for search purposes. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

CBogen added subscribers: Cparle, CBogen. CBogen added a comment. @Cparle is this something the SD team should look at? TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: CBogen Cc: CBogen, Cparle

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Umherirrender added a comment. The table has `PRIMARY KEY (cl_from,cl_to)` So it could use a range index on the primary? Order is on primary `ORDER BY cl_from ASC,cl_to ASC` to make the query ready for unique paginate. But for the long list of cl_from it needs a filesort to find the

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Ladsgroup edited projects, added Discovery-Search; removed Wikidata-Campsite. Ladsgroup added a comment. Wikidata team doesn't maintain WDQS, it's search platform. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emai

[Wikidata-bugs] [Maniphest] T260232: BatchRowIterator slow query on commonswiki

Reedy added projects: Wikidata-Query-Service, Wikidata, Wikidata-Campsite. TASK DETAIL https://phabricator.wikimedia.org/T260232 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Reedy Cc: Umherirrender, DannyS712, Naike, WDoranWMF, Krinkle, aaron, Reed