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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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 <[email protected]> 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 <[email protected]> >>>> 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 <[email protected]> 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 >>>>>> [email protected] >>>>>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Jaime Crespo >>>>> <http://wikimedia.org> >>>>> >>>>> _______________________________________________ >>>>> Labs-l mailing list >>>>> [email protected] >>>>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>>>> >>>>> >>>> >>>> _______________________________________________ >>>> Labs-l mailing list >>>> [email protected] >>>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>>> >>>> >>> >>> >>> -- >>> Jaime Crespo >>> <http://wikimedia.org> >>> >>> _______________________________________________ >>> Labs-l mailing list >>> [email protected] >>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>> >>> >> >
_______________________________________________ Labs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/labs-l
