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
