[Labs-l] Query takes forever on labsdb

[Huji Lee]

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> 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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20150830/a4f367a8/attachment.html>