List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:August 18 2010 6:40pm
Subject:Re: idle query
View as plain text  
On 8/12/2010 2:32 PM, Mike Spreitzer wrote:
> I also find that if I have both tables in MyISAM and use STRAIGHT_JOIN to 
> force the better query plan (enumerate the longer table, for each longer 
> table row use the shorter table's index to pick out the one right matching 
> row from the shorter table) then the server has low I/O utilization but 
> the CPU utilization is about as high as can be expected for a single query 
> running on a 16-CPU machine.  Why should this thing be CPU-bound?  Here is 
> the query:
> 
> create table fp2 (p VARCHAR(200) NOT NULL,
>        rcd DATETIME NOT NULL, rcms SMALLINT NOT NULL, msgid BIGINT NOT 
> NULL,
>        q VARCHAR(200) NOT NULL, scd DATETIME NOT NULL, scms SMALLINT NOT 
> NULL,
>        lat DECIMAL(14,3),
>        INDEX p(p), INDEX q(q) )
>        AS SELECT fldrcv.p, fldrcv.cd as rcd, fldrcv.cms as rcms,
>        fldrcv.msgid as msgid, fldrcv.q, fldsnd.cd as scd, fldsnd.cms as 
> scms,
>        TIMESTAMPDIFF(SECOND, fldsnd.cd, fldrcv.cd) + 
> (fldrcv.cms-fldsnd.cms)/1000 as lat
>        FROM fldrcv STRAIGHT_JOIN fldsnd
>        ON fldrcv.q=fldsnd.p AND fldrcv.qboot=fldsnd.pboot
>        AND fldrcv.msgid=fldsnd.msgid;
> 
> and here is some `iostat -x 5` output that shows a total of less than 50% 
> I/O utilization and about 15/16 CPU utilization:
> 
> ...
> 

You are doing a lot of index work which requires a lot of memory 
manipulation. You are populating two on the new table while using at 
least one to build your data. I believe it's that random accesss memory 
work that's chewing up a big chunk of your CPU time.

Does it work better if you delay the index creation of your temporary 
table until after the table is populated?

CREATE TABLE fp2 ... SELECT ... ;
ALTER TABLE fp2 ADD KEY p(p),KEY q(q);

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
idle queryMike Spreitzer27 Jul
  • Re: idle queryDan Nelson27 Jul
    • Re: idle queryMike Spreitzer27 Jul
      • Re: idle queryDan Nelson27 Jul
        • Re: idle queryMike Spreitzer27 Jul
          • Re: idle queryDan Nelson27 Jul
            • Re: idle queryMike Spreitzer27 Jul
              • Re: idle queryDan Nelson27 Jul
            • Re: idle queryDan Nelson27 Jul
              • Re: idle queryMike Spreitzer28 Jul
    • RE: idle queryJerry Schwartz27 Jul
    • Re: idle queryMike Spreitzer11 Aug
      • STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
        • Re: STRAIGHT JOIN vs. field namesMichael Dykman11 Aug
          • Re: STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
      • Re: idle queryMike Spreitzer12 Aug
        • Re: idle queryMySQL)18 Aug