Occasionally VERY slow queries - status "Sorting Result"

[Bill Meier]

I'm running mySQL v4.1.14 compiled with Linux threads on FreeBSD 4.7 on a virtual server setup.

I have this one query that has frustrated me for months, and I have been unable to understand it.

The query is

SELECT id FROM picture WHERE parent=NNN ORDER BY date LIMIT 0,50

NNN is a value with ordinal about 40. Parent and date (and id) are all keyed. Database has about 25,000 records, and one of these queries generally return 300 to 3000 records. I do my own "slow query" logging around this query, and also record the load average.

Over 99% of the time the query completes in 10ms or less, as might be expected. But, sometimes it takes 10 to 30 seconds or longer. Worst case I have seen it take over 6 minutes. In all cases, the load average is very low, very 0.20 so the system itself isn't busy. SHOW PROCESSLIST shows the query in "Sorting Result" state. It can stay in this state for a long time as noted above. Of course, the lock it takes on the table prevents all other UPDATE operations (and additional SELECTS like this one) from running, so they all queue up stalled waiting for this query. So, they also wait... When this one query completes, the log jam is cleared up, and everything else completes nearly instantaneously.

Perhaps I'm chasing a red herring, as I don't really think this is a mySQL problem, since the same query executes very fast nearly all the time. This database is also updated perhaps a dozen times a minute.

I have been thinking is this an OS scheduling issue? a mySQL Linux threads issue? some locking issue? (but I don't think it would get into "sorting result" if that was the case).

EXPLAIN says its SIMPLE; using a key; rows 407; extra Using where ...

So what is the hang up? I assume since the returned record is far less than max_length_for_sort_data (1024) and with only 300-3000 records, clearly fits in sort_buffer_size (2M) ... the sort is all done in memory?

Again, I'm not really sure it is a specific mySQL issue, but I would appreciate it if anyone had any feedback or any additional monitoring or hooks I could add.

Thanks,

Bill