ApiQueryAllPages is slow on langlinks edgecase
Open, NormalPublic

Description

Spotted in production:

May  1 15:45:18 mw1223:  SlowTimer [59999ms] at runtime/ext_mysql: slow query:
SELECT /* ApiQueryAllPages::run Pywikibot-test */  /*! STRAIGHT_JOIN */
    page_namespace,page_title,page_id,page_content_model,page_restrictions,page_is_redirect,
    page_is_new,page_touched,page_latest,page_len 
FROM
    `page`,`langlinks`
WHERE
    page_namespace = '0' AND (page_title>='!') AND (page_id=ll_from)  
GROUP BY
    page_title,page_id ORDER BY page_title LIMIT 6

The explain for the query is deceptively simple:

1mysql:wikiadmin@db1052 [enwiki]> explain SELECT /* ApiQueryAllPages::run Pywikibot-test */ /*! STRAIGHT_JOIN */ page_namespace,page_title,page_id,page_content_model,page_restrictions,page_is_redirect,page_is_new,page_touched,page_latest,page_len FROM `page`,`langlinks` WHERE page_namespace = '0' AND (page_title>='!') AND (page_id=ll_from) GROUP BY page_title,page_id ORDER BY page_title LIMIT 6;
2+------+-------------+-----------+------+--------------------+------------+---------+---------------------+----------+------------------------------------+
3| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4+------+-------------+-----------+------+--------------------+------------+---------+---------------------+----------+------------------------------------+
5| 1 | SIMPLE | page | ref | PRIMARY,name_title | name_title | 4 | const | 18727509 | Using index condition; Using where |
6| 1 | SIMPLE | langlinks | ref | ll_from | ll_from | 4 | enwiki.page.page_id | 2 | Using index |
7+------+-------------+-----------+------+--------------------+------------+---------+---------------------+----------+------------------------------------+
82 rows in set (0.08 sec)

Per @Anomie on IRC:

It's choosing a good index and not filesorting. The only bit that looks suspicious to me is the join with langlinks, if langlinks is mostly empty on that wiki, which would make it have to go through a lot of rows."

On a wiki like enwiki, this query is fast. On something like wikidatawiki where there's basically no langlinks in NS0, the edge case is really really slow, as in 60s or more.

An idea was floated:

No idea. Removing the straight join might work for wikidatawiki since langlinks is so small, but still wouldn't help the general case where other namespaces have lots of langlinks. Could add a "ll_from_namespace" column, I suppose.

But:

That could work, but would require backfilling to be useful.

demon created this task.May 1 2015, 5:00 PM
demon updated the task description. (Show Details)
demon raised the priority of this task from to Normal.
demon added subscribers: demon, Anomie.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 1 2015, 5:00 PM
Anomie added a comment.May 1 2015, 5:06 PM

It's basically one of those "Go through all pages in the namespace to find the tiny fraction that pass the filter" issues.

If nothing can be done to have the filtering done in SQL, I suppose we could select the $limit rows with a left join and filter them in PHP when $wgMiserMode is set (returning fewer than $limit, possibly 0, to the client), like we do in a few other places.

hashar updated the task description. (Show Details)Jun 8 2015, 8:37 AM
hashar set Security to None.
hashar moved this task from Backlog to Production Impact on the Wikimedia-log-errors board.
This comment was removed by Krinkle.
Krinkle removed a subscriber: Krinkle.Apr 16 2016, 12:44 AM