[Labs-l] Query takes forever on labsdb

[Bruce Myers]

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 
> <mailto:jcrespo at wikimedia.org>> wrote:
>
>     On Sat, Aug 29, 2015 at 10:36 PM, Alex Monk <krenair at gmail.com
>     <mailto: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 <mailto: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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20150830/878700f8/attachment-0001.html>