Hi Kevin,
----- Original Message ----- From: "Kevin" Sent: Monday, September 29, 2003 2:21 PM 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. Yes, it may be sending rows, but it also needs to read/process them before sending, which takes time (if filesort is used (not for your query), rows will be read before and after the sort, I think). > Any ideas why this phase would ever be taking 100-500 seconds? Yes, when a lot of data needs to be read. :-) I've had the same problem on a smaller scale. :-( How many MB is your poems table? Let's check the EXPLAIN and see if something can be changed... > [snip] > 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 | | > +-------+--------+---------------+---------+---------+-----------+------ > --+- It's easier to read that output here if you use \G at the end of the query instead of ;. :-) OK, all 272,000 poems rows are being scanned (assuming mid isn't part of the "created" index) and the index is being used for ORDER BY. Actually, since there's no filesort, it will abort when and if the LIMIT is satisfied. If mid isn't in the "created" index, MySQL needs to jump to the data file for each row to check if mid matches the WHERE. If your data file is too big to be cached in RAM by the OS and LIMIT rows aren't found early, the disk seeks will REALLY slow it down. Do all the problem queries have WHERE poems.mid=<number> in them? Why don't you try adding an index to poems.mid? Even if the WHERE matches a couple thousand rows and filesort is used, it should be a lot faster than reading the whole data file. If you're searching for a single mid value (ref type in EXPLAIN) and you're using MySQL 4+, you can eliminate filesort by creating a composite index on (mid, created) together. By the way, remove "use index (type)" from the query as there's nothing in your example that would allow an index on type to be used anyway. > Thanks for any help anyone can give me - this is driving me nuts! Yeah, see if indexing mid helps. Funny little riddles in your sig BTW. :-D Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
