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,del eted,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:dnelson@allantgroup.com]
> Sent: Saturday, September 27, 2003 6:55 PM
> To: Kevin
> Cc: mysql@lists.mysql.com
> 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
> dnelson@allantgroup.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=kevin@allpoetry.com


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org