MySQL Forums
Forum List  »  Newbie

Re: MySQL using 80-90% of CPU
Posted by: Rick James
Date: May 19, 2009 08:16AM

Tuning cannot fix high CPU. Redesigning SELECTs/indexes/schema can. But I don't have enough information to work with. You found the naughty query, now I need to see (as previously requested)
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]

select  a.id, call_category,logged_date, a.call_id,
        customer_name, phone, mobile, account_number,
        address, a.area, city, complaint,
        process_code, agent_reply, agent_comments,
        fstatus, cstatus, assignto, sub_process_code,
        cleared_date, closed_date, cleared_datetime,
        closed_datetime, cust_type, node_name,
        max(c.iqara_id) as iqara_id,
        count(distinct b.history_id) as repeatcalls,
        modem_status, soc_desc, action_taken,
        rem_desc,
        ifnull(reopened,0) as reopened,
        CASE
           WHEN fstatus = 'Closed' THEN 0
           WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,
                     a.logged_date,now())/60
           WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,
                     a.logged_date,a.cleared_datetime)/60
           WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,
                     a.logged_date,a.closed_datetime)/60
           END as open_hrs,
        CASE
           WHEN fstatus = 'Closed' THEN 0
           WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,
                  a.logged_date,now())%60
           WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,
                  a.logged_date,a.cleared_datetime)%60
           WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,
                  a.logged_date,a.closed_datetime)%60
           END as open_min
    from  new_call a
    left join  history b on a.id=b.id
    left join  history_iqara c on a.id=c.id
    left join  modem d on d.cust_acct_id=cast(a.account_number as SIGNED)
    left join  userdetail e on a.account_number = e.account_no
    left join  mast_area f on f.cust_acct_id =
                   cast(a.account_number as SIGNED)
    left join  compsource_m g on a.comp_source = g.soc_id
    left join  co_ordinator_remarks_m h on c.rem_no = h.rem_no
    left join  call_promo i on a.call_id = i.call_id
    left join  subprocesscode_m j on a.sub_process_code = j.spc
    left join  master_call_category k on j.mast_call_category_id = k.id
    where  city = 'Vadodara'
      and  process_code = 3
      and  sub_process_code in ('3.a','3.b',
       '3.c','3.d','3.f','3.g','3.e','3.h',
       '3.i')
      and  fdate >= '2009-03-01'
      and  fdate <= '2009-05-19'
    group by  a.id,k.call_category, a.call_id,
        customer_name, phone, mobile, account_number,
        address, a.area, city, complaint,
        process_code, agent_reply, agent_comments,
        fstatus, cstatus, assignto, sub_process_code,
        cleared_date, closed_date, cleared_datetime,
        closed_datetime, logged_date, cust_type,
        node_name , modem_status, soc_desc,
        iqara_id, action_taken, rem_desc,
       reopened
    having  c.iqara_id = 
           ( SELECT  max(iqara_id)
               from  history_iqara
              where  id = a.id )
       or c.iqara_id is null
    order by  logged_date desc;

"cast(a.account_number as SIGNED)" -- cannot use index on account_number

This is a strange pattern...
SELECT ... max(c.iqara_id) ...
  GROUP BY -- not iquara_id
  HAVING c.iqara_id ...
because c.iquara_id is not available by the time HAVING is executed.

Are you looking only at the rows with the max value of c_iqara_id, plus any null ones? If so, consider this pattern:
SELECT ...
   FROM ...
   LEFT JOIN ( SELECT ... ) c ON ...
   LEFT JOIN ...
The idea is to find the few rows that match the restrictions on history_iqara, before having to haul around lots of data. Note how it had to hit 406K rows (among the several tables) during the query.

It may be useful to rollup b, too: count(distinct b.history_id) as repeatcalls

Be sure to do the relevant parts of the GROUP BY when doing the above suggestions.

I can't tell which fields are which in the GROUP BY.

I suspect there are more hints available, but I can't see them without seeing the EXPLAIN and TABLE STATUS and CREATE TABLEs.

Options: ReplyQuote


Subject
Written By
Posted
May 11, 2009 11:53AM
Re: MySQL using 80-90% of CPU
May 19, 2009 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.