<div dir="ltr"><div>Oh that is smart!<br><br></div>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.<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Sun, Aug 30, 2015 at 5:33 PM, Bruce Myers <span dir="ltr"><<a href="mailto:bruce@brucemyers.com" target="_blank">bruce@brucemyers.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<div>Here is an EXPLAIN work around that I
learned from examining the code of the inoperable Query Analyzer
tool <a href="https://tools.wmflabs.org/tools-info/optimizer.py" target="_blank">https://tools.wmflabs.org/tools-info/optimizer.py</a><br>
<br>
Open 2 SQL sessions<br>
<br>
In session 1:<br>
SELECT CONNECTION_ID() AS conid;<br>
Note the number returned.<br>
Run the query to be explained.<br>
<br>
In session 2:<br>
Use the number noted above for <conid><br>
SHOW EXPLAIN FOR <conid>;<br>
<br>
SHOW EXPLAIN is a MariaDB extension usable by regular users.<div><div class="h5"><br>
<br>
On 15-08-30 05:01 PM, Huji Lee wrote:<br>
</div></div></div><div><div class="h5">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>That explains a lot.<br>
<br>
</div>
Unfortunately, we (regular uses of Labs) cannot run EXPLAIN
to identify these issues ourselves. I will change my code to
use labsdb1001 for now.<br>
<br>
</div>
Thanks,<br>
<br>
</div>
Huji<br>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Sun, Aug 30, 2015 at 1:27 PM, Jaime
Crespo <span dir="ltr"><<a href="mailto:jcrespo@wikimedia.org" target="_blank">jcrespo@wikimedia.org</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><span>On Sat, Aug 29, 2015 at 10:36
PM, Alex Monk <span dir="ltr"><<a href="mailto:krenair@gmail.com" target="_blank"></a><a href="mailto:krenair@gmail.com" target="_blank">krenair@gmail.com</a>></span>
wrote:<br>
</span>
<div class="gmail_extra">
<div class="gmail_quote"><span>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">Looks like it works on labsdb1001,
but not labsdb1002 and labsdb1003.</div>
</blockquote>
<div><br>
</div>
</span>
<div>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":<br>
<br>
MariaDB LABS labsdb1002 fawiki_p > EXPLAIN select
page_title<br>
-> from page<br>
-> join category on page_title = cat_title<br>
-> left join categorylinks on page_title =
cl_to<br>
-> left join templatelinks on tl_from =
page_id and tl_namespace = 10 and tl_title in
('رده_خالی' , 'رده_بهتر')<br>
-> where page_namespace = 14 and cl_to is
null and tl_title is null<br>
-> group by page_title\G<br>
*************************** 1. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: page<br>
type: ref<br>
possible_keys: name_title<br>
key: name_title<br>
key_len: 4<br>
ref: const<br>
rows: 322989<br>
Extra: Using where; Using index<br>
*************************** 2. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: category<br>
type: eq_ref<br>
possible_keys: cat_title<br>
key: cat_title<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 1<br>
Extra: Using index<br>
*************************** 3. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: categorylinks<br>
type: ref<br>
possible_keys: cl_timestamp,cl_sortkey<br>
key: cl_timestamp<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 72785<br>
Extra: Using where; Using index<br>
*************************** 4. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: templatelinks<br>
type: ref<br>
possible_keys:
tl_from,tl_namespace,tl_backlinks_namespace<br>
key: tl_namespace<br>
key_len: 4<br>
ref: const<br>
rows: 383047<br>
Extra: Using where; Using index<br>
4 rows in set (0.01 sec)<br>
<br>
<br>
<br>
<br>
MariaDB LABS labsdb1001 fawiki_p > EXPLAIN select
page_title<br>
-> from page<br>
-> join category on page_title = cat_title<br>
-> left join categorylinks on page_title =
cl_to<br>
-> left join templatelinks on tl_from =
page_id and tl_namespace = 10 and tl_title in
('رده_خالی' , 'رده_بهتر')<br>
-> where page_namespace = 14 and cl_to is
null and tl_title is null<br>
-> group by page_title\G<br>
*************************** 1. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: page<br>
type: ref<br>
possible_keys: name_title<br>
key: name_title<br>
key_len: 4<br>
ref: const<br>
rows: 340784<br>
Extra: Using where; Using index<br>
*************************** 2. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: category<br>
type: eq_ref<br>
possible_keys: cat_title<br>
key: cat_title<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 1<br>
Extra: Using index<br>
*************************** 3. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: categorylinks<br>
type: ref<br>
possible_keys: cl_timestamp,cl_sortkey<br>
key: cl_timestamp<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 10<br>
Extra: Using where; Using index<br>
*************************** 4. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: templatelinks<br>
type: ref<br>
possible_keys:
tl_from,tl_namespace,tl_backlinks_namespace<br>
key: tl_from<br>
key_len: 8<br>
ref: fawiki.page.page_id,const<br>
rows: 16<br>
Extra: Using where; Using index<br>
4 rows in set (0.00 sec)<br>
<br>
</div>
<div>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.<br>
<br>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/mailman/listinfo/labs-l</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Labs-l mailing list
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" target="_blank">https://lists.wikimedia.org/mailman/listinfo/labs-l</a>
</pre>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/mailman/listinfo/labs-l</a><br>
<br></blockquote></div><br></div>