The following query runs on the vslow host of wikidatawiki (which is good):
root@db1087.eqiad.wmnet[wikidatawiki]> explain SELECT /* SpecialMostRevisions::reallyDoQuery www-data@mwmain... */ page_namespace AS `namespace`,page_title AS `title`,COUNT(*) AS `value` FROM `revision`,`page` WHERE page_namespace IN ('640','146','0') AND (page_id = rev_page) AND (page_is_redirect = 0) GROUP BY page_namespace,page_title ORDER BY value DESC LIMIT 5000;
+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | page | ref | PRIMARY,name_title,page_redirect_namespace_len | page_redirect_namespace_len | 1 | const | 35629757 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | revision | ref | page_timestamp,page_user_timestamp,rev_page_id | rev_page_id | 4 | wikidatawiki.page.page_id | 8 | Using index |
+------+-------------+----------+------+------------------------------------------------+-----------------------------+---------+---------------------------+----------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)It has been running for 8h. It causes some spikes of lag: https://grafana.wikimedia.org/render/d-solo/000000273/mysql?panelId=6&orgId=1&var-dc=eqiad%20prometheus%2Fops&var-server=db1087&var-port=9104&from=1574654386713&to=1574675986713&width=1000&height=500&tz=Europe%2FMadrid
Which causes MW to complain (https://logstash.wikimedia.org/goto/64894c5953d862563733a4708e8f3da1):
@timestamp 2019-11-25T10:00:36
t @version 1
t _id AW6iAX06KWrIH1QR588P
t _index logstash-mediawiki-2019.11.25
# _score -
t _type mediawiki
t channel DBQuery
t db_name -
t db_server 10.64.32.113
t db_user wikiuser
t facility user
t host mw1338
t http_method POST
t ip 10.64.32.50
t level ERROR
t logsource mw1338
t message Timed out waiting for replication to reach 171970645-171970645-288070551,171978778-171978778-3298185533,171978924-171978924-759099546,180359242-180359242-170963125
t method Wikimedia\Rdbms\DatabaseMysqlBase::masterPosWait
t mwversion 1.35.0-wmf.5
t normalized_message Timed out waiting for replication to reach {raw_pos}
t phpversion 7.2.24-1+0~20191026.31+debian9~1.gbpbbacde+wmf1
t program mediawiki
t raw_pos [object] (Wikimedia\Rdbms\MySQLMasterPos: 171970645-171970645-288070551,171978778-171978778-3298185533,171978924-171978924-759099546,180359242-180359242-170963125)
t referrer -
t reqId 6648b5800cd87944acdd8f7b
t server jobrunner.discovery.wmnet
t severity err
t shard s8
t sql SELECT MASTER_GTID_WAIT('171978924-171978924-759099546', 1)
t tags input-kafka-rsyslog-udp-localhost, rsyslog-udp-localhost, kafka, es
t timeout 1
t timestamp 2019-11-25T10:00:36+00:00
t type mediawiki
t url /rpc/RunSingleJob.php
t wait_pos 171978924-171978924-759099546
t wiki wikidatawiki