Page MenuHomePhabricator

Special:AncientPages times out on translatewiki.net
Open, LowPublic

Description

2014/12/06 12:27:13 [error] 12579#0: *513670 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 1.2.3.4, server: translatewiki.net, request: "GET /wiki/Special:AncientPages HTTP/1.1", upstream: "fastcgi://unix:/run/php5-fpm.sock", host: "translatewiki.net"

I have nginx upstream timeout set to 15 seconds. Curiously enough it does not show up as PHP fatal error. Looking with mytop the query takes about 50 seconds. The query should be faster or not enabled by default.

Event Timeline

Nikerabbit raised the priority of this task from to Needs Triage.
Nikerabbit updated the task description. (Show Details)
Nikerabbit changed Security from none to None.
Nikerabbit added a subscriber: Nikerabbit.
EXPLAIN SELECT /* AncientPagesPage::reallyDoQuery Nike */ page_namespace AS namespace,page_title AS title,rev_timestamp AS value
FROM `bw_page`,`bw_revision` WHERE page_namespace
IN ('0','8','1102','1200','1202','1204','1206','1208','1210','1212','1214','1216','1218','1220','1222','1224','1226',
'1228','1230','1232','1234','1236','1238','1240','1242','1244','1246','1248','1250','1252','1254','1256','1258','1260')
AND page_is_redirect = '0' AND (page_latest=rev_id) ORDER BY value LIMIT 101:

*** row 1 ***
          table:  bw_page
           type:  ALL
  possible_keys:  name_title,page_redirect_namespace_len
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  4047639
          Extra:  Using where; Using temporary; Using filesort
*** row 2 ***
          table:  bw_revision
           type:  eq_ref
  possible_keys:  rev_id
            key:  rev_id
        key_len:  4
            ref:  translatewiki_net.bw_page.page_latest
           rows:  1
          Extra:  NULL

The special page is already marked as expensive, so setting miser mode the page (and all the others) are no longer running, but needs than a periodically run of updateSpecialPages.php

Even now, with more powerful server this issue still persists:

SELECT /* AncientPagesPage::reallyDoQuery Nike */ page_namespace AS namespace,page_title AS title,rev_timestamp AS value FROM `bw_page`,`bw_revision` WHERE page_namespace IN ('0','8','1102','1200','1202','1204','1206','1208','1210','1212','1214','1216','1218','1220','1222','1224','1226','1228','1230','1232','1234','1236','1238','1240','1242','1244','1246','1248','1250','1252','1254','1256','1258','1260','1262','1264') AND page_is_redirect = '0' AND (page_latest=rev_id) ORDER BY value LIMIT 101;
101 rows in set (35.60 sec)

I don't understand why this much slow queries are allowed in MediaWiki by default. I do not consider the heavy handed miser mode as a good solution: it disables way too much stuff that works, in my opinion.

FWIW this special page is now cached on translatewiki.net.