[Labs-l] Query takes forever on labsdb
Huji Lee
huji.huji at gmail.com
Sun Aug 30 22:17:11 UTC 2015
Oh that is smart!
By the way, after some exploration I figured out I really don't know how to
connect to a specific labsdb instance. What is the host name I should be
using on command line? fawiki.labsdb001 is not the answer.
On Sun, Aug 30, 2015 at 5:33 PM, Bruce Myers <bruce at brucemyers.com> wrote:
> Here is an EXPLAIN work around that I learned from examining the code of
> the inoperable Query Analyzer tool
> https://tools.wmflabs.org/tools-info/optimizer.py
>
> Open 2 SQL sessions
>
> In session 1:
> SELECT CONNECTION_ID() AS conid;
> Note the number returned.
> Run the query to be explained.
>
> In session 2:
> Use the number noted above for <conid>
> SHOW EXPLAIN FOR <conid>;
>
> SHOW EXPLAIN is a MariaDB extension usable by regular users.
>
>
> On 15-08-30 05:01 PM, Huji Lee wrote:
>
> That explains a lot.
>
> Unfortunately, we (regular uses of Labs) cannot run EXPLAIN to identify
> these issues ourselves. I will change my code to use labsdb1001 for now.
>
> Thanks,
>
> Huji
>
> On Sun, Aug 30, 2015 at 1:27 PM, Jaime Crespo <jcrespo at wikimedia.org>
> wrote:
>
>> On Sat, Aug 29, 2015 at 10:36 PM, Alex Monk < <krenair at gmail.com>
>> krenair at gmail.com> wrote:
>>
>>> Looks like it works on labsdb1001, but not labsdb1002 and labsdb1003.
>>>
>>
>> Yes, there is nothing wrong with the databases, such as corruption or
>> something similar, but MySQL choses a less optimal plan for 2 and 3, as you
>> can see on this explain, going from 15 second execution time to "forever":
>>
>> MariaDB LABS labsdb1002 fawiki_p > EXPLAIN select page_title
>> -> 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 ('رده_خالی' , 'رده_بهتر')
>> -> where page_namespace = 14 and cl_to is null and tl_title is null
>> -> group by page_title\G
>> *************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: page
>> type: ref
>> possible_keys: name_title
>> key: name_title
>> key_len: 4
>> ref: const
>> rows: 322989
>> Extra: Using where; Using index
>> *************************** 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: 72785
>> Extra: Using where; Using index
>> *************************** 4. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: templatelinks
>> type: ref
>> possible_keys: tl_from,tl_namespace,tl_backlinks_namespace
>> key: tl_namespace
>> key_len: 4
>> ref: const
>> rows: 383047
>> Extra: Using where; Using index
>> 4 rows in set (0.01 sec)
>>
>>
>>
>>
>> MariaDB LABS labsdb1001 fawiki_p > EXPLAIN select page_title
>> -> 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 ('رده_خالی' , 'رده_بهتر')
>> -> where page_namespace = 14 and cl_to is null and tl_title is null
>> -> group by page_title\G
>> *************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: page
>> type: ref
>> possible_keys: name_title
>> key: name_title
>> key_len: 4
>> ref: const
>> rows: 340784
>> Extra: Using where; Using index
>> *************************** 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: 10
>> Extra: Using where; Using index
>> *************************** 4. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: templatelinks
>> type: ref
>> possible_keys: tl_from,tl_namespace,tl_backlinks_namespace
>> key: tl_from
>> key_len: 8
>> ref: fawiki.page.page_id,const
>> rows: 16
>> Extra: Using where; Using index
>> 4 rows in set (0.00 sec)
>>
>> As a workaround, for now, please use labsdb1001, instead of default host
>> for fawiki for this particular query. Sadly, you cannot use FORCE/USE index
>> on views- I will try to reanalyze the table statistics over the next week
>> to see if that fixes this particular query.
>>
>>
>> _______________________________________________
>> Labs-l mailing list
>> Labs-l at lists.wikimedia.org
>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>
>>
>
>
> _______________________________________________
> Labs-l mailing listLabs-l at lists.wikimedia.orghttps://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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20150830/34ba5a75/attachment.html>
More information about the Labs-l
mailing list