Setting this as security protected as this could be used as a DDoS.
The following query was run many time on s7 replicas:
root@db1174.eqiad.wmnet[centralauth]> explain SELECT /* IndexPager::buildQueryInfo (MediaWiki\Extension\CentralAuth\Special\GlobalUsersPager) */ gu_name,gu_id,MAX(gu_locked) AS `gu_locked`,MAX(lu_attached_method) AS `lu_attached_method`,(SELECT GROUP_CONCAT(G.gug_group SEPARATOR '|') FROM `global_user_groups` `G` WHERE (G.gug_user = gu_id) AND (G.gug_expiry IS NULL OR G.gug_expiry >= '20220331051842') ) AS `gug_group`,(SELECT GROUP_CONCAT(IFNULL(G.gug_expiry, "null") SEPARATOR '|') FROM `global_user_groups` `G` WHERE (G.gug_user = gu_id) AND (G.gug_expiry IS NULL OR G.gug_expiry >= '20220331051842') ) AS `gug_expiry` FROM `globaluser` LEFT JOIN `localuser` ON ((gu_name = lu_name) AND lu_wiki = 'metawiki') WHERE gu_hidden_level = 0 GROUP BY gu_name, gu_id ORDER BY gu_name DESC LIMIT 51; +------+--------------------+------------+--------+--------------------+---------+---------+--------------------------------------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+------------+--------+--------------------+---------+---------+--------------------------------------+----------+----------------------------------------------+ | 1 | PRIMARY | globaluser | ALL | NULL | NULL | NULL | NULL | 64813976 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | localuser | eq_ref | PRIMARY,lu_name | PRIMARY | 514 | const,centralauth.globaluser.gu_name | 1 | Using where | | 3 | DEPENDENT SUBQUERY | G | ref | PRIMARY,gug_expiry | PRIMARY | 4 | func | 1 | Using where | | 2 | DEPENDENT SUBQUERY | G | ref | PRIMARY,gug_expiry | PRIMARY | 4 | func | 1 | Using where | +------+--------------------+------------+--------+--------------------+---------+---------+--------------------------------------+----------+----------------------------------------------+ 4 rows in set (0.001 sec)
There were two problems:
- the query is very slow (full scan - takes more than 10 minutes to run)
- it had hundreds of threads spawned