Page MenuHomePhabricator

Investigate Extension:GoogleNewsSitemap query performance
Closed, ResolvedPublic

Description

Splitting from T287380: Decide on the future of DPL.

Note: extension:googlenewssitemap which powers the rss feeds on wikinews ( https://en.wikinews.org/w/index.php?title=Special:NewsFeed&format=atom ) does similar queries.

https://www.mediawiki.org/wiki/Extension:GoogleNewsSitemap is enabled on all Wikinewses (including ruwikinews) and testwiki.

We should analyze:

  • What queries this extension makes, and what their worst case performance is (tagging DBA for assistance with this)
  • Whether any other preventative measures like concurrency limiting (aka T263220: Limit concurrency of DPL queries) are needed

Event Timeline

Marostegui subscribed.

We'd need some hints in order to be able to search for specific queries.

Its similar to DPL. Main differences:

  • No alternative sort methods
  • Different caching model
  • Not tied to a page, so resource exhaustion from over-active jobs is less of a threat
  • some of the other more scary less used features from DPL are not included.

The main query should have a comment in it that looks like GoogleNewsSitemap::getCategories. It should also make some additional queries that have the comment GoogleNewsSitemap::getCacheInvalidationInfo, but those are expected to be cheap queries.

I suspect its much less used, because RSS is mostly dead these days.

Whether any other preventative measures like concurrency limiting (aka T263220: Limit concurrency of DPL queries) are needed

Probably a good idea

For reference, an example page triggering the query (assuming not cached): https://en.wikinews.org/w/index.php?title=Special:NewsFeed&feed=atom&categories=Published%7CAsia&notcategories=No%20publish%7Cdisputed&namespace=0&count=15&ordermethod=categoryadd&stablepages=only

I have not been able to find any slow query with GoogleNewsSitemap pattern in the last 120h, do you happen to know if they are usual or do they run occasionally?
It could also be that they are super fast and hence not caught.

Here's all the GoogleNewsSitemap::* queries generated by visiting that URL:

12021-08-05 09:45:32 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getCacheInvalidationInfo [0.001s] db2127: SELECT cat_pages FROM `category` WHERE cat_title IN ('Published','Asia','No_publish','Disputed') ORDER BY cat_title {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getCacheInvalidationInfo","sql":"SELECT cat_pages FROM `category` WHERE cat_title IN ('Published','Asia','No_publish','Disputed') ORDER BY cat_title ","domain":"enwikinews","runtime":0.001}
22021-08-05 09:45:32 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getCacheInvalidationInfo [0.001s] db2127: (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Published' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Asia' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'No_publish' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Disputed' ) {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getCacheInvalidationInfo","sql":"(SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Published' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Asia' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'No_publish' ) UNION ALL (SELECT MAX(cl_timestamp) AS ts FROM `categorylinks` WHERE cl_to = 'Disputed' )","domain":"enwikinews","runtime":0.001}
32021-08-05 09:45:32 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getCategories [0.065s] db2127: SELECT page_namespace,page_title,page_id,c1.cl_timestamp FROM `page` LEFT JOIN `flaggedpages` ON ((page_id = fp_page_id)) INNER JOIN `categorylinks` `c1` ON ((page_id = c1.cl_from) AND (c1.cl_to='Published')) INNER JOIN `categorylinks` `c2` ON ((page_id = c2.cl_from) AND (c2.cl_to='Asia')) LEFT OUTER JOIN `categorylinks` `c3` ON ((page_id = c3.cl_from) AND (c3.cl_to='No_publish')) LEFT OUTER JOIN `categorylinks` `c4` ON ((page_id = c4.cl_from) AND (c4.cl_to='Disputed')) WHERE page_namespace = 0 AND (fp_stable IS NOT NULL ) AND page_is_redirect = 0 AND (c3.cl_to IS NULL) AND (c4.cl_to IS NULL) ORDER BY c1.cl_timestamp DESC LIMIT 15 {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getCategories","sql":"SELECT page_namespace,page_title,page_id,c1.cl_timestamp FROM `page` LEFT JOIN `flaggedpages` ON ((page_id = fp_page_id)) INNER JOIN `categorylinks` `c1` ON ((page_id = c1.cl_from) AND (c1.cl_to='Published')) INNER JOIN `categorylinks` `c2` ON ((page_id = c2.cl_from) AND (c2.cl_to='Asia')) LEFT OUTER JOIN `categorylinks` `c3` ON ((page_id = c3.cl_from) AND (c3.cl_to='No_publish')) LEFT OUTER JOIN `categorylinks` `c4` ON ((page_id = c4.cl_from) AND (c4.cl_to='Disputed')) WHERE page_namespace = 0 AND (fp_stable IS NOT NULL ) AND page_is_redirect = 0 AND (c3.cl_to IS NULL) AND (c4.cl_to IS NULL) ORDER BY c1.cl_timestamp DESC LIMIT 15 ","domain":"enwikinews","runtime":0.065}
42021-08-05 09:45:32 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2927381 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2927381 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
52021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2927049 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2927049 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
62021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2926957 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2926957 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
72021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2925313 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2925313 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
82021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2924985 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2924985 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
92021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2924411 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2924411 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
102021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2923692 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2923692 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
112021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2921097 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2921097 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
122021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2921086 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2921086 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
132021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.002s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2920210 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2920210 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.002}
142021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2920166 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2920166 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
152021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2919535 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2919535 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
162021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2919508 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2919508 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
172021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2918646 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2918646 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}
182021-08-05 09:45:33 [46d12dfa-debb-4f24-8934-c276e2861bf2] mwdebug2001 enwikinews 1.37.0-wmf.17 DBQuery DEBUG: GoogleNewsSitemap::getVisibleCategories [0.001s] db2127: SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2918237 AND pp_propname IS NULL {"db_server":"db2127","db_name":"enwikinews","db_user":"wikiuser","method":"GoogleNewsSitemap::getVisibleCategories","sql":"SELECT cl_to FROM `categorylinks` LEFT OUTER JOIN `page` ON (page_namespace = 14 AND (page_title=cl_to)) LEFT OUTER JOIN `page_props` ON ((pp_page=page_id) AND pp_propname = 'hiddencat') WHERE cl_from = 2918237 AND pp_propname IS NULL ","domain":"enwikinews","runtime":0.001}

Thanks @Legoktm - by looking at those runtimes it makes sense they were not being captured, as they are pretty fast, they are almost all of them 0.01 and the slowest one seems to be 0.6 which is still pretty good.

This is that one

root@db1123.eqiad.wmnet[enwikinews]> explain SELECT  page_namespace,page_title,page_id,c1.cl_timestamp  FROM `page` LEFT JOIN `flaggedpages` ON ((page_id = fp_page_id)) INNER JOIN `categorylinks` `c1` ON ((page_id = c1.cl_from) AND (c1.cl_to='Published')) INNER JOIN `categorylinks` `c2` ON ((page_id = c2.cl_from) AND (c2.cl_to='Asia')) LEFT OUTER JOIN `categorylinks` `c3` ON ((page_id = c3.cl_from) AND (c3.cl_to='No_publish')) LEFT OUTER JOIN `categorylinks` `c4` ON ((page_id = c4.cl_from) AND (c4.cl_to='Disputed'))   WHERE page_namespace = 0 AND (fp_stable IS NOT NULL ) AND page_is_redirect = 0 AND (c3.cl_to IS NULL) AND (c4.cl_to IS NULL)  ORDER BY c1.cl_timestamp DESC LIMIT 15   ;
+------+-------------+--------------+--------+-----------------------------------------------------+--------------+---------+-----------------------------+------+-----------------------------------------------------------+
| id   | select_type | table        | type   | possible_keys                                       | key          | key_len | ref                         | rows | Extra                                                     |
+------+-------------+--------------+--------+-----------------------------------------------------+--------------+---------+-----------------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | c2           | ref    | PRIMARY,cl_timestamp,cl_sortkey                     | cl_timestamp | 257     | const                       | 3669 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | c1           | eq_ref | PRIMARY,cl_timestamp,cl_sortkey                     | PRIMARY      | 261     | enwikinews.c2.cl_from,const | 1    | Using where                                               |
|    1 | SIMPLE      | page         | eq_ref | PRIMARY,page_name_title,page_redirect_namespace_len | PRIMARY      | 4       | enwikinews.c2.cl_from       | 1    | Using where                                               |
|    1 | SIMPLE      | flaggedpages | eq_ref | PRIMARY                                             | PRIMARY      | 4       | enwikinews.c2.cl_from       | 1    | Using where                                               |
|    1 | SIMPLE      | c3           | eq_ref | PRIMARY,cl_timestamp,cl_sortkey                     | PRIMARY      | 261     | enwikinews.c2.cl_from,const | 1    | Using where; Using index; Not exists                      |
|    1 | SIMPLE      | c4           | eq_ref | PRIMARY,cl_timestamp,cl_sortkey                     | PRIMARY      | 261     | enwikinews.c2.cl_from,const | 1    | Using where; Using index; Not exists                      |
+------+-------------+--------------+--------+-----------------------------------------------------+--------------+---------+-----------------------------+------+-----------------------------------------------------------+
6 rows in set (0.004 sec)

I would assume that enwikinews and ruwikinews are the biggest wikinews wikis right?

I think despite of the size of the wiki and the query time/speed, having a MW limit on the number of connections that can run at the same time (apart from the server-side limit we have) is always something to have in place.

I would assume that enwikinews and ruwikinews are the biggest wikinews wikis right?

By pure size yes, but AIUI (per bawolff) we really care about largest category size here, in which case the order is ruwikinews, srwikinews (also bot imports), ptwikinews, then enwikinews - https://bawolff.toolforge.org/max-cat-size-dpl-wikis.txt.

I think despite of the size of the wiki and the query time/speed, having a MW limit on the number of connections that can run at the same time (apart from the server-side limit we have) is always something to have in place.

OK, will file a separate task for that. Should be easier than adding it to DPL since it doesn't get used inside a page parse. I'll leave this open until bawolff has a chance to comment, but otherwise it seems the investigation is over :)

My example was randomly chosen. I think its pretty representative of enwikinews, but its possible the worst case is worse. The worst case on ruwikinews is probably quite a bit worse due to the order of magnitude size difference (but still not anywhere near as bad as the problematic dpl query)

I am happy to double check queries for this extension on ruwikinews if we get them, @Legoktm any chance you can do the same capture for ruwikinews so we can double check their query plan and execution time in case it is worse (due to rows and/or the optimizer choosing a silly plan?)

Marostegui claimed this task.

I am going to close this as resolved - please feel free to re-open if there's still something left. If we can get some specific queries, I am happy to run and analyze then in whichever wiki you all think they could be or become problematic.