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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
