Page MenuHomePhabricator

Slow query taking up to 60 seconds to run IndexPager::buildQueryInfo (NewFilesPager)
Closed, ResolvedPublic

Description

The following query needs to be looked at.
It was arriving to commonswiki and it was taking almost 60 seconds to finish (the query killer kicks in at 60) so it is likely to be killed often:

root@db1084.eqiad.wmnet[commonswiki]> explain SELECT /* IndexPager::buildQueryInfo (NewFilesPager) */ img_name, img_timestamp, actor_img_user.actor_user AS `img_user`, actor_img_user.actor_name AS `img_user_text`, img_actor FROM `image` JOIN `actor` `actor_img_user` ON ((actor_img_user.actor_id = img_actor)) LEFT JOIN `user_groups` ON (ug_group = 'bot' AND (ug_user = actor_img_user.actor_user) AND (ug_expiry IS NULL OR ug_expiry >= '20200206194925')) WHERE (ug_group IS NULL) ORDER BY img_timestamp DESC LIMIT 51 /* 2567daabb35c15eb12ef18d460139091 db1084 commonswiki 59s */;
+------+-------------+----------------+--------+----------------------------+---------------------+---------+---------------------------------------------+---------+---------------------------------+
| id   | select_type | table          | type   | possible_keys              | key                 | key_len | ref                                         | rows    | Extra                           |
+------+-------------+----------------+--------+----------------------------+---------------------+---------+---------------------------------------------+---------+---------------------------------+
|    1 | SIMPLE      | actor_img_user | ALL    | PRIMARY                    | NULL                | NULL    | NULL                                        | 8690956 | Using temporary; Using filesort |
|    1 | SIMPLE      | user_groups    | eq_ref | PRIMARY,ug_group,ug_expiry | PRIMARY             | 261     | commonswiki.actor_img_user.actor_user,const |       1 | Using where; Not exists         |
|    1 | SIMPLE      | image          | ref    | img_actor_timestamp        | img_actor_timestamp | 8       | commonswiki.actor_img_user.actor_id         |       2 | Using index                     |
+------+-------------+----------------+--------+----------------------------+---------------------+---------+---------------------------------------------+---------+---------------------------------+
3 rows in set (0.00 sec)

Details

Related Gerrit Patches:

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 6 2020, 9:01 PM
Daimona added a subscriber: Daimona.Feb 6 2020, 9:31 PM

Another bad plan issue. It should do something more like

wikiadmin@10.64.16.102(commonswiki)> explain SELECT /*!STRAIGHT_JOIN*/ /* IndexPager::buildQueryInfo (NewFilesPager) */ img_name, img_timestamp, actor_img_user.actor_user AS `img_user`, actor_img_user.actor_name AS `img_user_text`, img_actor FROM `image` JOIN `actor` `actor_img_user` ON ((actor_img_user.actor_id = img_actor)) LEFT JOIN `user_groups` ON (ug_group = 'bot' AND (ug_user = actor_img_user.actor_user) AND (ug_expiry IS NULL OR ug_expiry >= '20200206194925')) WHERE (ug_group IS NULL) ORDER BY img_timestamp DESC LIMIT 51;
+------+-------------+----------------+--------+----------------------------+---------------+---------+---------------------------------------------+------+-------------------------+
| id   | select_type | table          | type   | possible_keys              | key           | key_len | ref                                         | rows | Extra                   |
+------+-------------+----------------+--------+----------------------------+---------------+---------+---------------------------------------------+------+-------------------------+
|    1 | SIMPLE      | image          | index  | img_actor_timestamp        | img_timestamp | 18      | NULL                                        |   51 |                         |
|    1 | SIMPLE      | actor_img_user | eq_ref | PRIMARY                    | PRIMARY       | 8       | commonswiki.image.img_actor                 |    1 |                         |
|    1 | SIMPLE      | user_groups    | eq_ref | PRIMARY,ug_group,ug_expiry | PRIMARY       | 261     | commonswiki.actor_img_user.actor_user,const |    1 | Using where; Not exists |
+------+-------------+----------------+--------+----------------------------+---------------+---------+---------------------------------------------+------+-------------------------+

The estimate of 51 rows there is off a bit (actual for this query is something like 122 based on SHOW STATUS output), but still 122 is far better than 8 million and completes in 0.00 seconds. @Marostegui, think we should add that STRAIGHT_JOIN?

Yeah, let's go for that @Anomie - I am curious to test this (and all the other optimizer issues we've tagged so far) on 10.4 to see if there is some improvement there.

Heh, turns out we had a very similar issue in T124205.

Change 570942 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@master] NewFilesPager: Apply STRAIGHT_JOIN to all code paths

https://gerrit.wikimedia.org/r/570942

Change 570942 merged by jenkins-bot:
[mediawiki/core@master] NewFilesPager: Apply STRAIGHT_JOIN to all code paths

https://gerrit.wikimedia.org/r/570942

Anomie closed this task as Resolved.Feb 10 2020, 5:58 PM
Anomie claimed this task.