List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 27 2010 3:31am
Subject:Re: idle query
View as plain text  
In the last episode (Jul 26), Mike Spreitzer said:
> A colleague is running MySQL community server 5.1.34 on RHEL 5 on a big
> Xeon-based SMP (16 CPUs, 64 GB memory).  It is taking a surprisingly long
> time to execute a query, yet is not working particularly hard at it.  I
> wonder why this might be.  Following are details.  First, some `vmstat`
> output that shows the machine is doing almost nothing (I have inserted
> some additional spaces in the header to make it line up better); it shows
> no CPU activity and very little I/O:
> 
> # vmstat 5
> procs -----------memory---------- ---swap-- -----io---- --system-- 
> -----cpu------
>  r  b   swpd     free   buff   cache   si   so    bi    bo   in   cs us sy   id wa
> st
>  0  0      0 56954556 328608 1040188    0    0  2600   130    1    1  1  0  99  0  0
>  0  1      0 56954564 328608 1040188    0    0    74     6  301  961  0  0  100  0 
> 0
>  0  0      0 56954564 328608 1040188    0    0    73     1  287  970  0  0  100  0 
> 0
>  0  0      0 56954564 328608 1040188    0    0    73     5  297  925  0  0  100  0 
> 0
>  0  0      0 56954564 328608 1040188    0    0    71    51  291  926  0  0  100  0 
> 0

iostat -x output would be helpful here, too, so we can see whether your
disks are at 100% busy.

You're doing ~75 I/O's per second, so it looks like you're running on slow
SATA drives (a 4500 RPM drive will tops out at 75 iops); with drives like
that, anything that forces random I/O to a large table is going to kill you. 
MySQL runs a single thread for each submitted query (i.e.  no Parallel Query
option like Oracle has), so if a query needs to do 14 million random reads,
that'll be 1400000/70/3600 ~= 6 hours just to fetch table data (let alone
the index lookups required to find the table rows).

MySQL also only has a simple index join type on disk-based tables, so no
hash joins (which would load your 2nd table into RAM temporarily as a hash
table).  It does use hash indexes on memory tables though, so if you load
fldrcv into a memory table and join fldsnd against it, that might work
pretty well.

Another option would be to create an index on fldrcv that covers both your
WHERE clause fields and your SELECT clause fields.  MySQL will be able to
use that index to satisfy the query's requirements without going to the
table itself.  Then you can use the LOAD INDEX INTO CACHE command to preload
that index into memory.  This ends up working similar to the memory table
option (no disk I/O needed for the 2nd table), with tradeoffs on both sides
(a preloaded index consumes disk space and mysql has to keep it updated when
the table is updated, but a memory table has to be dropped and rebuilt every
time you run your main query if the fldrcv table changes often).  Which
option you choose depends on how often you run the query, and how often
fldrcv changes.
 
> create table fldpar (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,
>  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 from fldrcv, fldsnd where fldrcv.q=fldsnd.p AND
>  fldrcv.qboot=fldsnd.pboot AND fldrcv.msgid=fldsnd.msgid;

> This statement makes a new table by joining two existing tables.  Here is 
> what `explain` has to say about the select part of the statement:
> 
> mysql> explain 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 from fldrcv, fldsnd where
>     ->  fldrcv.q=fldsnd.p AND fldrcv.qboot=fldsnd.pboot AND
>     ->  fldrcv.msgid=fldsnd.msgid;
>
> +----+-------------+--------+------+---------------+------+---------+------------------------------------------------------------------------------------------+-------+-------------+
> | id | select_type | table  | type | possible_keys | key  | key_len | ref           
> | rows  | Extra       |
>
> +----+-------------+--------+------+---------------+------+---------+------------------------------------------------------------------------------------------+-------+-------------+
> |  1 | SIMPLE      | fldsnd | ALL  | pec,pbm       | NULL | NULL    | NULL           
> | 29036 |             | 
> |  1 | SIMPLE      | fldrcv | ref  | qbm           | qbm  | 220     | 
> bigCell2906_flood.fldsnd.p,bigCell2906_flood.fldsnd.pboot,bigCell2906_flood.fldsnd.msgid 
> |   452 | Using where | 
>
> +----+-------------+--------+------+---------------+------+---------+------------------------------------------------------------------------------------------+-------+-------------+
> 
> The fldrcv table has an index on precisely the fields used in this join. 
> There are about 14 million rows in that table, which is about 480 times as
> many rows as there are in the fldsnd table.  I expect the result to be no
> larger than the fldrcv table.  So it looks like the index is making this
> query run about as fast as can be expected, right?  It did not take
> anywhere near 9 hours to make the fldrcv table ...  so why is it taking so
> long to do this join to make the fldpar table?

-- 
	Dan Nelson
	dnelson@stripped
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