[Labs-l] Empty Cat query

Fri Sep 23 15:33:19 UTC 2016

I am not so proficient with analyzing the EXPLAIN results. I see that in
all places I expected it, the query is using an index. Then how come it
suddenly became so slow?

Could it be that the DB requires its stats to be updated? We have had
issues like that in the past. An example is:
https://phabricator.wikimedia.org/T123985

Can one of you kindly remind me how I could connect to a specific replica
to run the query on that particular server?

On Fri, Sep 23, 2016 at 11:31 AM, Huji Lee <huji.huji at gmail.com> wrote:

> See https://quarry.wmflabs.org/query/12718 and the error message returned.
>
> EXPLAIN only works if you have access to the underlying tables. I only
> have access to the VIEWs built on top of actual wiki tables (for good
> reason, same applies to most of us) so I get an error when I try EXPLAIN.
>
> On Fri, Sep 23, 2016 at 11:24 AM, Jaime Crespo <jcrespo at wikimedia.org>
> wrote:
>
>> Explain (you should be able to run SHOW EXPLAIN on your own queries:
>> http://s.petrunia.net/blog/?p=89):
>>
>> EXPLAIN select page_title, count(ll_lang) from page join category on
>> page_title = cat_title left join categorylinks on page_title = cl_to left
>> join templatelinks on tl_from = page_id and tl_namespace = 10 and tl_title
>> in (   'رده_خالی' , 'رده_بهتر', 'رده_ابهام‌زدایی', 'رده_ردیابی‌کردن' ) left
>> join langlinks on page_id = ll_from where page_namespace = 14 and
>> page_is_redirect = 0 and cl_to is null and tl_title is null group by
>> page_title order by 2, 1 limit 5000\G
>> *************************** 1. row ***************************
>>            id: 1
>>   select_type: SIMPLE
>>         table: page
>>          type: ref
>> possible_keys: name_title,page_redirect_namespace_len
>>           key: page_redirect_namespace_len
>>       key_len: 5
>>           ref: const,const
>>          rows: 364273
>>         Extra: Using where; Using temporary; Using filesort
>> *************************** 2. row ***************************
>>            id: 1
>>   select_type: SIMPLE
>>         table: category
>>          type: eq_ref
>> possible_keys: cat_title
>>           key: cat_title
>>       key_len: 257
>>           ref: fawiki.page.page_title
>>          rows: 1
>>         Extra: Using index
>> *************************** 3. row ***************************
>>            id: 1
>>   select_type: SIMPLE
>>         table: categorylinks
>>          type: ref
>> possible_keys: cl_timestamp,cl_sortkey
>>           key: cl_timestamp
>>       key_len: 257
>>           ref: fawiki.page.page_title
>>          rows: 12
>>         Extra: Using where; Using index
>> *************************** 4. row ***************************
>>            id: 1
>>   select_type: SIMPLE
>>         table: templatelinks
>>          type: ref
>> possible_keys: tl_from,tl_namespace
>>           key: tl_namespace
>>       key_len: 4
>>           ref: const
>>          rows: 390610
>>         Extra: Using where; Using index
>> *************************** 5. row ***************************
>>            id: 1
>>   select_type: SIMPLE
>>         table: langlinks
>>          type: ref
>> possible_keys: ll_from
>>           key: ll_from
>>       key_len: 4
>>           ref: fawiki.page.page_id
>>          rows: 104910
>>         Extra: Using index
>>
>> On Fri, Sep 23, 2016 at 5:16 PM, Huji Lee <huji.huji at gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> I have a query [1] which I run weekly to identify empty categories;
>>> those that don't have interwiki links and stay empty for a while are then
>>> deleted by a sysop.
>>>
>>> The query ran just fine every week until Sep 2nd. [2] Since then, the
>>> query times out and the page doesn't get updated.
>>>
>>> The query is no that complex (a SELECT with five JOINs) and used to
>>> finish in about 3-5 minutes. I don't have EXPLAIN access on Labs so I
>>> cannot tell what is slowing it down. Can someone kindly take a look and
>>> advise why this suddenly stopped working?
>>>
>>> Thanks,
>>>
>>> Huji
>>>
>>> [1] https://quarry.wmflabs.org/query/3760
>>> [2] https://fa.wikipedia.org/w/index.php?title=%D9%88%DB%8C%DA%A
>>> 9%DB%8C%E2%80%8C%D9%BE%D8%AF%DB%8C%D8%A7:%DA%AF%D8%B2%D8%A7%
>>> D8%B1%D8%B4_%D8%AF%DB%8C%D8%AA%D8%A7%D8%A8%DB%8C%D8%B3/%
>>> D8%B1%D8%AF%D9%87%E2%80%8C%D9%87%D8%A7%DB%8C_%D8%AE%D8%A7%
>>> D9%84%DB%8C&action=history&uselang=en
>>>
>>> _______________________________________________
>>> Labs-l mailing list
>>> Labs-l at lists.wikimedia.org
>>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>>
>>>
>>
>>
>> --
>> Jaime Crespo
>> <http://wikimedia.org>
>>
>> _______________________________________________
>> Labs-l mailing list
>> Labs-l at lists.wikimedia.org
>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20160923/f0f7bf4c/attachment.html>