idle query

--=_alternative 000AC4268525776D_=
Content-Type: text/plain; charset="US-ASCII"

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

Here is what "show processlist" gets me:

+----+------+-----------+-------------------+---------+----- --+--------------+------------------------------------------ ------------------------------------------------------------ +
| Id | User | Host | db | Command | Time | State |
Info |
+----+------+-----------+-------------------+---------+----- --+--------------+------------------------------------------ ------------------------------------------------------------ +
| 3 | root | localhost | bigCell2906_flood | Query | 32050 | Sending
data | create table fldpar (p VARCHAR(200) NOT NULL, rcd DATETIME NOT
NULL, rcms SMALLINT NOT NULL, |
| 8 | root | localhost | | Query | 0 | | show
processlist |
+----+------+-----------+-------------------+---------+----- --+--------------+------------------------------------------ ------------------------------------------------------------ +

Yes, that's nearly 9 hours so far on this query. The amount of data
involved here is not small, but it is not so big that 9 hours should be
needed for this statement. Here is the statement:

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,bi gCell2906_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?

/etc/my.cnf is based on the distribution's my-huge.cnf, with only minor
customization.

Thanks,
Mike Spreitzer

--=_alternative 000AC4268525776D_=--

Re: idle query

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,bi gCell2906_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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

--=_alternative 00174D648525776D_=
Content-Type: text/plain; charset="US-ASCII"

Thanks for the clues. In this case the storage is not on a SATA disk,
rather is it on a GPFS (
http://en.wikipedia.org/wiki/IBM_General_Parallel_File_Syste m) mount. This
thing is capable of quite a lot more I/O bandwidth. I invoked `wc` on a
large file and it took the "bi" stat of `vmstat` over 16000. The iostat
utility is not installed there, I will look into that.

Regarding your suggestions how to improve, I suspect I will not be able to
put fldrcv in a RAM table. "SHOW TABLE STATUS" says the data_length of
fldrcv is about 2 GB. I will look into your other suggestions.

Thanks!
Mike Spreitzer

--=_alternative 00174D648525776D_=--

Re: idle query

In the last episode (Jul 27), Mike Spreitzer said:
> Thanks for the clues. In this case the storage is not on a SATA disk,
> rather is it on a GPFS (
> http://en.wikipedia.org/wiki/IBM_General_Parallel_File_Syste m) mount. This
> thing is capable of quite a lot more I/O bandwidth. I invoked `wc` on a
> large file and it took the "bi" stat of `vmstat` over 16000. The iostat
> utility is not installed there, I will look into that.

wc is sequential (and likely is reading very small chunks at a time, no more
than 8k, which further inflates your sequential iops number), while mysql is
doing random access on that table. Single-threaded random access is always
equal to the _average_ iops of all disks, not the total. It doesn't matter
what filesystem you use, since you can't predict random access and each read
is going to move one of your drive heads (but you don't know which one).

> Regarding your suggestions how to improve, I suspect I will not be able to
> put fldrcv in a RAM table. "SHOW TABLE STATUS" says the data_length of
> fldrcv is about 2 GB. I will look into your other suggestions.

You did say you had 64GB of memory? I wouldn't expect 2gb to be noticed at
all :)

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

--=_alternative 002044848525776D_=
Content-Type: text/plain; charset="US-ASCII"

Sure, `wc` is different from mysql --- but different enough to account for
a 16000:75 ratio?

Will iostat give a good utilization metric for GPFS?

If I want to try to actually hold a 2GB table in RAM, is there anything I
need to set in my.cnf to enable that?

Thanks,
Mike Spreitzer
SMTP: mspreitz@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM
Office phone: +1-914-784-6424 (IBM T/L 863-)
AOL Instant Messaging: M1k3Sprtzr
--=_alternative 002044848525776D_=--

Re: idle query

In the last episode (Jul 27), Mike Spreitzer said:
> Sure, `wc` is different from mysql --- but different enough to account for
> a 16000:75 ratio?

Most definitely. wc is reading sequentially, and the OS is probably
coalescing those reads and prefetching disk blocks in 128KB chunks.
160000*128 is around 2GB/sec (iostat would tell you your actual throughput).
You probably either have a 2gb fibre-channel card, or else wc is CPU-bound
at this point, counting each character as it streams past. I bet "dd
if=largefile of=/dev/null bs=8k" would give you even more iops. "dd ...
bs=1m" would probably max out your fibre-channel card's bandwidth. None of
those commands are doing random I/Os, though, so you can't compare their
numbers to your mysql query.

> Will iostat give a good utilization metric for GPFS?

For your particular query, yes. You're doing single-threaded random IO, so
you are fetching a random disk block, waiting for the result, then fetching
another random block, etc. 100% of your time should be in iowait, waiting
for a disk head to seek to your data. If it's not at least 80%, then your
query isn't waiting on disk I/O, and since you aren't CPU-bound, I'm not
sure what your bottleneck would be at that point...

> If I want to try to actually hold a 2GB table in RAM, is there anything I
> need to set in my.cnf to enable that?

Just make sure your key_buffer_size is large enough to hold the index. You
can find this number by setting key_buffer_size to a huge number (32GB for
example), running "LOAD INDEX INTO CACHE" for your index, then running "show
status like 'key_blocks_used'; ".

http://dev.mysql.com/doc/refman/5.1/en/load-index.html

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

--=_alternative 0042686B8525776D_=
Content-Type: text/plain; charset="US-ASCII"

Does `iostat` consider GPFS mounts at all? If so, how can I tell which
line of `iostat` output is about the GPFS mounted at /dev/gpfscf ? I do
not see such a thing mentioned in the iostat output.

In `vmstat` output, I thought "bi" is in terms of fixed-size blocks, not
I/O commands.

Thanks,
Mike Spreitzer

--=_alternative 0042686B8525776D_=--

RE: idle query

>-----Original Message-----
>From: Dan Nelson [mailto:dnelson@allantgroup.com]
>Sent: Monday, July 26, 2010 11:31 PM
>To: Mike Spreitzer
>Cc: MySql
>Subject: Re: idle query


>iostat -x output would be helpful here, too, so we can see whether your
>disks are at 100% busy.
>
[JS] 100% busy would be a disaster! Anything more than about 80% busy is going
to slow things down rapidly, but by busy I mean not idle. The number of I/Os
per second is not a good indicator, because there's a big difference between
sequential reads, repeatable reads (that could be satisfied out of the disk's
or controller's buffers), random reads, and writes of any of the same flavors.
It also matters whether or not you have write caching (delayed writes) turned
on.

Then you have to consider the effect of striping, the number of controllers
across which the disks are spread, the smarts in the controllers (request
chaining?) and the like.

There's also the behavior of the file system at the OS level. If reads are
unlikely to be satisfied in the file system's buffers, then a small buffer
pool would be marginally more efficient that a large one; if they are fairly
localized or repeatable, a bigger buffer pool is better. A journaling file
system is better for writing, under most circumstances.

In most cases, when a system is slow but the CPU's are all loafing it's a good
guess that the bottleneck is in the disk subsystem.

[I was a performance consultant in a past life. In another past life I
invented the sharp stick.]

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>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,b igCell2906_flood.fld
>snd.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@allantgroup.com
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

In the last episode (Jul 27), Mike Spreitzer said:
> Does `iostat` consider GPFS mounts at all? If so, how can I tell which
> line of `iostat` output is about the GPFS mounted at /dev/gpfscf ? I do
> not see such a thing mentioned in the iostat output.

iostat works at the disk device level, not at the filesystem level. If your
gpfs filesystem consists of one SAN device, then it'll be easy to find in
your iostat -x output :) If it is spread over multiple SAN devices, then
you'll only see per-device stats. There may be a gpfs-specific command that
can give you summary IO stats for the filesystem as a whole.

> In `vmstat` output, I thought "bi" is in terms of fixed-size blocks, not
> I/O commands.

It looks like on Linux, "bi" and "bo" are the total disk throughput in
kbytes.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

In the last episode (Jul 27), Dan Nelson said:
> In the last episode (Jul 27), Mike Spreitzer said:
> > If I want to try to actually hold a 2GB table in RAM, is there anything I
> > need to set in my.cnf to enable that?
>
> Just make sure your key_buffer_size is large enough to hold the index. You
> can find this number by setting key_buffer_size to a huge number (32GB for
> example), running "LOAD INDEX INTO CACHE" for your index, then running "show
> status like 'key_blocks_used'; ".
>
> http://dev.mysql.com/doc/refman/5.1/en/load-index.html

Oops. My previous email described the requirements for the "prefetch index
into RAM" option. For the "load table into RAM" option, you'll need to
raise max_heap_table_size to a size large enough to hold your table.

http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine .html

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: idle query

--=_alternative 004CE0BD8525776E_=
Content-Type: text/plain; charset="US-ASCII"

I installed iostat and used it. It showed that my MySQL data is striped
over four devices. During my "idle" query each of those four devices has
about 25% utilization, which is consistent with the hypothesis that this
I/O is the bottleneck. It looks like case closed. I am looking into
better server&disk and rewriting my query along the lines you suggested.

Thanks!
Mike Spreitzer
SMTP: mspreitz@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM
Office phone: +1-914-784-6424 (IBM T/L 863-)
AOL Instant Messaging: M1k3Sprtzr
--=_alternative 004CE0BD8525776E_=--

Re: idle query

--=_alternative 005FFFF78525777C_=
Content-Type: text/plain; charset="US-ASCII"

I finally started trying to optimize along the memory-based lines you
suggested. I am surprised to find that the query plan is to enumerate the
memory-based table and then pick out the hundreds of related rows from the
much larger MyISAM table. What's going on here?

`show create table` says this about the relevant tables:

CREATE TABLE `fldsndm` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) NOT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`toself` tinyint(1) DEFAULT NULL,
`sepoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

CREATE TABLE `fldrcv` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) DEFAULT NULL,
`qip` char(15) NOT NULL,
`qport` smallint(6) NOT NULL,
`qboot` bigint(20) DEFAULT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`q` varchar(200) DEFAULT NULL,
`repoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
KEY `c` (`c`),
KEY `pec` (`p`,`repoch`,`c`),
KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
KEY `qbm` (`q`,`qboot`,`msgid`),
KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And here is the query planning I see:

mysql> explain select * from fldrcv join fldsndm on fldrcv.q=fldsndm.p AND
fldrcv.qboot=fldsndm.pboot and fldrcv.msgid=fldsndm.msgid;
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+
| 1 | SIMPLE | fldsndm | ALL | pbm | NULL | NULL |
NULL | 29036 | |
| 1 | SIMPLE | fldrcv | ref | qbm | qbm | 220 |
bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot, bigCell2906_flood.fldsndm.msgid
| 452 | Using where |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+

BTW, here are the table sizes:

mysql> select count(*) from fldrcv;
+----------+
| count(*) |
+----------+
| 13785373 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from fldsndm;
+----------+
| count(*) |
+----------+
| 29036 |
+----------+

Thanks,
Mike Spreitzer

--=_alternative 005FFFF78525777C_=--

STRAIGHT JOIN vs. field names

--=_alternative 00702E788525777C_=
Content-Type: text/plain; charset="US-ASCII"

Why is it that a field name that works fine for a JOIN is invalid in a
STRAIGHT JOIN?

mysql> show create table fldsndm;
+---------+------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -----------------------------------+
| Table | Create Table |
+---------+------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -----------------------------------+
| fldsndm | CREATE TABLE `fldsndm` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) NOT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`toself` tinyint(1) DEFAULT NULL,
`sepoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -----------------------------------+
1 row in set (0.00 sec)

mysql> show create table fldrcv;
+--------+-------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------+
| Table | Create Table |
+--------+-------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------+
| fldrcv | CREATE TABLE `fldrcv` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) DEFAULT NULL,
`qip` char(15) NOT NULL,
`qport` smallint(6) NOT NULL,
`qboot` bigint(20) DEFAULT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`q` varchar(200) DEFAULT NULL,
`repoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
KEY `c` (`c`),
KEY `pec` (`p`,`repoch`,`c`),
KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
KEY `qbm` (`q`,`qboot`,`msgid`),
KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------+
1 row in set (0.00 sec)

mysql> explain extended select * from fldrcv join fldsndm on
(fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and
fldrcv.msgid=fldsndm.msgid);
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- --+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- --+-------------+
| 1 | SIMPLE | fldsndm | ALL | pbm | NULL | NULL |
NULL | 29036 | 100.00 | |
| 1 | SIMPLE | fldrcv | ref | qbm | qbm | 220 |
bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot, bigCell2906_flood.fldsndm.msgid
| 452 | 100.00 | Using where |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- --+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain extended select * from fldrcv straight join fldsndm on
(fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and
fldrcv.msgid=fldsndm.msgid);
ERROR 1054 (42S22): Unknown column 'fldrcv.q' in 'on clause'
mysql>

Thanks,
Mike Spreitzer

--=_alternative 00702E788525777C_=--

Re: STRAIGHT JOIN vs. field names

The relationship looks righteous enough but I note that you use
'straight join' in your expression, rather than 'straight_join' as
indicated in the manual
(http://dev.mysql.com/doc/refman/5.1/en/join.html).

Perhaps the message is a red herring and your trouble is elsewhere?

- michael dykman

On Wed, Aug 11, 2010 at 4:25 PM, Mike Spreitzer wrote=
:
> Why is it that a field name that works fine for a JOIN is invalid in a
> STRAIGHT JOIN?
>
> mysql> show create table fldsndm;
> +---------+------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> | Table =A0 | Create Table =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0|
> +---------+------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> | fldsndm | CREATE TABLE `fldsndm` (
> =A0`p` varchar(200) NOT NULL,
> =A0`cd` datetime NOT NULL,
> =A0`cms` smallint(6) NOT NULL,
> =A0`pip` char(15) NOT NULL,
> =A0`pport` smallint(6) NOT NULL,
> =A0`pboot` bigint(20) NOT NULL,
> =A0`msgid` bigint(20) NOT NULL,
> =A0`startgtime` bigint(20) NOT NULL,
> =A0`datalen` int(11) NOT NULL,
> =A0`toself` tinyint(1) DEFAULT NULL,
> =A0`sepoch` bigint(20) NOT NULL DEFAULT '0',
> =A0`c` decimal(11,3) NOT NULL DEFAULT '0.000',
> =A0UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
> ) ENGINE=3DMEMORY DEFAULT CHARSET=3Dlatin1 |
> +---------+------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> 1 row in set (0.00 sec)
>
> mysql> show create table fldrcv;
> +--------+-------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> | Table =A0| Create Table =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> +--------+-------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> | fldrcv | CREATE TABLE `fldrcv` (
> =A0`p` varchar(200) NOT NULL,
> =A0`cd` datetime NOT NULL,
> =A0`cms` smallint(6) NOT NULL,
> =A0`pip` char(15) NOT NULL,
> =A0`pport` smallint(6) NOT NULL,
> =A0`pboot` bigint(20) DEFAULT NULL,
> =A0`qip` char(15) NOT NULL,
> =A0`qport` smallint(6) NOT NULL,
> =A0`qboot` bigint(20) DEFAULT NULL,
> =A0`msgid` bigint(20) NOT NULL,
> =A0`startgtime` bigint(20) NOT NULL,
> =A0`datalen` int(11) NOT NULL,
> =A0`q` varchar(200) DEFAULT NULL,
> =A0`repoch` bigint(20) NOT NULL DEFAULT '0',
> =A0`c` decimal(11,3) NOT NULL DEFAULT '0.000',
> =A0KEY `c` (`c`),
> =A0KEY `pec` (`p`,`repoch`,`c`),
> =A0KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
> =A0KEY `qbm` (`q`,`qboot`,`msgid`),
> =A0KEY `pbm` (`p`,`pboot`,`msgid`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
> +--------+-------------------------------------------------- -------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------+
> 1 row in set (0.00 sec)
>
> mysql> explain extended select * from fldrcv join fldsndm on
> (fldrcv.q=3Dfldsndm.p AND fldrcv.qboot=3Dfldsndm.pboot and
> fldrcv.msgid=3Dfldsndm.msgid);
> +----+-------------+---------+------+---------------+------+ ---------+---=
------------------------------------------------------------ ---------------=
---------------+-------+----------+-------------+
> | id | select_type | table =A0 | type | possible_keys | key =A0| key_len =
| ref
> =A0 =A0 =A0 =A0 =A0 =A0 =A0| rows =A0| filtered | Extra =A0 =A0 =A0 |
> +----+-------------+---------+------+---------------+------+ ---------+---=
------------------------------------------------------------ ---------------=
---------------+-------+----------+-------------+
> | =A01 | SIMPLE =A0 =A0 =A0| fldsndm | ALL =A0| pbm =A0 =A0 =A0 =A0 =A0 |=
NULL | NULL =A0 =A0|
> NULL =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 29036 | =A0 100.00 | =A0 =A0 =
=A0 =A0 =A0 =A0 |
> | =A01 | SIMPLE =A0 =A0 =A0| fldrcv =A0| ref =A0| qbm =A0 =A0 =A0 =A0 =A0=
| qbm =A0| 220 =A0 =A0 |
> bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot, bigCell2906_f=
lood.fldsndm.msgid
> | =A0 452 | =A0 100.00 | Using where |
> +----+-------------+---------+------+---------------+------+ ---------+---=
------------------------------------------------------------ ---------------=
---------------+-------+----------+-------------+
> 2 rows in set, 1 warning (0.00 sec)
>
> mysql> explain extended select * from fldrcv straight join fldsndm on
> (fldrcv.q=3Dfldsndm.p AND fldrcv.qboot=3Dfldsndm.pboot and
> fldrcv.msgid=3Dfldsndm.msgid);
> ERROR 1054 (42S22): Unknown column 'fldrcv.q' in 'on clause'
> mysql>
>
> Thanks,
> Mike Spreitzer
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: STRAIGHT JOIN vs. field names

--=_alternative 0071ADFB8525777C_=
Content-Type: text/plain; charset="US-ASCII"

Yes, that's it. I should be typing "STRAIGHT_JOIN" instead of "STRAIGHT
JOIN".

Thanks!
Mike Spreitzer

--=_alternative 0071ADFB8525777C_=--

Re: idle query

--=_alternative 0065DC268525777D_=
Content-Type: text/plain; charset="US-ASCII"

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:

avg-cpu: %user %nice %system %iowait %steal %idle
4.27 0.00 1.82 0.00 0.03 93.89

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 1.20 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 1.20 0.00 4.60 3.83
0.00 0.00 0.00 0.00
sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdg 0.00 0.00 4.40 3.20 2252.80 1434.00 485.11
0.16 20.74 13.26 10.08
sdh 0.00 0.00 4.40 2.80 2252.80 1433.60 512.00
0.13 18.44 12.89 9.28
sdi 0.00 0.00 4.20 2.80 2150.40 1433.60 512.00
0.13 19.20 12.91 9.04
sdj 0.00 0.00 4.40 2.80 2252.80 1433.60 512.00
0.16 22.44 15.56 11.20
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle
4.28 0.00 1.81 0.01 0.03 93.88

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.40 0.00 12.80 32.00
0.00 4.00 4.00 0.16
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.60 0.00 3.80 6.33
0.00 0.00 0.00 0.00
sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdg 0.00 0.00 4.40 3.00 2252.80 1433.80 498.19
0.17 23.57 16.65 12.32
sdh 0.00 0.00 4.40 2.80 2252.80 1433.60 512.00
0.16 21.67 14.78 10.64
sdi 0.00 0.00 4.40 2.80 2252.80 1433.60 512.00
0.15 20.89 14.44 10.40
sdj 0.00 0.00 4.20 2.80 2150.40 1433.60 512.00
0.15 21.71 14.74 10.32
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00


Thanks,
Mike Spreitzer




From: Mike Spreitzer/Watson/IBM@IBMUS
To: Dan Nelson
Cc: MySql
Date: 08/11/2010 01:30 PM
Subject: Re: idle query



I finally started trying to optimize along the memory-based lines you
suggested. I am surprised to find that the query plan is to enumerate the

memory-based table and then pick out the hundreds of related rows from the

much larger MyISAM table. What's going on here?

`show create table` says this about the relevant tables:

CREATE TABLE `fldsndm` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) NOT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`toself` tinyint(1) DEFAULT NULL,
`sepoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

CREATE TABLE `fldrcv` (
`p` varchar(200) NOT NULL,
`cd` datetime NOT NULL,
`cms` smallint(6) NOT NULL,
`pip` char(15) NOT NULL,
`pport` smallint(6) NOT NULL,
`pboot` bigint(20) DEFAULT NULL,
`qip` char(15) NOT NULL,
`qport` smallint(6) NOT NULL,
`qboot` bigint(20) DEFAULT NULL,
`msgid` bigint(20) NOT NULL,
`startgtime` bigint(20) NOT NULL,
`datalen` int(11) NOT NULL,
`q` varchar(200) DEFAULT NULL,
`repoch` bigint(20) NOT NULL DEFAULT '0',
`c` decimal(11,3) NOT NULL DEFAULT '0.000',
KEY `c` (`c`),
KEY `pec` (`p`,`repoch`,`c`),
KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
KEY `qbm` (`q`,`qboot`,`msgid`),
KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And here is the query planning I see:

mysql> explain select * from fldrcv join fldsndm on fldrcv.q=fldsndm.p AND

fldrcv.qboot=fldsndm.pboot and fldrcv.msgid=fldsndm.msgid;
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+
| id | select_type | table | type | possible_keys | key | key_len | ref

| rows | Extra |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+
| 1 | SIMPLE | fldsndm | ALL | pbm | NULL | NULL |
NULL | 29036 | |
| 1 | SIMPLE | fldrcv | ref | qbm | qbm | 220 |
bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot, bigCell2906_flood.fldsndm.msgid

| 452 | Using where |
+----+-------------+---------+------+---------------+------+ ---------+-------------------------------------------------- -------------------------------------------+-------+-------- -----+

BTW, here are the table sizes:

mysql> select count(*) from fldrcv;
+----------+
| count(*) |
+----------+
| 13785373 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from fldsndm;
+----------+
| count(*) |
+----------+
| 29036 |
+----------+

Thanks,
Mike Spreitzer


--=_alternative 0065DC268525777D_=--

Re: idle query

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=gcdmg-mysql-2@m.gmane.org