Could it be possible that the number of rows returned causes the slowness?
There is a 'limit 0,30' in the query, but does it still lookup the join for each row? > --+-| poems | index | NULL | created | 4 | NULL | > 272319 | Using where | > | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | > 1 | There are 272,000 rows returned... This slow 'sending data' phase only happens under decently high load... But my impression of this query was that it should be relatively instantm since its only loading 30 rows and there is an index. Are all 272,000 rows matched against the poets table? Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench > -----Original Message----- > From: Kevin [mailto:[EMAIL PROTECTED] > Sent: Monday, September 29, 2003 12:21 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: slow 'sending data' phase > > I've fixed my swapping issues, but the system continues to get stuck in > a 'sending data' phase from time to time. > > With mod_perl + mysql, this phase SHOULD be when mysql collects the rows > (after sorting, etc) and sends them to the perl handler for processing. > > Any ideas why this phase would ever be taking 100-500 seconds? The > system appears to be stable for long times, then gets in a huge > bottleneck locking on one sending data process. These queries aren't > locked and then just finally being processed - they actually remain in > the sending data phase for most of this time. > > The process varies from time to time - I believe it is something to do > with the OS, disk, or ram, but have no idea where to look. The system > is NOT swapping, and has 105 MB Ram free. I am using the same disk for > my tmp drive and data storage, which is because we were having problems > with software raid slowing down the system. > > An example query stuck in sending data phase: > > Id User Host db Command Time State Info > 130 allpoetry localhost allpoetry Query 231 > Sending data SELECT > lid,brief,title,content,collection,written,created,cat1,cat2,cat3,type,p > oems.contest,critical,poems.mid,name,preferred,lastlogin,deleted,image,s > yndicated FROM poems use index (type) left join poets on poems.mid = > poets.mid WHERE poems.mid =2001 ORDER BY created DESC LIMIT 0,30 > > explained: > +-------+--------+---------------+---------+---------+-----------+------ > --+-| table | type | possible_keys | key | key_len | ref | > rows | Extra | > +-------+--------+---------------+---------+---------+-----------+------ > --+-| poems | index | NULL | created | 4 | NULL | > 272319 | Using where | > | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | > 1 | | > +-------+--------+---------------+---------+---------+-----------+------ > --+- > > Thanks for any help anyone can give me - this is driving me nuts! > > Ciao, > Kevin Watt > Community Manager, Allpoetry.com > What happened to the cow who went for a drive? He got a Moo_ving > violation > What do you call someone who is crazy about hot chocolate? A cocoa nut > What do bees use to cut wood? Buzz saws > Who eats at underwater resturants ? Suba diners > How do really small people call each other ? On Microphones > How do you fix a broken chimp? With a monkey wrench > > > -----Original Message----- > > From: Dan Nelson [mailto:[EMAIL PROTECTED] > > Sent: Saturday, September 27, 2003 6:55 PM > > To: Kevin > > Cc: [EMAIL PROTECTED] > > Subject: Re: # processes vs. #threads, and memory usage (Revisited for > > thread cache) > > > > In the last episode (Sep 27), Kevin said: > > > Bringing this back up again, because the number of extra 'threads' > > > mysql is using seems to vary widely, from I've noticed sometimes, > > > from 5 more than the threads I'm using to more than 50! > > > > > > My memory usage also seems to differ accordingly. > > > > > > It seems to be because my thread cache is set to 40, so 'mytop' > shows > > > 38 threads cached, with only 20 connected right now. Shouldn't it > > > kill off those threads after awhile? > > > > > > Is that what the wait_timeout field is for? I thought it was for > > > keeping the connection open, rather than the connection cached. > Mine > > > is currently at '600', and it doesn't seem to be clearing up the > > > cache... > > > > Idle threads should take up almost no RAM (thread_stack plus a little > > bit of overhead), so there should be no need to kill them. If you're > > swapping, add more RAM, or reduce mysql's memory usage by reducing its > > buffer sizes (check the manual for which ones are used when). > > > > wait_timeout is how long before an idle client connection is > > terminated. If the total thread count is greater than thread_cache, > > the thread exits too. > > > > > The reason this is important is because I suspect my 'slow queries' > > > comes up when I run out of ram and it starts using disk swap - which > > > happens because mysql is taking up so much extra memory. > > > > Well, that's easy enough to test; just watch vmstat output during a > > query. If you are swapping, either reduce the global cache settings > > (key_buffer, query_cache_size, or one of the many innodb_*_size > > variables), or the per-query settings (sort_buffer, tmp_table_size, > > join_buffer_size, etc). RAM is cheap too. > > > > -- > > Dan Nelson > > [EMAIL PROTECTED] > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL Perl Mailing List > For list archives: http://lists.mysql.com/perl > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
