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=arch...@jab.org
Re: idle query
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.270.001.820.000.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.000.00 0.00 0.00 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sde 0.00 0.00 0.00 1.20 0.00 4.60 3.83 0.000.00 0.00 0.00 sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.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.000.00 0.00 0.00 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 4.280.001.810.010.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.0012.8032.00 0.004.00 4.00 0.16 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sde 0.00 0.00 0.00 0.60 0.00 3.80 6.33 0.000.00 0.00 0.00 sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.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.000.00 0.00 0.00 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 Thanks, Mike Spreitzer From: Mike Spreitzer/Watson/i...@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`
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
Re: idle query
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: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr
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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: idle query
>-Original Message- >From: Dan Nelson [mailto:dnel...@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: je...@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 140/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 | >> ++-++--+---+--+-+ >--- &
Re: idle query
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
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. 16*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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: idle query
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: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr
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_System) 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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: idle query
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_System) 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
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 sobibo in cs us sy > id wa st > 0 0 0 56954556 328608 104018800 2600 13011 1 0 > 99 0 0 > 0 1 0 56954564 328608 10401880074 6 301 961 0 0 > 100 0 0 > 0 0 0 56954564 328608 10401880073 1 287 970 0 0 > 100 0 0 > 0 0 0 56954564 328608 10401880073 5 297 925 0 0 > 100 0 0 > 0 0 0 56954564 328608 1040188007151 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 140/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
idle query
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 sobibo in cs us sy id wa st 0 0 0 56954556 328608 104018800 2600 13011 1 0 99 0 0 0 1 0 56954564 328608 10401880074 6 301 961 0 0 100 0 0 0 0 0 56954564 328608 10401880073 1 287 970 0 0 100 0 0 0 0 0 56954564 328608 10401880073 5 297 925 0 0 100 0 0 0 0 0 56954564 328608 1040188007151 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,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? /etc/my.cnf is based on the distribution's my-huge.cnf, with only minor customization. Thanks, Mike Spreitzer
