Page MenuHomePhabricator

rcshow=oresreview is slow
Closed, ResolvedPublic

Description

Logstash has a couple warnings like this:

Expectation (readQueryTime <= 5) by ApiMain::setRequestExpectations not met (actual: 41.6174659729):
query: SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid FROM `recentchanges` INNER JOIN `ores_model` ON...

The query in question is

mysql:wikiadmin@db1080 [enwiki]> DESCRIBE SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid  FROM `recentchanges` INNER JOIN `ores_model` ON ((oresm_name = 'damaging' AND oresm_is_current = 1)) INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_model = oresm_id AND oresc_class = 1))   WHERE rc_type IN ('0','1','3','6')  AND (oresc_probability > '0.49')  ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 11 ;
+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+
| id   | select_type | table               | type | possible_keys                           | key               | key_len | ref                                | rows    | Extra                                                     |
+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+
|    1 | SIMPLE      | ores_model          | ref  | PRIMARY,oresm_version,ores_model_status | ores_model_status | 35      | const,const                        |       1 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | recentchanges       | ALL  | tmp_1                                   | NULL              | NULL    | NULL                               | 9933340 | Using where; Using join buffer (flat, BNL join)           |
|    1 | SIMPLE      | ores_classification | ref  | oresc_winner                            | oresc_winner      | 4       | enwiki.recentchanges.rc_this_oldid |       1 | Using where                                               |
+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+

Even for scanning 10 million rows, 50 seconds seems pretty extreme. And there should be no reason to do that: without the limit, the query matches about a million rows so probably the limit of 11 should be hit after a few hundred.

Event Timeline

Tgr created this task.Dec 7 2016, 4:03 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 7 2016, 4:03 AM
Tgr updated the task description. (Show Details)Dec 7 2016, 4:04 AM
Tgr added a comment.Dec 7 2016, 5:12 AM

Huhh, query optimizer fail.

mysql:wikiadmin@db1080 [enwiki]> SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid  FROM `recentchanges` INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_model = 21 AND oresc_class = 1))   WHERE rc_type IN ('0','1','3','6')  AND (oresc_probability > '0.49')  ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 11 ;

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid  FROM `recentchanges` INNER JOIN `ores_model` ON ((oresm_name = 'damaging' AND oresm_is_current = 1)) INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_model = oresm_id AND oresc_class = 1))   WHERE rc_type IN ('0','1','3','6')  AND (oresc_probability > '0.49')  ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 11 ; 
+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
| rc_id     | rc_timestamp   | rc_namespace | rc_title                      | rc_cur_id | rc_type | rc_deleted | rc_this_oldid | rc_last_oldid |
+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
| 888731689 | 20161207050821 |            0 | T-Mobile_Arena                |  39717296 |       0 |          0 |     753442718 |     752916791 |
| 888731686 | 20161207050818 |            0 | NetSim                        |  26077056 |       0 |          0 |     753442715 |     752605553 |
| 888731667 | 20161207050809 |            0 | Northwest_High_School_(Texas) |   8412367 |       0 |          0 |     753442707 |     753442564 |
| 888731649 | 20161207050800 |            0 | Chagossians                   |    727770 |       0 |          0 |     753442696 |     752846555 |
| 888731646 | 20161207050758 |            0 | Quantico_(season_2)           |  50715532 |       0 |          0 |     753442693 |     753276340 |
| 888731643 | 20161207050757 |            0 | Vito_Acconci                  |    222210 |       0 |          0 |     753442692 |     751390888 |
| 888731610 | 20161207050745 |            0 | Order_of_St._George           |    430958 |       0 |          0 |     753442669 |     753442345 |
| 888731599 | 20161207050740 |            0 | Vincent_Meteor                |  24950535 |       0 |          0 |     753442662 |     731216087 |
| 888731591 | 20161207050733 |            0 | Geothermal_power_in_Japan     |  24737295 |       0 |          0 |     753442655 |     753442619 |
| 888731581 | 20161207050730 |            0 | Globisporangium_sylvaticum    |  52295905 |       0 |          0 |     753442647 |     753442194 |
| 888731550 | 20161207050713 |            0 | Geothermal_power_in_Japan     |  24737295 |       0 |          0 |     753442619 |     741661854 |
+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
11 rows in set (0.01 sec)

