very slow "Sending data" phase

Hello All,

I run a mod_perl/mysql website, and have been noticing increasingly
often lately that queries will get stuck for a very long time in the
sending data phase:

3652 allpoetry localhost allpoetry Query 291
Sending data SELECT
amt,bid,bids.lid,bids.mid,owner,name,title,bids.type,bids.pr eferred,cat1
,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type
as p_type, poems.image FROM bids left join poets on bids.owner =
poets.mid left join poems on poems.lid = bids.lid WHERE remaining > 0
ORDER BY amt DESC LIMIT 5
3653 allpoetry localhost allpoetry Query 291
Sending data SELECT
amt,bid,bids.lid,bids.mid,owner,name,title,bids.type,bids.pr eferred,cat1
,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type
as p_type, poems.image FROM bids left join poets on bids.owner =
poets.mid left join poems on poems.lid = bids.lid WHERE remaining > 0
ORDER BY amt DESC LIMIT 5

... 10 more of the same thing ...

Often these are the *only* queries shown running for 10-20-30 seconds in
"show full processlist", though other things are running, just very
quickily. Eventually things start to wait for writes and it locks up
and drags the system to slowness for 5-10 minutes.

The long 'sending data' phase seems to happen for many different
queries. The explain for the top query shows:
----------------------------+
| table | type | possible_keys | key | key_len | ref |
rows |
+-------+--------+---------------+-----------+---------+---- --------+---
--
| bids | range | remaining | remaining | 2 | NULL |
192 | Using where; Using filesort |
| poets | eq_ref | PRIMARY | PRIMARY | 3 | bids.owner |
1 | |
| poems | eq_ref | PRIMARY | PRIMARY | 3 | bids.lid |
1 | |
+-------+--------+---------------+-----------+---------+---- --------+---
+-------+--------+---------------+-----------+---------+---- --------+---

Sure, it's a filesort, but only 192 rows so should be no big deal.
(Side note: the 'remaining' key is on (remaining, amt), so shouldn't it
be using it and not filesorting?).

During these times apache doesn't seem to be using any more than usual
processor power, and I've done lots of 'strace -p' but without really
learning anything.

Any ideas what could be causing such a long sending-data phase? Ram is
a little short, but not dipping into swap.

total: used: free: shared: buffers: cached:
Mem: 650891264 636760064 14131200 0 7864320 347262976
Swap: 806068224 20066304 786001920


I'm running the latest 4.0x redhat rpm version (4.0.15-standard-log),
with all myisam tables.

My process list looks like:
## PID UID Size Share VSize Rss TTY St Command
1 12085 mysql 22M 2.1M 132M 22M 34816 S mysqld
2 12087 mysql 22M 2.1M 132M 22M 34816 S mysqld
3 12088 mysql 22M 2.1M 132M 22M 34816 S mysqld
... repeat exactly until ...
49 12367 mysql 22M 2.1M 132M 22M 34816 S mysqld

1 12056 root 988K 820K 4.1M 988K 34816 S mysqld_safe

Which is strange too because it has way more processes than apache can
be using (should be only one persistent connection per process): # ps
aux | grep /sbin/httpd | wc -l
22
# ps aux | grep mysql | wc -l
53

My relevant my.cnf variables are:
set-variable = key_buffer=100M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = myisam_max_sort_file_size=600M
set-variable = myisam_max_extra_sort_file_size=600M
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache=40
set-variable = thread_concurrency=2

Thanks for any help you can suggest! Sorry for including so much
information, I'm not sure what could be causing it. I can't narrow down
any recent changes to what could be causing it.

Ciao,
Kevin Watt
Community Manager, Allpoetry.com
How do really small people call each other ? On Microphones
How do you fix a broken chimp? With a monkey wrench



--
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