[Labs-l] Empty Cat query
Huji Lee
huji.huji at gmail.com
Fri Sep 23 16:15:08 UTC 2016
I still need to know how to run queries on specific servers though.
Commands like this use to work but don't anymore:
mysql -h labsdb1001 fawiki -e "SELECT count(*) FROM imagelinks"
On Fri, Sep 23, 2016 at 12:02 PM, Huji Lee <huji.huji at gmail.com> wrote:
> With two connections to the DB via Terminal, it is possible! Here is what
> I got; it matches what you sent above too:
>
>
>
> MariaDB [fawiki_p]> show processlist;
> +---------+--------+-------------------+----------+---------
> +------+----------------------------+-----------------------
> ------------------------------------------------------------
> -------------------+----------+
> | Id | User | Host | db | Command | Time |
> State | Info
> |
> Progress |
> +---------+--------+-------------------+----------+---------
> +------+----------------------------+-----------------------
> ------------------------------------------------------------
> -------------------+----------+
> | 3556437 | s51403 | 10.68.23.58:53391 | fawiki_p | Query | 19 |
> Queried about 5450000 rows | select page_title, count(ll_lang) from page
> join category on page_title = cat_title left join catego | 0.000 |
> | 3556865 | s51403 | 10.68.23.58:54083 | fawiki_p | Query | 0 |
> init | show processlist
> |
> 0.000 |
> +---------+--------+-------------------+----------+---------
> +------+----------------------------+-----------------------
> ------------------------------------------------------------
> -------------------+----------+
> 2 rows in set (0.00 sec)
>
> MariaDB [fawiki_p]> show explain for 3556437;
> +------+-------------+---------------+--------+-------------
> ---------------------------+-----------------------------+--
> -------+------------------------+--------+------------------
> ----------------------------+
> | id | select_type | table | type |
> possible_keys | key |
> key_len | ref | rows | Extra
> |
> +------+-------------+---------------+--------+-------------
> ---------------------------+-----------------------------+--
> -------+------------------------+--------+------------------
> ----------------------------+
> | 1 | SIMPLE | page | ref | name_title,page_redirect_namespace_len
> | page_redirect_namespace_len | 5 | const,const | 364177 |
> Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | category | eq_ref |
> cat_title | cat_title |
> 257 | fawiki.page.page_title | 1 | Using
> index |
> | 1 | SIMPLE | categorylinks | ref |
> cl_timestamp,cl_sortkey | cl_timestamp |
> 257 | fawiki.page.page_title | 12 | Using where; Using
> index |
> | 1 | SIMPLE | templatelinks | ref |
> tl_from,tl_namespace | tl_namespace |
> 4 | const | 390622 | Using where; Using
> index |
> | 1 | SIMPLE | langlinks | ref |
> ll_from | ll_from |
> 4 | fawiki.page.page_id | 104911 | Using
> index |
> +------+-------------+---------------+--------+-------------
> ---------------------------+-----------------------------+--
> -------+------------------------+--------+------------------
> ----------------------------+
>
>
> On Fri, Sep 23, 2016 at 11:58 AM, Huji Lee <huji.huji at gmail.com> wrote:
>
>> Oh, I misread your comment.
>>
>> I doubt SHOW EXPLAIN would work via Quarry, so I am trying to get it to
>> work via terminal access to Labs. I wonder though if it works for a query
>> that fails to complete.
>>
>> On Fri, Sep 23, 2016 at 11:53 AM, Jaime Crespo <jcrespo at wikimedia.org>
>> wrote:
>>
>>> Huji, note I suggested SHOW EXPLAIN, not EXPLAIN; it is a different
>>> command, check the link I provided and report if that doesn't work for you.
>>>
>>> On Fri, Sep 23, 2016 at 5:31 PM, 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%B
>>>>>> 1%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
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> 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/51e69049/attachment-0001.html>
More information about the Labs-l
mailing list