+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
| rc_id     | rc_timestamp   | rc_namespace | rc_title                      | rc_cur_id | rc_type | rc_deleted | rc_this_oldid | rc_last_oldid |
+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
| 888731689 | 20161207050821 |            0 | T-Mobile_Arena                |  39717296 |       0 |          0 |     753442718 |     752916791 |
| 888731686 | 20161207050818 |            0 | NetSim                        |  26077056 |       0 |          0 |     753442715 |     752605553 |
| 888731667 | 20161207050809 |            0 | Northwest_High_School_(Texas) |   8412367 |       0 |          0 |     753442707 |     753442564 |
| 888731649 | 20161207050800 |            0 | Chagossians                   |    727770 |       0 |          0 |     753442696 |     752846555 |
| 888731646 | 20161207050758 |            0 | Quantico_(season_2)           |  50715532 |       0 |          0 |     753442693 |     753276340 |
| 888731643 | 20161207050757 |            0 | Vito_Acconci                  |    222210 |       0 |          0 |     753442692 |     751390888 |
| 888731610 | 20161207050745 |            0 | Order_of_St._George           |    430958 |       0 |          0 |     753442669 |     753442345 |
| 888731599 | 20161207050740 |            0 | Vincent_Meteor                |  24950535 |       0 |          0 |     753442662 |     731216087 |
| 888731591 | 20161207050733 |            0 | Geothermal_power_in_Japan     |  24737295 |       0 |          0 |     753442655 |     753442619 |
| 888731581 | 20161207050730 |            0 | Globisporangium_sylvaticum    |  52295905 |       0 |          0 |     753442647 |     753442194 |
| 888731550 | 20161207050713 |            0 | Geothermal_power_in_Japan     |  24737295 |       0 |          0 |     753442619 |     741661854 |
+-----------+----------------+--------------+-------------------------------+-----------+---------+------------+---------------+---------------+
11 rows in set (33.50 sec)


mysql:wikiadmin@db1080 [enwiki]> DESCRIBE SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid  FROM `recentchanges` INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_model = 21 AND oresc_class = 1))   WHERE rc_type IN ('0','1','3','6')  AND (oresc_probability > '0.49')  ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 11 ;

DESCRIBE SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid  FROM `recentchanges` INNER JOIN `ores_model` ON ((oresm_name = 'damaging' AND oresm_is_current = 1)) INNER JOIN `ores_classification` ON ((rc_this_oldid = oresc_rev AND oresc_model = oresm_id AND oresc_class = 1))   WHERE rc_type IN ('0','1','3','6')  AND (oresc_probability > '0.49')  ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 11 ; 
+------+-------------+---------------------+-------+---------------+--------------+---------+------------------------------------+------+-------------+
| id   | select_type | table               | type  | possible_keys | key          | key_len | ref                                | rows | Extra       |
+------+-------------+---------------------+-------+---------------+--------------+---------+------------------------------------+------+-------------+
|    1 | SIMPLE      | recentchanges       | index | tmp_1         | rc_timestamp | 16      | NULL                               |   11 | Using where |
|    1 | SIMPLE      | ores_classification | ref   | oresc_winner  | oresc_winner | 4       | enwiki.recentchanges.rc_this_oldid |    1 | Using where |
+------+-------------+---------------------+-------+---------------+--------------+---------+------------------------------------+------+-------------+
2 rows in set (0.00 sec)

+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+
| id   | select_type | table               | type | possible_keys                           | key               | key_len | ref                                | rows    | Extra                                                     |
+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+
|    1 | SIMPLE      | ores_model          | ref  | PRIMARY,oresm_version,ores_model_status | ores_model_status | 35      | const,const                        |       1 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | recentchanges       | ALL  | tmp_1                                   | NULL              | NULL    | NULL                               | 9930986 | Using where; Using join buffer (flat, BNL join)           |
|    1 | SIMPLE      | ores_classification | ref  | oresc_winner                            | oresc_winner      | 4       | enwiki.recentchanges.rc_this_oldid |       1 | Using where                                               |
+------+-------------+---------------------+------+-----------------------------------------+-------------------+---------+------------------------------------+---------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

So looks like ores_model should be handled in PHP instead.

Restricted Application added a project: Scoring-platform-team. · View Herald TranscriptFeb 3 2017, 4:19 PM
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptFeb 9 2017, 3:30 PM
Ladsgroup moved this task from Incoming to Blocked on others on the User-Ladsgroup board.

Change 337263 had a related patch set uploaded (by Gergő Tisza):
Make rcshow=oresreview bypass query optimizer failure

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

Change 337263 merged by jenkins-bot:
Make rcshow=oresreview bypass query optimizer failure

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

Halfak closed this task as Resolved.Mar 16 2017, 9:22 PM