Page MenuHomePhabricator

Another slow running query on - lvwiki.labsdb this time.
Closed, ResolvedPublic

Description

Per T114513 from 20 mins ago, but lvwiki this time. Same query, same plan. Same fix ?

tools.tb-dev@tools-bastion-01:~/public_html/RLRL$ mysql -h lvwiki.labsdb
MariaDB [(none)]> use p50380g50491__rlrl_lvwiki_p;
MariaDB [p50380g50491__rlrl_lvwiki_p]>
  SELECT sug_orig_ns, sug_orig, sug_new_ns, sug_new 
  FROM p50380g50491__rlrl_lvwiki_p.suggestions s 
  WHERE NOT EXISTS (
    SELECT 1   
    FROM lvwiki_p.pagelinks   
    INNER JOIN lvwiki_p.page ON pl_from = page_id   
    WHERE pl_namespace = s.sug_orig_ns   
    AND   pl_title = s.sug_orig   
    AND   page_namespace in (0, 10, 14)   
    AND   page_title NOT LIKE '%/sandbox'   
    AND   page_title NOT LIKE '%/testcases' )
  AND s.sug_status > 0;

Event Timeline

Tb assigned this task to jcrespo.
Tb raised the priority of this task from to Needs Triage.
Tb updated the task description. (Show Details)
Tb added projects: DBA, Cloud-Services, Toolforge.
Tb added a subscriber: Tb.

Can you send a list of all wikis you potentially query? That way we avoid the back an forth and fix them all at once.

The tool generating these queries is configured for:

enwiki, enwiktionary, enwikt, enwikq, ennews,
dewiki, itwiki, frwiki, plwiki, eswiki, ruwiki, nlwiki, jawiki,
scowiki, mlwiki, ukwiki, cawiki, ptwiki, lvwiki

I've tested them all; only cawiki (now fixed) and lvwiki (this task) seem to be generating weird plans for this query.

That should be it:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1781
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: pagelinks
         type: ref
possible_keys: pl_from,pl_namespace,pl_backlinks_namespace
          key: pl_namespace
      key_len: 261
          ref: func,func
         rows: 15
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: page
         type: eq_ref
possible_keys: PRIMARY,name_title,page_random,page_len,page_redirect_namespace_len
          key: PRIMARY
      key_len: 4
          ref: lvwiki.pagelinks.pl_from
         rows: 1
        Extra: Using where
3 rows in set (0.00 sec)

A vast improvement once again - many thanks.